oracle 用户 角色 权限

时间:2014-01-14 19:17:53   收藏:0   阅读:999

bubuko.com,布布扣

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
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>

原文:http://www.cnblogs.com/MR-Guo/p/3511993.html

评论(0
© 2014 bubuko.com 版权所有 - 联系我们:wmxa8@hotmail.com
打开技术之扣,分享程序人生!