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