11gOCP 1z0-052 :2013-09-11 MGR_ROLE role
11gOCP 1z0-052 :2013-09-11 MGR_ROLE role
正确答案A
实验测试
1、创建用户:SKD
[html]
gyj@OCM> create user SKD identified by SKD;
User created.
2、授权
[html]
gyj@OCM> grant connect,resource to SKD;
Grant succeeded.
3、根据题意创建角色MGR_ROLE
[html]
gyj@OCM> create role MGR_ROLE;
Role created.
4、查询角色
[html]
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授权
[html]
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
[html]
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用户登录查一下自己的当前权限
[html]
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)授予其他用户或角色，取消是不级联的。
[html]
gyj@OCM> GRANT MGR_ROLE TO SKD WITH ADMIN OPTION;
Grant succeeded.
11、再次查SKD用户的当前权限，多出三条，即角色MGR_ROLE，就说现在SKD用户有13个系统权限。
[html]
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 系统权限
[html]
gyj@OCM> create user a identified by a;
User created.
gyj@OCM> grant create session to a;
Grant succeeded.
13、查当前a用户只有一个创建会话的权限
[html]
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.
实验如下:
[html]
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 line 1:
ORA-01031: insufficient privileges
答案B不正确,不但可以回收它自己授于的MGR_ROLE角色，(在授权时加上with admin option),而且实际也可回收不是他直接授于的MGR_ROLE角色，我这里先通过SKD用户给a用户，a用户再给hr用户，然后用SKD用户去收回hr用户的MGR_ROLE角色。当你搞不清楚这道题时到底是对还是错，小技巧带个only的一般来说都不对的。
这道题的英语有点绕，读了半天才终于明白，技术啊，好苦逼，懂了就明很容易明白，不懂再简单也觉得难。
[html]
a@OCM> conn SKD/SKD
Connected.
skd@OCM> grant mgr_role to a with admin option;
Grant succeeded.
skd@OCM> conn a/a
Connected.
a@OCM> grant mgr_role to hr;
Grant succeeded.
a@OCM> conn SKD/SKD
Connected.
skd@OCM> revoke mgr_role from hr;
