oracle 用户角色权限-白红宇

oracle 用户角色权限

阅读量：4576 次

发布时间：2019-06-08

本文共 4845 字，大约阅读时间需要 16 分钟。

SQL> create user user1 identified by user1;

用户被创建

SQL> create user user2 identified by user2;

用户被创建

SQL> create role temp;

角色被创建

SQL> grant connect to temp with grant option;（预定义角色赋给自定义角色）

grant connect to temp with grant option

ORA-01939: 只能指定 ADMIN OPTION

SQL> grant connect to temp with admin option;

授予成功

SQL> grant temp to user1 with grant option;（自定义角色赋予用户）

grant temp to user1 with grant option

ORA-01939: 只能指定 ADMIN OPTION

SQL> grant temp to user1 with admin option;

授予成功

SQL> conn user1/user1;（user1可以登录）

已连接到 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

已连接为 user1

SQL> conn system/cqstc;

已连接到 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

已连接为 system

SQL> revoke connect from temp;

撤回成功

SQL> conn user1/user1;(user1不可以登陆，但有temp的角色，角色temp没有角色)

没有登录

SQL> select * from dba_role_privs where grantee='USER1';

GRANTEE GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE

------------------ ------------------------------ ----------- ------------

USER1 TEMP YES YES

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++=

已连接到 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

已连接为 system

SQL> SHOW user;

User is "system"

SQL> grant connect to temp with admin option;

授予成功

SQL> select * from dba_role_privs where grantee='USER1';

GRANTEE GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE

------------------------------ ------------------------------ ------------ ------------

USER1 TEMP YES YES

SQL> select * from dba_ROLE_privs where grantee='TEMP';

GRANTEE GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE

------------------------------ ------------------------------ ------------ ------------

TEMP CONNECT YES YES

SQL> select * from dba_SYS_privs where grantee='TEMP';

GRANTEE PRIVILEGE ADMIN_OPTION

------------------------------ ---------------------------------------- ------------

SQL> select * from dba_SYS_privs where grantee='USER1';

GRANTEE PRIVILEGE ADMIN_OPTION

------------------------------ ---------------------------------------- ------------

SQL> select * from dba_SYS_privs where grantee='CONNECT';

GRANTEE PRIVILEGE ADMIN_OPTION

------------------------------ ---------------------------------------- ------------

CONNECT CREATE SESSION NO

//都没有对象权限

SQL> select * from dba_TAB_privs where grantee='CONNECT';

GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRANTABLE HIERARCHY

------------------------------ ------------------------------ ------------------------------ ------------------------------ ----------------------------------------

SQL> select * from dba_TAB_privs where grantee='USER1';

GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRANTABLE HIERARCHY

------------------------------ ------------------------------ ------------------------------ ------------------------------ ----------------------------------------

SQL> select * from dba_TAB_privs where grantee='TEMP';

GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRANTABLE HIERARCHY

------------------------------ ------------------------------ ------------------------------ ------------------------------ ----------------------------------------

SQL> conn user1/user1;（user1可以登录，有temp角色）

已连接到 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

已连接为 user1

SQL> grant connect to user2;

授予成功

SQL> conn user2/user2;（user2可以登录）

已连接到 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

已连接为 user2

SQL> select * from user_role_privs;

USERNAME GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE OS_GRANTED

------------------------------ ------------------------------ ------------ ------------ ----------

USER2 CONNECT NO YES NO