Common User in Oracle Multi-Tenant Database

There are two types of users that you can find in a multi-tenant environment; one is Common User and Local User.

Common user has the same identity and authentication in all the PDBs, so the common user can log in to any PDB either directly using connect command (with user ID/ password to the PDB) or login to root container and switch to any PDB (If the user has switch any container privilege), but the roles and privileges on each PDB can be different for the same common user. 

Few important points about common user:

• The actions performed by a common user are for your administrative purposes. You should not create common user for business application usage

• User-Created CDB Common user must start with “C##” prefix. This prefix is defined by the parameter “COMMON_USER_PREFIX”, which has the default value of “C##”. But this value can be changed (But not recommended)

• Oracle-supplied common user such as SYS, SYSTEM does not have to start with C##

Some of the actions performed by Common User:

• Creating, opening, closing, unplugging, dropping PDBs
• Create common objects and common user in the root container
• Can perform operation on PDB as well, such as granting privilege to local user
• Grant Privileges to Common User and Common Role

Creating Common User Example:

CREATE USER c##hr_admin IDENTIFIED BY password

DEFAULT TABLESPACE data_ts

QUOTA 100M ON test_ts QUOTA 500K ON data_ts

TEMPORARY TABLESPACE temp_ts

CONTAINER = ALL;  

GRANT SET CONTAINER, CREATE SESSION TO c##hr_admin CONTAINER = ALL;

• c## prefix in the user ID indicates that it is a common user

• CONTAINER = ALL Clause is optional and is explicitly given to indicate that the user will be created on all containers.

• The default tablespace, Temporary Tablespace and other such objects used in the “Create User” command must exist on all the container (PDBs).

• SET CONTAINER Privilege gives the permission to switch to any container

Watch the below video for a detailed tutorial & demo on this topic.

https://youtu.be/eeyjWbZaAsk

=======================================================================

** Email to info@shreyantech.com to get the Tip of the Day in your mailbox.

** Connect with the author / trainer: https://suchitdba.blogspot.com/p/connect-with-author-database-trainer.html