common and local privileges in 12c

oracle 12c introduced common (valid for all containers) and local (valid for just a pdb) users. one might think that creating a common user and assigning privileges to that users results in the same set of privileges in all the pdbs. this is not the case:

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> create user c##1 identified by "test" container=all;

User created.

SQL> grant create session to c##1;

Grant succeeded.

SQL> connect c##1/test
Connected.
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> connect c##1/test@pdb1
ERROR:
ORA-01045: user C##1 lacks CREATE SESSION privilege; logon denied


Warning: You are no longer connected to ORACLE.

so, either you’ll have to grant the privilege in each container you want the user to be able to do a specfic task or you’ll need to grant it for all containers:

SQL> grant create session to c##1 container=all;

Grant succeeded.

SQL> connect c##1@pdb1 
Enter password: 
Connected.
SQL> 

what happens if a new pdb is created? do we need to re-grant the privilege for the new pdb?

SQL> conn / as sysdba
Connected.
SQL> CREATE PLUGGABLE DATABASE pdb2 ADMIN USER admin2 IDENTIFIED BY "test"
  STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M)
  DEFAULT TABLESPACE pbd1
    DATAFILE '/oradata/orcl/pdb1/pdb2_01.dbf' SIZE 250M AUTOEXTEND ON
  PATH_PREFIX = '/oradata/orcl/pdb2'
  FILE_NAME_CONVERT = ('/oradata/orcl/pdbseed/', '/oradata/orcl/pdb2');
  2    3    4    5    6  

Pluggable database created.

SQL> SQL> alter pluggable database pdb2 open;

Pluggable database altered.
SQL> connect c##1/test@pdb2
Connected.
SQL> show con_name

CON_NAME
------------------------------
PDB2
SQL> 

no. once granted on the root level the privilege is available on all the current pdbs and all pdbs that might get created in the future. what will happen if you unplug a pdb and plug it to a container which does not have the commen user? needs to be tested …