11gOCP 1z0-052 :2013-09-11 MGR_ROLE role........................................................A66

转载请注明出处:http://blog.csdn.net/guoyjoe/article/details/11584537

正确答案A

 

实验测试

1、创建用户:SKD

gyj@OCM> create user SKD identified by SKD;

User created.

2、授权

gyj@OCM> grant connect,resource to SKD;

Grant succeeded.

3、根据题意创建角色MGR_ROLE

gyj@OCM> create role MGR_ROLE;

Role created.

4、查询角色

gyj@OCM> select * from dba_role_privs where  grantee='MGR_ROLE';

no rows selected

gyj@OCM>  select * from role_sys_privs where role='MGR_ROLE';

no rows selected

gyj@OCM> select * from role_tab_privs where role='MGR_ROLE';

no rows selected

5、根据题意给角色MGR_ROLE授权

gyj@OCM>  grant create role  to MGR_ROLE; 

Grant succeeded.

gyj@OCM>  grant create user to MGR_ROLE;

Grant succeeded.
gyj@OCM> grant select any table to MGR_ROLE;

Grant succeeded.

6、再次查询ROLE

gyj@OCM> select * from dba_role_privs where  grantee='MGR_ROLE';

no rows selected

gyj@OCM> select * from role_sys_privs where role='MGR_ROLE';

ROLE                           PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
MGR_ROLE                       SELECT ANY TABLE                         NO
MGR_ROLE                       CREATE ROLE                              NO
MGR_ROLE                       CREATE USER                              NO

gyj@OCM> select * from role_tab_privs where role='MGR_ROLE';

no rows selected

7、利用OEM查，与题意吻合

8、用SKD用户登录查一下自己的当前权限

gyj@OCM> conn SKD/SKD
Connected.
skd@OCM> 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.

10、用WITHADMIN OPTION给SKD用户授权角色

WITH ADMIN OPTION的意思是被授予该权限的用户有权将某个权限(如MGR_ROLE)授予其他用户或角色，取消是不级联的。

gyj@OCM> GRANT MGR_ROLE TO SKD WITH ADMIN OPTION;

Grant succeeded.

11、再次查SKD用户的当前权限，多出三条，即角色MGR_ROLE，就说现在SKD用户有13个系统权限。

skd@OCM> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE USER
CREATE TABLE
SELECT ANY TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE ROLE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE

13 rows selected.

12、建个用户a,并只授于CREATE SESSION 系统权限

gyj@OCM> create user a identified by a;

User created.

gyj@OCM> grant create session to a;

Grant succeeded.

13、查当前a用户只有一个创建会话的权限

gyj@OCM> conn a/a
Connected.
a@OCM> select * from session_privs;

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

答案A正确，能授于MGR_ROLE权限,但不能授于SKD用户下的其它权限，因为以上的第8步GRANT MGR_ROLE TO SKD WITH ADMIN OPTION;的操作加上了WITH ADMIN OPTION.

实验如下:

a@OCM> conn SKD/SKD
Connected.
skd@OCM> grant MGR_ROLE to a;

Grant succeeded.

skd@OCM> grant CREATE TABLE to a;
grant CREATE TABLE to a
*
ERROR at l