Oracle 用户权限管理

时间：2021-07-27 10:29:14
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权限

相关文章