common and local privileges in 12c

November 22, 2013 — Leave a comment

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 …

Advertisements

No Comments

Be the first to start the conversation!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s