Oracle 用户权限管理

最新推荐文章于 2022-05-23 09:01:40 发布

转载最新推荐文章于 2022-05-23 09:01:40 发布 · 97 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/zhaoyangjian724/p/3798024.html

文章标签：

#数据库

本文详细介绍了如何为特定用户授予SQL权限和角色，包括使用`GRANT`语句和角色分配，以及如何通过查询确认权限。演示了为用户`test3`仅赋予`connect`权限，并进一步授予`resource`角色和`createview`权限，最终实现用户对数据库的增删查改等操作。同时，说明了在创建用户时通常应给予`connect`, `resource`和`createview`权限的重要性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SQL> select * from ROLE_SYS_PRIVS where ROLE='RESOURCE';

ROLE			       PRIVILEGE				ADM
------------------------------ ---------------------------------------- ---
RESOURCE		       CREATE SEQUENCE				NO
RESOURCE		       CREATE TRIGGER				NO
RESOURCE		       CREATE CLUSTER				NO
RESOURCE		       CREATE PROCEDURE 			NO
RESOURCE		       CREATE TYPE				NO
RESOURCE		       CREATE OPERATOR				NO
RESOURCE		       CREATE TABLE				NO
RESOURCE		       CREATE INDEXTYPE 			NO

SQL> select * from ROLE_SYS_PRIVS where ROLE='CONNECT';

ROLE			       PRIVILEGE				ADM
------------------------------ ---------------------------------------- ---
CONNECT 		       CREATE SESSION				NO


Select  *  from  session_privs;SQL> 

PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE

10 rows selected.



只给test3用户connect权限:
SQL> create user test3 identified by test3;

User created.

SQL> grant connect to test3;

Grant succeeded.

SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION

SQL> create table id(id int);
create table id(id int)
*
ERROR at line 1:
ORA-01031: insufficient privileges


给test3用户connect和resource 角色:

SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE

10 rows selected.
SQL> create view v1 as select * from dual;
create view v1 as select * from dual
            *
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> grant create view to test3;

Grant succeeded.

SQL> create view v1 as select * from dual;

View created.



创建用户时一般给connect,resource和create view权限

转载于:https://www.cnblogs.com/zhaoyangjian724/p/3798024.html