In your multitenant container database (CDB) containing pluggable database (PDBs), you granted
the CREATE TABLE privilege to the common user C # # A_ADMIN in root and all PDBs. You
execute the following command from the root container:
SQL > REVOKE create table FROM C # # A_ADMIN;
What is the result?
A.
It executes successfully and the CREATE TABLE privilege is revoked from C # # A_ADMIN in
root only.
B.
It fails and reports an error because the CONTAINER=ALL clause is not used.
C.
It excludes successfully and the CREATE TABLE privilege is revoked from C # # A_ADMIN in
root and all PDBs.
D.
It fails and reports an error because the CONTAINER=CURRENT clause is not used.
E.
It executes successfully and the CREATE TABLE privilege is revoked from C # # A_ADMIN in
all PDBs.
Explanation:
REVOKE ..FROM
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 with
CONTAINER = 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.
Reference: Oracle Database SQL Language Reference 12c, Revoke
SQL> revoke create table from c##test;
revoke create table from c##test
*
ERROR at line 1:
ORA-65092: system privilege granted with a different scope to ‘C##TEST’
SQL> revoke create table from c##test container=all;
Revoke succeeded.
B! user is COMMON
SQL> show user
USER is “SYS”
SQL> show con_name
CON_NAME
——————————
CDB$ROOT
SQL> create user c##a_admin identified by password container=all;
User created.
SQL> grant create table to c##a_admin container=all;
Grant succeeded.
SQL> REVOKE create table FROM c##a_admin;
REVOKE create table FROM c##a_admin
*
ERROR at line 1:
ORA-65092: system privilege granted with a different scope to ‘C##A_ADMIN’
B is more logical based on explanation given in question.
What happens if instead you specify container=CURRENT; ??
It’s depend how the privilege was granted.
If you give the privilege with:
grant create table to c##a_admin container=all;
You only take back with:
revoke create table from c##a_admin container=all;
However if you give the privilege at container and at each of pdbs without container=all clause, you can remove the privilege only in cdb and A will be correct.
In the case of this question, I would mark A, because of this sentence:
“you granted
the CREATE TABLE privilege to the common user C # # A_ADMIN in root and all PDBs”
“you granted the CREATE TABLE privilege to the common user C # # A_ADMIN in root and all PDBs.”
According to this, it is more logically to assume that single command was used.
B
A – OK
tested:
SQL> create user C##A_ADMIN identified by ppp container=all;
User created.
SQL> grant create table to C##A_ADMIN;
Grant succeeded.
SQL> alter session set container = pdb1;
Session altered.
SQL> grant create table to C##A_ADMIN;
Grant succeeded.
SQL> alter session set container = CDB$ROOT;
Session altered.
SQL> revoke create table from C##A_ADMIN;
Revoke succeeded.
set container PDB and run select
SQL> SELECT * FROM dba_SYS_PRIVS WHERE GRANTEE like ‘C##%’;
GRANTEE PRIVILEGE
——————————————————————————————————————————– ———
C##A_ADMIN CREATE TABLE
You did a REVOKE CREATE TABLE and then when you select from DBA_SYS_PRIVS it still shows the privilege CREATE TABLE
?
I see it only showed the priv remaining in the PDB, and revoked from the CDB$ROOT.
B is correct answer. Tested.
We should user container=All in order to succeed the revoke statement. Or else it throws error ORA-65092: system privilege granted with a different scope to ‘C##A_ADMIN’
What if you specify container=CURRENT ???
A
Yes, A. It seems GRANT and REVOKE default to CONTAINER=CURRENT.
Role CREATE and DROP defaults to CONTAINER=ALL.
B
the question said
you granted the CREATE TABLE privilege to the common user C # # A_ADMIN in root and all PDBs.
so all PDBs means container=all.
to revoke you must specify container=all
other wise it will fail
Question Says: CREATE TABLE privilege to the common user C # # A_ADMIN in root and all PDBs. You execute the following command from the root container
SQL > REVOKE create table FROM C # # A_ADMIN;
B is a correct Answer.
SQL> grant create table to c##a container=all;
Grant succeeded.
SQL> revoke create table from c##a;
revoke create table from c##a
*
ERROR at line 1:
ORA-65092: system privilege granted with a different scope to ‘C##A’
SQL> revoke create table from c##a container=all;
Revoke succeeded.
B Correct
SQL> SELECT * FROM CDB_SYS_PRIVS WHERE GRANTEE=’C##_A_ADMIN’;
GRANTEE PRIVILEGE ADM COM CON_ID
—————————— —————————————- — — ———-
C##_A_ADMIN CREATE TABLE NO YES 7
C##_A_ADMIN CREATE TABLE NO YES 6
C##_A_ADMIN CREATE TABLE NO YES 5
C##_A_ADMIN CREATE TABLE NO YES 4
C##_A_ADMIN CREATE TABLE NO YES 3
C##_A_ADMIN CREATE TABLE NO YES 1
SQL> REVOKE create table FROM C##_A_ADMIN;
REVOKE create table FROM C##_A_ADMIN
*
ERROR at line 1:
ORA-65092: system privilege granted with a different scope to ‘C##_A_ADMIN’
Answer:B
13:26:57 SYS@MYCDB> create user C##USER identified by C##USER;
User created.
13:27:19 SYS@MYCDB> grant create table to C##USER container = ALL;
Grant succeeded.
13:27:54 SYS@MYCDB>select substr(GRANTEE,1,15) GRANTEE, substr(PRIVILEGE,1,20) PRIVILEGE, con_id from cdb_sys_privs where grantee=’C##USER’;
GRANTEE PRIVILEGE CON_ID
————— ——————– ————————————————–
C##USER CREATE TABLE 1
C##USER CREATE TABLE 4
13:28:14 SYS@MYCDB> revoke create table from C##USER;
revoke create table from C##USER
*
ERROR at line 1:
ORA-65092: system privilege granted with a different scope to ‘C##USER’
13:40:56 SYS@MYCDB> revoke create table from C##USER container=current;
revoke create table from C##USER container=current
*
ERROR at line 1:
ORA-65092: system privilege granted with a different scope to ‘C##USER’
13:42:22 SYS@MYCDB> revoke create table from C##USER container=all;
Revoke succeeded.
Please provide exact answer whoever completed exam successfully.
Case 1 : Answer is B
—————————-
SQL> conn / as sysdba
Connected.
SQL> grant create table to C##A_ADMIN container =all;
Grant succeeded.
SQL> revoke create table from C##A_ADMIN;
revoke create table from C##A_ADMIN
*
ERROR at line 1:
ORA-65092: system privilege granted with a different scope to ‘C##A_ADMIN’
SQL> revoke create table from C##A_ADMIN container =all;
Revoke succeeded.
Case 2 : Answer is A
—————————-
SQL> grant create table to C##A_ADMIN;
Grant succeeded.
SQL> alter session set container = pdb1;
Session altered.
SQL> show con_name
CON_NAME
——————————
PDB1
SQL> grant create table to C##A_ADMIN;
Grant succeeded.
SQL> conn / as sysdba
Connected.
SQL> revoke create table from C##A_ADMIN;
Revoke succeeded.
SQL> conn C##A_ADMIN/password1
Connected.
SQL> create table test01 as select * from dual;
create table test01 as select * from dual
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> conn C##A_ADMIN/password1@pdb1
Connected.
SQL> create table test01 as select * from dual;
Table created.
SQL>
Excellent. Thanks for real demos.
B if granted using grant create table to c##a_admin container=all;
Tested: B is correct
Testing Ousseini:
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
———- —————————— ———- ———-
2 PDB$SEED READ ONLY NO
3 PDBTST READ WRITE NO
4 PDBEXPL READ WRITE NO
SQL>
SQL> show con_id
CON_ID
——————————
1
SQL> create user c##testuser identified by test12;
User created.
SQL> grant create table to c##testuser container = ALL;
Grant succeeded.
SQL>
SQL> select substr(p.GRANTEE,1,15) GRANTEE, substr(p.PRIVILEGE,1,20) PRIVILEGE, p.con_id, c.name
from cdb_sys_privs p, v$pdbs c
where p.con_id = c.con_id(+) and p.grantee=’C##TESTUSER’;
GRANTEE PRIVILEGE CON_ID NAME
————— ————— ———- ——————————
C##TESTUSER CREATE TABLE 4 PDBEXPL
C##TESTUSER CREATE TABLE 3 PDBTST
C##TESTUSER CREATE TABLE 1
SQL> show con_name
CON_NAME
——————————
CDB$ROOT
SQL> show con_id
CON_ID
——————————
1
SQL> revoke create table from C##TESTUSER;
revoke create table from C##TESTUSER
*
ERROR at line 1:
ORA-65092: system privilege granted with a different scope to ‘C##TESTUSER’
SQL> revoke create table from C##TESTUSER container=all;
Revoke succeeded.
SQL> select substr(p.GRANTEE,1,15) GRANTEE, substr(p.PRIVILEGE,1,20) PRIVILEGE, p.con_id, c.name
from cdb_sys_privs p, v$pdbs c
where p.con_id = c.con_id(+) and p.grantee=’C##TESTUSER’;
no rows selected
SQL> drop user C##TESTUSER cascade;
User dropped.
A is right if the priv is granted locally in root and all pdbs (with container=current).
B is right if the priv is granted commonly (with container=all).
Tested both.
It’s not clear from the question which case it is, however I think the right answer in the test should be “B”.
A
CONTAINER Clause
If the current container is a PDB, then you can specify CONTAINER = CURRENT to change the attributes of a local user, or the container-specific attributes (such as the default tablespace) of a common user, in the current container. If the current container is the root, then you can specify CONTAINER = ALL to change the attributes of a common user across the entire CDB. If you omit this clause and the current container is a PDB, then CONTAINER = CURRENT is the default. If you omit this clause and the current container is the root, then CONTAINER = ALL is the default.
https://docs.oracle.com/database/121/SQLRF/statements_4003.htm#SQLRF01103
B is correct