You are administering a multitenant container database (CDB) that contains multiple
pluggable databases (PDBs). You are connected to cdb$root as the sys user. You execute
the commands:
SQL> CREATE USER C##ADMIN IDENTIFIED BY orcll23;
SQL> CREATE ROLE C##CONNECT;
SQL> GRANT CREATE SESSION, CREATE TABLE, SELECT ANY TABLE TO
C##CONNECT;
SQL> GRANT C##CONNECT to C##ADMIN CONTAINER=ALL;
Which statement is true about the c##connect role?
A.
It is created only in cdb$root and cannot be granted to the c##admin user with the
container=all clause.
B.
It is granted to the c##admin user only in the CDB.
C.
It is granted to the c##admin user in all PDBs and can be granted only to a local user in a
PDB.
D.
It is granted to the c##admin user in all PDBs and can be granted object and system
privileges for a PDB.
Here doesn’t exist right answer. The role is common, hence it can be granted to common user. Last command grant it for all containers and you can see it in CDB_SYS_PRIVS, but privileges was granted by 3’d command to role only for cdb$root, and c##admin can use they only in cdb$root. Not exactly B describe the result situation.
IS “B” CORRECT?
For me D is the correct answer.
https://docs.oracle.com/database/121/CNCPT/cdblogic.htm#CNCPT89261
Thanks Vonpire!
Answer is B
SQL> CREATE ROLE C##CONNECT; # ROLE is local to the ROOT Container
SQL> GRANT CREATE SESSION, CREATE TABLE, SELECT ANY TABLE TO C##CONNECT;
The above statement grants the privileges to the role only in the root container and not the PDBs as CONTAINER=ALL is missing.
This is similar to the example in the document link:
t4
SQL> CREATE ROLE c##admin CONTAINER=ALL;
SYSTEM creates a common role named c##admin. The clause CONTAINER=ALL makes the role a common role.
t5
SQL> GRANT SELECT ANY TABLE TO c##admin;
Grant succeeded.
SYSTEM grants the SELECT ANY TABLE privilege to the c##admin role. The absence of the CONTAINER=ALL clause makes the privilege local to the root. Thus, this common role contains a privilege that is exercisable only in the root.
D
It is B: It is granted to the c##admin user only in the CDB.
Because the role C##CONNECT was only crearted in the root containers…;
The answer is D:
https://docs.oracle.com/database/121/SQLRF/statements_6014.htm#SQLRF01311
“CONTAINER Clause
The CONTAINER clause applies when you are connected to a CDB. However, it is not necessary to specify the CONTAINER clause because its default values are the only allowed values.
– To create a common role, you must be connected to the root. You can optionally specify CONTAINER = ALL, which is the default when you are connected to the root.
– To create a local role, you must be connected to a PDB. You can optionally specify CONTAINER = CURRENT, which is the default when you are connected to a PDB.”
Correct Answer is C
It is granted to the c##admin user in all PDBs and can be granted only to a local user in a
PDB.
Because the a dummy role is granted to the user and that can be granted to the local user in a PDB
GRANT C##CONNECT to C##ADMIN CONTAINER=ALL;
D is right, I tried it out:
1) First of all I executed the statements given:
$ sqlplus / as sysdba
SQL> CREATE USER C##ADMIN IDENTIFIED BY orcl123;
User created.
SQL> CREATE ROLE C##CONNECT;
Role created.
SQL> GRANT CREATE SESSION, CREATE TABLE, SELECT ANY TABLE TO C##CONNECT;
Grant succeeded.
SQL> GRANT C##CONNECT to C##ADMIN CONTAINER=ALL;
Grant succeeded.
2) After that I changed container, logged into PDB1:
SQL> alter session set container=pdb1;
Session altered.
3) I queried the granted roles of C##ADMIN:
SQL> select GRANTED_ROLE from dba_role_privs where GRANTEE=’C##ADMIN’;
GRANTED_ROLE
——————————————————————————–
C##CONNECT
So, as we see the C##CONNECT-Role is granted to C##ADMIN in all containers. This proves that answer “D” is right.
So concerning the answer “C”.
The following steps prove that “C” is wrong:
1) I connected to the root and I created another common user named c##demo:
SQL> CREATE USER C##demo IDENTIFIED BY demo;
User created.
2) After that I connected to PDB1 as SYS and queried the roles of C##DEMO (no roles granted yet):
SQL> connect sys/oracle@pdb1 as sysdba
Connected.
SQL> select GRANTED_ROLE from dba_role_privs where GRANTEE=’C##DEMO’;
no rows selected
3) My attempt to grant a common role to a common user in PDB1 was successful !!!
SQL> grant C##CONNECT to C##demo;
Grant succeeded.
Conclusion: C is wrong because it states that the role C##CONNECT can be granted only to a local user in a PDB (“only” is wrong).
Right answer is D. This is my lab:
SQL> show con_name
CON_NAME
——————————
CDB$ROOT
SQL> create user c##admin identified by admin;
User created.
SQL> create role c##connect;
Role created.
SQL> grant create session, create table, select any table to c##connect;
Grant succeeded.
SQL> grant c##connect to c##admin container=all;
Grant succeeded.
SQL> show con_name
CON_NAME
——————————
CDB$ROOT
SQL> select granted_role from dba_role_privs where grantee=’C##ADMIN’;
GRANTED_ROLE
——————————
C##CONNECT
SQL> alter session set container=pdb01;
Session altered.
SQL> show con_name
CON_NAME
——————————
PDB01
SQL> select granted_role from dba_role_privs where grantee=’C##ADMIN’;
GRANTED_ROLE
——————————
C##CONNECT
##
## ==> This means A and B are WRONG. C##CONNECT role is created in PDBs, and can be granted to C##ADMIN also in PDBs.
SQL> alter session set container=pdb02;
Session altered.
SQL> select granted_role from dba_role_privs where grantee=’C##ADMIN’;
GRANTED_ROLE
——————————
C##CONNECT
#
# Idem.
SQL> select username from dba_users where username like ‘C##%’;
USERNAME
———–
C##COMUN ###—-> A common user.
C##ADMIN
SQL> show user
USER is “SYS”
SQL> show con_name;
CON_NAME
——————————
PDB02
SQL> grant C##CONNECT to C##COMUN;
Grant succeeded.
##
## ==> This means C is WRONG, because i can grant the common role C##CONNECT to another common user in PDB.
D
SQL> CREATE USER C##ADMIN IDENTIFIED BY orcll23;
User created.
SQL> CREATE ROLE C##CONNECT;
Role created.
SQL> GRANT CREATE SESSION, CREATE TABLE, SELECT ANY TABLE TO
2 C##CONNECT;
Grant succeeded.
SQL> GRANT C##CONNECT to C##ADMIN CONTAINER=ALL;
Grant succeeded.
SQL> select * from dba_sys_privs where grantee=’C##CONNECT’;
GRANTEE PRIVILEGE ADM COM
———- —————————————- — —
C##CONNECT CREATE TABLE NO NO
C##CONNECT SELECT ANY TABLE NO NO
C##CONNECT CREATE SESSION NO NO
SQL>
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
———- —————————— ———- ———-
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 MOUNTED
SQL> alter session set container=pdb1;
Session altered.
SQL> select * from dba_sys_privs where grantee=’C##CONNECT’;
no rows selected
B
For GRANT it is different than for CREATE USER
CONTAINER Clause (in GRANT)
If the current container is a pluggable database (PDB):
Specify CONTAINER = CURRENT to locally grant a system privilege, object privilege, or role to a user or role. The privilege or role is granted to the user or role only in the current PDB.
If the current container is the root:
Specify CONTAINER = CURRENT to locally grant a system privilege, object privilege, or role to a common user or common role. The privilege or role is granted to the user or role only in the root.
Specify CONTAINER = ALL to commonly grant a system privilege, object privilege on a common object, or role, to a common user or common role.
If you OMIT this clause, then CONTAINER = CURRENT is the DEFAULT.
GRANT CREATE SESSION, CREATE TABLE, SELECT ANY TABLE TO
C##CONNECT;
If you OMIT this clause, then CONTAINER = CURRENT is the DEFAULT.
So – B
Correct Answer is C.
Because common user also act as a local user inside the PDB. we can’t grant a role or privileges across PDBs for common users that means in a pdb we can grant with container=current(default) only the possible option. we can’t specify container=All clause. for same way in the CDB$ROOT container=all(default) only possible option. we can’t specify container=Current clause.
D