OCP-1Z0-052-V8.02-156题

156. Examine the following steps performed on a database instance:

1: The DBA grants the CREATE TABLE system privilege to the SKD user with ADMIN OPTION.

2: The SKD user creates a table.

3: The SKD user grants the CREATE TABLE system privilege to the HR user.

4: The HR user creates a table.

5: The DBA revokes the CREATE TABLE system privilege from SKD.

Which statement is true after step 5 is performed?

A.The table created by SKD is not accessible and SKD cannot create new tables.

B.The tables created by SKD and HR remain, but both cannot create new tables.

C.The table created by HR remains and HR still has the CREATE TABLE system privilege.

D.The table created by HR remains and HR can grant the CREATE TABLE system privilege to other

users.

Answer: C  

 

现象重现：

1、创建两个用户skd和rh（hr数据库里本来就有，故此区别），并授予create session权限，即连接的权限。

 

sys@TEST0910> create user skd identified by skd;

 

User created.

sys@TEST0910> grant create session to skd;

 

Grant succeeded.

 

sys@TEST0910> create user rh identified by rh;

 

User created.

 

sys@TEST0910> grant create session to rh;

 

Grant succeeded.

 

2、DBA 授权skd的create table的权限，skd连接，并创建一张表t1。

sys@TEST0910> grant create table to skd with admin option;

 

Grant succeeded.

sys@TEST0910> conn skd/skd

Connected.

skd@TEST0910> create table t1(id number);

 

Table created.

 

3、skd将create table授权给rh，rh创建一张表t2.

skd@TEST0910> grant create table to rh;

 

Grant succeeded.

 

skd@TEST0910> conn rh/rh

Connected.

rh@TEST0910> create table t2(id number);

 

Table created.

 

4、DBA将skd的create table收回。

rh@TEST0910> conn /as sysdba

Connected.

sys@TEST0910> revoke create table from skd;

 

Revoke succeeded.

 

A答案：skd用户可以访问原来创建的表，但是不能创建新的表。

skd@TEST0910> select * from t1;

 

no rows selected

 

skd@TEST0910> create table t3(id number);

create table t3(id number)

*

ERROR at line 1:

ORA-01031: insufficient privileges

 

skd@TEST0910> select * from session_privs;

 

PRIVILEGE

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

CREATE SESSION

 

B答案，skd和rh用户的表都会保留，skd不能创建新表，如A答案解析，但是rh用户可以创建新表。DBA只是把skd的创建表权限收回，Oracle系统权限不具有级联收回的功能。

skd@TEST0910> conn rh/rh

Connected.

rh@TEST0910> select * from t2;

 

no rows selected

rh@TEST0910> create table t4(id number);

 

Table created.

 

rh@TEST0910> select * from session_privs;

 

PRIVILEGE

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

CREATE SESSION

CREATE TABLE

 

C答案，如B答案解析，rh的表保留，rh用户也可以创建新表

 

D，因为没有级联授权，故rh不能授权给其他人。