Which three statements are true about the working of system privileges in a multitenant control
database (CDB) that has pluggable databases (PDBs)?
A.
System privileges apply only to the PDB in which they are used.
B.
Local users cannot use local system privileges on the schema of a common user.
C.
The granter of system privileges must possess the set container privilege.
D.
Common users connected to a PDB can exercise privileges across other PDBs.
E.
System privileges with the with grant option container all clause must be granted to a common
user before the common user can grant privileges to other users.
Explanation:
A, Not D: In a CDB, PUBLIC is a common role. In a PDB, privileges granted locally
to PUBLIC enable all local and common users to exercise these privileges in this PDB only.
C: A user can only perform common operations on a common role, for example, granting
privileges commonly to the role, when the following criteria are met:
The user is a common user whose current container is root.
The user has the SET CONTAINER privilege granted commonly, which means that the privilege
applies in all containers.
The user has privilege controlling the ability to perform the specified operation, and this privilege
has been granted commonly
Incorrect:
Note:
*Every privilege and role granted to Oracle-supplied users and roles is granted commonly except
for system privileges granted toPUBLIC, which are granted locally.
I think A-C-E correct
A C and E appears to be correct
ACE
Take the exam today, the question changed to ‘Which two …’ and option does not seem to change
Hi Handley, did you manage to clear the exam.? I have to give it next week.
Hi Handley, Requesting to please confirm that these set of questions are sufficient to clear the 12c OCP exam. I am planning it 18th April’16. Regards
I think A is not correct as per
http://oradb-srv.wlv.ac.uk/E16655_01/network.121/e17607/authorization.htm#BABGGBDG
Users can exercise system privileges only within the PDB in which they were granted.
System privileges can apply in the root and in all existing and future PDBs if the following requirements are met:
ACE
Common users can exercise privileges only in the context of the PDB to which they are connected. D is ruled out.
To grant a system privilege, the grantor must have the GRANT ANY PRIVILEGE system
privilege or have been granted the privilege WITH ADMIN OPTION.
When a user grants a privilege with the CONTAINER=ALL clause, the privilege becomes a
common privilege. The user must have the SET CONTAINER privilege for all the PDBs because
he grants the privilege to the same common user in each container.
A, C, E
AB
B: “A user cannot exercise local system privileges on a common user’s schema.”
From the student guide
Not C: SET CONTAINER privilege is not needed if container=current. For instance, if you are a local admin in a PDB, you don’t need SET CONTAINER to assign system privs.
Not E: You need ADMIN OPTION, not GRANT OPTION.
ACE
ACE
If only 2 then answer should be:
AB
AE
B – FALSE, for example a local user with CREATE ANY TABLE can create a table in common’s user schema in current container
C – FALSE, the grant of system privileges must have the GRANT ANY PRIVILEGE system
privilege or have been granted the privilege WITH ADMIN OPTION. He does not have to posses SET CONTAINER privilege if granting to local user or even common user in current container!
C,D is wrong:
Common users cannot exercise their privileges across other PDBs. They must first switch to the PDB that they want, and then exercise their privileges from there. To switch to a different container, the common user must have the SET CONTAINER privilege. The SET CONTAINER privilege must be granted either commonly or in the container to which the user is attempting to switch. Alternatively, the common user can start a new database session whose initial current container is the container this user wants, relying on the CREATE SESSION privilege in that PDB.
A,E is correct
Not sure about B, didnt test.
Sorry, C is correct:
Common roles are visible in every PDB in a multitenant environment. Commonly granted privileges that have been made to the common role apply in the root and all PDBs to which the grantor can connect, including PDBs that may be added later on, if the following requirements are met:
Both the grantor and the grantee are common users.
The grantor possesses the following:
-The commonly granted SET CONTAINER privilege
-The ADMIN OPTION for the common role that was granted
-The GRANT statement contains the CONTAINER=ALL clause.
Even change ‘grant option’ to ‘admin option’, E still incorrect.
Let’s see this example:
SQL> conn system/oracle1
Connected.
SQL> create user c##u identified by oracle1;
User created.
SQL> grant create session to c##u with admin option;
Grant succeeded.
SQL>
SQL>
SQL>
SQL> create user c##a identified by oracle1;
User created.
SQL> conn c##u/oracle1
Connected.
SQL> grant create session to c##a;
Grant succeeded.
SQL>
This example also shows C incorrect.
D incorrect, of course.
B correct (tested by another example)
So only A, B are correct.