oracle 用户角色权限

浏览数：195 / 时间：2015年06月12日

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没有角色)
没有登录

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

已连接到 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
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不可以登录）
没有登录

SQL> conn user2/user2;（user2可以登录）
已连接到 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
已连接为 user2

SQL> conn system/cqstc;
已连接到 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
已连接为 system

SQL> revoke temp from user1;

撤回成功

SQL> conn user2/user2;（user2可以登录）
已连接到 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
已连接为 user2

SQL> conn user1/user1;(USRE1不可以登陆)
没有登录

SQL> conn system/cqstc;
已连接到 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
已连接为 system

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

GRANTEE                        GRANTED_ROLE                   ADMIN_OPTION DEFAULT_ROLE
------------------------------ ------------------------------ ------------ ------------

SQL> select* from dba_role_privs where grantee=‘TEMP‘;

GRANTEE                        GRANTED_ROLE                   ADMIN_OPTION DEFAULT_ROLE
------------------------------ ------------------------------ ------------ ------------

SQL> select* from dba_role_privs where grantee=‘USER2‘;

GRANTEE                        GRANTED_ROLE                   ADMIN_OPTION DEFAULT_ROLE
------------------------------ ------------------------------ ------------ ------------
USER2                          CONNECT                        NO           YES

SQL>