Manage common and local users, roles, privileges and profiles in PDBs

Common users and Local users: This concept came with Oracle Database 12c.

Common Users: a Common user is a user which have the same username and password authentication across PDBs of CDB.

Common users are created at CDB level and the common user can connect to the root and perform operations. By giving proper privileges to common users we can perform operations in PDBs too. Operations can be like granting privileges to local users of PDBs. A common user is a database user that has the same identity in the CDB root and in every existing and future PDBs in the CDB, or in an application root and in every existing and future application PDBs in the application container. An application common user does not require a prefix like a CDB common user.

Common users can be created in CDB$ROOT or in the application container.

Local Users: Local users are PDB specific users. We can have the same local name in PDBs.

Creating Common Users 

We can create common users using CONTAINER=ALL clause in creating user statement.

SQL> CREATE USER AA1 IDENTIFIED BY ORACLE CONTAINER=ALL;
CREATE USER AA1 IDENTIFIED BY ORACLE CONTAINER=ALL
            *
ERROR at line 1:
ORA-65096: invalid common user or role name

Oops.. It gives me an error invalid common user or role name.

Note: A common users name must start with ‘C##’ prefix.

Let’s try it again ..!!!

SQL> CREATE USER C##AA1 IDENTIFIED BY ORACLE CONTAINER=ALL;
User created.

While creating application common users we need to be connected to Application Container and when we synchronize application pdb, application common users will be replicated to Applications PDBs.

We can check common users from CDB_USERS view where common=’YES’

SQL> select username,profile,common,con_id from cdb_users where username='C##AA1';

USERNAME	     PROFILE		  COM	  CON_ID
-------------------- -------------------- --- ----------
C##AA1		     DEFAULT		  YES	       1
C##AA1		     DEFAULT		  YES	       3

We can see our recently created user here.

Common Roles in CDB :

We can create a common role by giving CONTAINER=ALL clause in CREATE ROLE statement.

Common Role name must start with C## prefix

SQL> CREATE ROLE C##ROLE1 CONTAINER=ALL;

Role created.

We can check common roles from CDB_ROLES view.

SQL> SELECT ROLE,ROLE_ID,COMMON,CON_ID FROM CDB_ROLES WHERE ROLE='C##ROLE1';

ROLE	      ROLE_ID COM     CON_ID
---------- ---------- --- ----------
C##ROLE1	  107 YES	   1
C##ROLE1	  108 YES	   3

Granting Permission to common roles and common users :

First I’m granting connect privilege to recently created role C##ROLE1 with a container=all clause.

SQL> GRANT CONNECT TO C##ROLE1 CONTAINER=ALL;

Grant succeeded.

Now I’m granting C##ROLE1 to C##AA1 user.

SQL> GRANT C##ROLE1 TO C##AA1 CONTAINER = ALL;

Grant succeeded.

Profile: We can create the common profile using create the profile and container=all statement.

SQL> CREATE PROFILE C##P1 LIMIT PASSWORD_REUSE_MAX 2 CONTAINER=ALL;

Profile created.

Stay tuned for More articles on Oracle Multitenant

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Telegram Channel: https://t.me/helporacle

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp