1z0-060 Upgrade to 12c: CONTAINER CLAUSE & Common granted privilege

If the current container is a pluggable database (PDB):

• Specify CONTAINER = CURRENT to revoke a locally granted system privilege, object privilege, or role from a local user, common user, local role, or common role. The privilege or role is revoked from the user or role only in the current PDB. This clause does not revoke privileges granted withCONTAINER = ALL.

If the current container is the root:

• Specify CONTAINER = CURRENT to revoke a locally granted system privilege, object privilege, or role from a common user or common role. The privilege or role is revoked from the user or role only in the root. This clause does not revoke privileges granted with CONTAINER = ALL.

• Specify CONTAINER = ALL to revoke a commonly granted system privilege, object privilege on a common object, or role from a common user or common role. The privilege or role is revoked from the user or role across the entire CDB. This clause can revoke only a privilege or role granted withCONTAINER = ALL from the specified common user or common role. This clause does not revoke privileges granted locally with CONTAINER = CURRENT. However, any locally granted privileges that depend on the commonly granted privilege being revoked are also revoked.

If you omit this clause, then CONTAINER = CURRENT is the default.

 

SQL> desc cdb_sys_privs
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
GRANTEE                                            VARCHAR2(128)
PRIVILEGE                                          VARCHAR2(40)
ADMIN_OPTION                                       VARCHAR2(3)
COMMON                                             VARCHAR2(3)
CON_ID                                             NUMBER

SQL> create user c##admin identified by password;

User created.

SQL> grant create table to c##admin container=ALL;

Grant succeeded.

SQL> grant create view to c##admin container=CURRENT;

Grant succeeded.

SQL> select privilege, common,con_id from cdb_sys_privs where grantee='C##ADMIN';

PRIVILEGE                                COM     CON_ID
---------------------------------------- --- ----------
CREATE VIEW                              NO           1
CREATE TABLE                             YES          1
CREATE TABLE                             YES          3

SQL> revoke create table from c##admin;
revoke create table from c##admin
*
ERROR at line 1:
ORA-65092: system privilege granted with a different scope to 'C##ADMIN'

SQL> revoke create table from c##admin container=CURRENT;
revoke create table from c##admin container=CURRENT
*
ERROR at line 1:
ORA-65092: system privilege granted with a different scope to 'C##ADMIN'

SQL> revoke create table from c##admin container=ALL;

Revoke succeeded.

SQL> select privilege, common,con_id from cdb_sys_privs where grantee='C##ADMIN';

PRIVILEGE                                COM     CON_ID
---------------------------------------- --- ----------
CREATE VIEW                              NO           1

SQL> revoke create view from c##admin container=ALL;
revoke create view from c##admin container=ALL
*
ERROR at line 1:
ORA-65092: system privilege granted with a different scope to 'C##ADMIN'

SQL> select privilege, common,con_id from cdb_sys_privs where grantee='C##ADMIN';

PRIVILEGE                                COM     CON_ID
---------------------------------------- --- ----------
CREATE VIEW                              NO           1

SQL> revoke create view from c##admin container=CURRENT;

Revoke succeeded.

SQL> select privilege, common,con_id from cdb_sys_privs where grantee='C##ADMIN';

no rows selected