User creation, Roles and privileges in 12c.
User creation and role & privilege management changes slightly on Oracle 12c.
Now we have two types of user and roles & privileges such as Local and Common.
* Local User
* Common User
* Local roles and privileges
* Common roles and privileges.
Below are some points about this topic.
A CDB can contain only common User. By default any user created in a CBD is a common user.
Let’s create a Common User.
SQL> SQL> create user c##testuser1 identified by oracle; User created. SQL>
SQL> L 1* select username,user_id,account_status,profile,common,oracle_maintained from dba_users where oracle_maintained='N' SQL> / USERNAME USER_ID ACCOUNT_STATUS PROFILE COM O --------------- ---------- -------------------- ---------- --- - C##TESTUSER1 105 OPEN DEFAULT YES N C##CDB_USER 102 OPEN DEFAULT YES N C##TEST1 104 OPEN DEFAULT YES N SQL>
– A common user is visible and accessible on the root container and all pdb container.
Let’s check whether the common user is visible at PDB level.
Login to any PDB or switch to any PDB and check the dba_users dictionary view.
SQL> alter session set container=PDB20; Session altered. SQL> sho con_name CON_NAME ------------------------------ PDB20 SQL> set lines 200 pages 2000 SQL> col username for a20 SQL> select username,account_status,common from dba_users where oracle_maintained='N'; USERNAME ACCOUNT_STATUS COM -------------------- -------------------------------- --- TEST3 OPEN NO TEST2 OPEN NO TEST1 OPEN NO PDB_ADM OPEN NO C##TESTUSER1 OPEN YES C##DEEPAK OPEN YES C##SREE OPEN YES C##DEEPAK2 OPEN YES 8 rows selected. SQL> select username,account_status,common from dba_users where username='C##TESTUSER1'; USERNAME ACCOUNT_STATUS COM -------------------- -------------------------------- --- C##TESTUSER1 OPEN YES SQL>
– A common user can perform administrative action on root container and PDB containers only if provided with necessary roles and privileges.
Let’s try to login to a containter with the newly created common user.
SQL> SQL> conn c##testuser1/deepak@localhost:1521/pdb20 ERROR: ORA-01045: user C##TESTUSER1 lacks CREATE SESSION privilege; logon denied Warning: You are no longer connected to ORACLE. SQL>
As per the above snippet we are not able to even login to the PDB without necessary privilege.
– A common user name always start with the string “c##”, for example “c##tester”
– By default it is not possible to create without string “C##”, it is possible to create user with regular name only by setting the undocumented parameter “_ORACLE_SCRIPT”. (Ofcourse it is not recommended to set underscore parameter without consent from Oracle Support. Your database may be desupported if you set undocumented parameter without directive from Oracle support.)
Now let’s try to create common user without the C## string.
SQL> show parameter _Oracle_script
SQL>
SQL>
SQL> create user testuser2 identified by deepak;
create user testuser2 identified by deepak
*
ERROR at line 1:
ORA-65096: invalid common user or role name
SQL>
The undocumented parameter “_ORACLE_SCRIPT” is not set and we tried to create a cmmon user without the string, we encountered an error.
SQL> SQL> alter session set "_ORACLE_SCRIPT"=TRUE; Session altered. SQL> create user testuser1 identified by oracle; User created. SQL>
After setting the parameter we are able to create user without c## string.
Let’s check the user is whether common user and it is accessible from PDB level.
CDB level.
SQL> SET LINES 200 PAGES 2000 SQL> COL USERNAME FOR A20 SQL> select username,account_status,common from dba_users where username='TESTUSER1'; USERNAME ACCOUNT_STATUS COM -------------------- -------------------------------- --- TESTUSER1 OPEN YES SQL> SHO CON_NAME CON_NAME ------------------------------ CDB$ROOT SQL>
At PDB level
SQL> alter session set container=PDB20; Session altered. SQL> SHO CON_NAME CON_NAME ------------------------------ PDB20 SQL> SET LINES 200 PAGES 2000 SQL> COL USERNAME FOR A20 SQL> select username,account_status,common from dba_users where username='TESTUSER1'; no rows selected SQL>
Common users created without c## is not visible at PDB level.
When trying to login with this common user, we get invalid user instead of missing Create session privilege
SQL> conn testuser1/oracle@localhost:1521/pdb20 ERROR: ORA-01017: invalid username/password; logon denied Warning: You are no longer connected to ORACLE. SQL>
– After creating a common user we must provide necessary privilege to the common user.
– It is possible to restrict the common role and privilege for a common user to only a subset of PDB, and it is also possible to provide Common role and privilege to all PDBs at once.
– It is possible to provide common users different privilege in different containers.
Now let’s provide create session privilege to only the PDB20 container and check the login behaviour at various containers.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBORCL READ WRITE NO
4 DEEPAK1 READ WRITE NO
5 PDB20 READ WRITE NO
6 PDB21 READ WRITE NO
SQL>
SQL> grant create session to c##testuser1;
Grant succeeded.
SQL> select grantee,privilege,common,con_id from cdb_sys_privs where grantee='C##TESTUSER1';
GRANTEE PRIVILEGE COM CON_ID
--------------- ---------------------------------------- --- ----------
C##TESTUSER1 CREATE SESSION NO 1
SQL>
As checked in the cdb_sys_privs the privilege is granted only in the container 1 which is root container.
Let’s try logging into some PDBs.
We have tried to login to the containers PDB20 and PDB21, but we are not able to login as the role provided was local to CDB$ROOT container.
SQL> SQL> conn c##testuser1/deepak@localhost:1521/pdb20; ERROR: ORA-01045: user C##TESTUSER1 lacks CREATE SESSION privilege; logon denied Warning: You are no longer connected to ORACLE. SQL> conn c##testuser1/deepak@localhost:1521/pdb21; ERROR: ORA-01045: user C##TESTUSER1 lacks CREATE SESSION privilege; logon denied SQL>
We can provide common role to common user with either the option CONTAINER=ALL or CONTAINER=CURRENT.
Below examples shows how to set privilege at specific container level, for example PDB20
SQL> CONN / AS SYSDBA Connected. SQL> SQL> SHOW CON_NAME CON_NAME ------------------------------ CDB$ROOT SQL> ALTER SESSION SET CONTAINER=PDB20; Session altered. SQL> SHOW CON_NAME CON_NAME ------------------------------ PDB20 SQL> GRANT CREATE SESSION TO C##TESTUSER1 CONTAINER=CURRENT; Grant succeeded. SQL> select grantee,privilege,common,con_id from cdb_sys_privs where grantee='C##TESTUSER1'; GRANTEE PRIVILEGE COM CON_ID --------------- ---------------------------------------- --- ---------- C##TESTUSER1 CREATE SESSION NO 5 SQL> alter session set container=cdb$root; Session altered. SQL> select grantee,privilege,common,con_id from cdb_sys_privs where grantee='C##TESTUSER1'; GRANTEE PRIVILEGE COM CON_ID --------------- ---------------------------------------- --- ---------- C##TESTUSER1 CREATE SESSION NO 1 C##TESTUSER1 CREATE SESSION NO 5
NOTE: Please not that the above privilege is not a Common privilege which is shown by the column COMMON.
Now create session has been granted to cdb$root and PDB20 containers. Let’s try to login to few PDBs.
SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> conn c##testuser1/deepak@localhost:1521/pdb20 Connected. SQL> SQL> show con_name CON_NAME ------------------------------ PDB20 SQL> SQL> disc Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> SQL> SQL> conn c##testuser1/deepak@localhost:1521/pdb21 ERROR: ORA-01045: user C##TESTUSER1 lacks CREATE SESSION privilege; logon denied SQL>
We are not able to login to the container PDB21 since we have not granted create session privilege to c##testuser1 for the container PDB21.
Now let’s give create session privilege to c##testuser1 with CONTAINER=ALL option and try the same.
SQL> conn / as sysdba Connected. SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> SQL> grant create session to c##testuser1 container=all; Grant succeeded. SQL> select grantee,privilege,common,con_id from cdb_sys_privs where grantee='C##TESTUSER1'; GRANTEE PRIVILEGE COM CON_ID --------------- ---------------------------------------- --- ---------- C##TESTUSER1 CREATE SESSION NO 1 C##TESTUSER1 CREATE SESSION YES 1 C##TESTUSER1 CREATE SESSION YES 4 C##TESTUSER1 CREATE SESSION YES 3 C##TESTUSER1 CREATE SESSION YES 6 C##TESTUSER1 CREATE SESSION NO 5 C##TESTUSER1 CREATE SESSION YES 5 7 rows selected. SQL>
Note that the privilege we grant with container=all option created a record in the above view for all the available containers (except PDB$SEED).
Try logging some PDBs.
SQL> conn c##testuser1/deepak@localhost:1521/pdb20 Connected. SQL> disc Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> conn c##testuser1/deepak@localhost:1521/pdb21 Connected. SQL> disc Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> conn c##testuser1/deepak@localhost:1521/pdborcl Connected. SQL> disc Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL>
We are able to login to all PDBs without problem.
Also note that if you grant a privilege with option CONTAINER=ALL to an username without preceeding c## string, it will NOT obtain privilege for all the containers. It will obtain the privilege only for the Root container (CDB).
Let’s create a common user without preceeding c## string at the root container. After creation we will grant create session with CONTAINER=ALL option. After granting the privilege check the dcitionary view cdb_sys_privs to confirm on which containers privilege are granted.
SQL> SQL> ALTER SESSION SET "_ORACLE_SCRIPT"=TRUE; Session altered. SQL> CREATE USER TESTER IDENTIFIED BY deepak; User created. SQL> grant create session to tester container=all; Grant succeeded. SQL> desc cdb_sys_privs Name Null? Type ----------------------------------------- -------- ---------------------------- GRANTEE VARCHAR2(128) PRIVILEGE VARCHAR2(40) ADMIN_OPTION VARCHAR2(3) COMMON VARCHAR2(3) CON_ID NUMBER SQL> select grantee,privilege,common,con_id from cdb_sys_privs where grantee='TESTER'; GRANTEE PRIVILEGE COM CON_ID ---------------------------------------- --- ---------- TESTER CREATE SESSION YES 1 SQL>
– A user which is created in a PDB is a local user.
– It is not possible to create a common user at PDB level.
– There is no restriction like the string “C##” while creating a local user. We can create users with regular name as usual.
– Its not possible to create a common user at a PDB, try doing so will result in error.
– The role and privlege that is provided at a PDB is local role and local privilege.
– It is not possible to provide common role and common privilege at PDB level. Trying do so will result in error.
Deepak Oracle DBA 