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 to PUBLIC, which are granted locally.
Only A and B are correct. E will be correct if you change “with grant option” with “with admin option”. System privileges can be given by granter, if the granter has granted “WITH ADMIN OPTION”. Otherwise, the granter needs to have “GRANT ANY PRIVILEGE” to be able to grant system privilege.
The given answer, ACE is wrong.
A is correct. The scope of use of a sys priv within a PDB is the PDB itself.
B is wrong. Create a local user (LOCAL1) in a PDB and a common user (C##COMMON1). Grant both CREATE SESSION. Grant CREATE TABLE to C##COMMON1 and ALTER ANY TABLE to LOCAL1. C##COMMON1 creates a table and LOCAL1 can alter its structure. It works!
C is wrong. Revoke ALTER ANY TABLE from LOCAL1 and re-grant it with ADMIN OPTION. All LOCAL1 has is CREATE SESSION and ALTER ANY TABLE WITH ADMIN OPTION. Login as LOCAL1 and grant ALTER ANY TABLE to C##COMMON1. It works and LOCAL1 does NOT have SET CONTAINER.
D is wrong. A common user in one PDB has no visibility into another PDB.
E is wrong. Assume “grant option” is actually “admin option”, but it’s still wrong. A common user could be granted a sys priv with admin option local to a PDB. It could then grant that sys priv to other users within that PDB. So, to say the common user had to have the sys priv granted with admin option and CONTAINER=ALL before it could grant that sys priv to others is incorrect.
H*T*H