You are connected using SQL* Plus to a multitenant container database (CDB) with SYSDBA
privileges and execute the following sequence statements:
What is the result of the last SET CONTAINER statement and why is it so?
A.
It succeeds because the PDB_ADMIN user has the required privileges.
B.
It fails because common users are unable to use the SET CONTAINER statement.
C.
It fails because local users are unable to use the SET CONTAINER statement.
D.
If fails because the SET CONTAINER statement cannot be used with PDB$SEED as the target
pluggable database (PDB).
Explanation:
just test it. PDB_ADMIN is a LOCAL user! So answer C
Yes, the answer is indeed C.
Documented @ http://docs.oracle.com/cd/E16655_01/server.121/e17636/cdb_admin.htm
only common user can change container when have privilage SET CONTAINER
PDB_ADMIN is a local user
C
C
C is correct. PDB_ADMIN is the local user and therefore will get an error if he tries to set the container to PDB$SEED from the local PDB.
A is correct.
SQL> connect pdbadmin/oracle@localhost/pdb2
Connected.
SQL> show user
USER is “PDBADMIN”
SQL>
SQL> alter session set container=pdb2;
Session altered.
SQL> show user
USER is “PDBADMIN”
SQL>
SQL> show con_name
CON_NAME
——————————
PDB2
SQL>
SQL> alter session set container=PDB$SEED;
ERROR:
ORA-01031: insufficient privileges
Looks like PDBADMIN account is a common user thats why it succeeded.
I think it is C.
because it asks about last comand and even in the example it returns error message
SQL> alter session set container=PDB$SEED;
ERROR:
ORA-01031: insufficient privileges
C
The following prerequisites must be met to use the ALTER SESSION SET CONTAINER statement:
The current user must be a common user. The initial connection must be made using the SQL*Plus CONNECT command.
When altering a session to switch to a PDB as a common user that was not supplied with Oracle Database, the current user must be granted the SET CONTAINER privilege commonly or must be granted this privilege locally in the PDB.
C
Answer is C
https://docs.oracle.com/database/121/CNCPT/cdblogic.htm#CNCPT89248
Common Roles in a CDB
A common role is a database role that exists in the root and in every existing and future PDB. Common roles are useful for cross-container operations (see “Cross-Container Operations”), ensuring that a common user has a role in every container.
Every common role is either user-created or Oracle-supplied. All Oracle-supplied roles are common, such as DBA and PUBLIC. User-created common roles must have names starting with C## or c##, and must contain only ASCII or EBCDIC characters. For example, a CDB administrator might create common user c##dba, and then grant the DBA role commonly to this user, so that c##dba has the DBA role in any existing and future PDB.
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.
PDBADMIN account is a common user thats why it succeeded??????
is that true? is there a common user called PDBADMIN? .. I dont think so…
Tested C is the right answer:
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> create pluggable database new_pdb admin user pdb_admin identified by secret;
Pluggable database created.
SQL> alter pluggable database new_pdb open;
Pluggable database altered.
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
5 NEW_PDB READ WRITE NO
SQL>
SQL>
SQL> alter session set container = new_pdb;
Session altered.
SQL> sho pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
———- —————————— ———- ———-
5 NEW_PDB READ WRITE NO
SQL>
SQL> grant connect to pdb_admin;
Grant succeeded.
SQL> connect pdb_admin/secret@localhost/new_pdb
Connected.
SQL> show user
USER is “PDB_ADMIN”
SQL> select * from session_privs;
PRIVILEGE
—————————————-
SET CONTAINER
CREATE PLUGGABLE DATABASE
CREATE SESSION
SQL> alter session set container = pdb$seed;
ERROR:
ORA-01031: insufficient privileges
SQL>
SQL> conn / as sysdba
Connected.
SQL>
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
5 NEW_PDB READ WRITE NO
SQL>
SQL>
SQL> select * from all_users where username = ‘PDB_ADMIN’;
no rows selected
SQL> conn sys/Oraadmin12@localhost/new_pdb as sysdba
Connected.
SQL>
SQL> col USERNAME format a20
SQL> set lines 120
SQL>
SQL> select * from all_users where username = ‘PDB_ADMIN’;
USERNAME USER_ID CREATED COM O
——————– ———- ——— — –
PDB_ADMIN 102 17-MAR-16 NO N
SQL> conn / as sysdba
Connected.
SQL>
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
5 NEW_PDB READ WRITE NO
SQL>
SQL> alter pluggable database NEW_PDB close immediate;
Pluggable database altered.
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
5 NEW_PDB MOUNTED
SQL> drop pluggable database NEW_PDB including datafiles;
Pluggable database dropped.
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>
The answer correct this A:
Switching to a Container Using the ALTER SESSION Statement
When you are connected to a container as a common user, you can use the following statement to switch to a different container:
ALTER SESSION SET CONTAINER = container_name
http://docs.oracle.com/database/121/ADMIN/cdb_admin.htm#ADMIN13616