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
CORRECT ANSWER: A, BECAUSE IF YOU DON’T write “CONTAINER”, FOR DEFAULT WILL BE container=current
CONTAINER = { CURRENT | ALL }
The following settings are possible:
CURRENT means that the statement applies only to the current container.
ALL means that the statement applies to all containers in the CDB, including the root and all of the PDBs.
JRD i tested it. answer B! lets you try it.
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’
Thank you
B
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”
As the wording of the question doesn’t say the privilege (create table) was *commonly* granted, I’m with Fabio here. Answer is A. Good effort though smbd.smth.
I agree with Fabio, becarefully about how the question says. the questions and posible answers not specify the way how the privilege has been created.
Then I would mark A
A
B: grant with CONTAINER=ALL
I tested it now and yes answer is B. It will fail unless you specify container=all to revoke the common system privilege given to a common user.
Answer is : A
SQL> select con_id,name from v$containers
2 /
CON_ID NAME
———- ——————————
1 CDB$ROOT
2 PDB$SEED
3 CDB1PDB1
SQL> alter pluggable database cdb1pdb1 open read only;
Pluggable database altered.
SQL> create pluggable database cdb1pdb2 from cdb1pdb1 file_name_convert=(‘/oradata/CDB1/CDB1PDB1′,’/oradata/CDB1/CDB1PDB2′);
Pluggable database created.
SQL> alter pluggable database cdb1pdb1 close;
Pluggable database altered.
SQL> alter pluggable database cdb1pdb1 open;
Pluggable database altered.
SQL> alter pluggable database cdb1pdb2 open;
Pluggable database altered.
SQL> create user c##admin identified by password container=all;
User created.
SQL> grant create table to c##admin;
Grant succeeded.
SQL> grant create session to c##admin container=all;
Grant succeeded.
SQL> desc dba_sys_privs
Name Null? Type
—————————————– ——– —————————-
GRANTEE VARCHAR2(128)
PRIVILEGE VARCHAR2(40)
ADMIN_OPTION VARCHAR2(3)
COMMON VARCHAR2(3)
#######CDB1PDB1
SQL> grant create table to c##admin;
Grant succeeded.
############CDB1PDB2
SQL> grant create table to c##admin;
Grant succeeded.
#########CDB$ROOT
SQL> revoke create table from c##admin;
Revoke succeeded.
SQL> select privilege from dba_sys_privs where grantee=’C##ADMIN’;
PRIVILEGE
—————————————-
CREATE SESSION
SQL>
####CDB1PDB1
SQL> select privilege from user_sys_privs where username=’C##ADMIN’;
no rows selected
SQL> connect c##admin@cdb1pdb1
Enter password:
Connected.
SQL> select privilege from user_sys_privs where username=’C##ADMIN’;
PRIVILEGE
—————————————-
CREATE TABLE
CREATE SESSION
#####CDB1PDB2
SQL> create table test1(col1 number);
Table created.
SQL> connect c##admin@cdb1pdb2
Enter password:
Connected.
SQL> create table test1(col1 number);
Table created.
SQL> select privilege from user_sys_privs where username=’C##ADMIN’;
PRIVILEGE
—————————————-
CREATE TABLE
CREATE SESSION
SQL>
Answer is A
A