User, Role and Privilege management in Oracle 12c

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.


<pre class="brush: csharp"
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>

</pre>

- 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.

[code language="sql"]
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>
[/code]

- 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.

[code language="sql"]
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>
[/code]

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.)

At PDB level

[code language="sql"]
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>
[/code]

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

[code language="sql"]
SQL> conn testuser1/oracle@localhost:1521/pdb20
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.
SQL>
[/code]

- 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.

[code language="sql"]
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>

[/code]

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.

[code language="sql"]
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>
[/code]

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

[code language="sql"]
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

[/code]

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.

[code language="sql"]
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>

[/code]

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
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>
...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.

[code language="sql"]
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>
[/code]

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.

[code language="sql"]
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>

[/code]

- 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.