Local And Common Users in Oracle Database 12c

The Oracle 12c multitenant database can have two classes of users, whose rights and privileges need to be understood and harnessed to in order to manage the databases. 

§        Local users

o       Users who exist only within a single pluggable database (PDB)  - i.e. the one where they are created

§        Common users

o       Users who exist in all containers current and future and can navigate across them.

The table below compares the most important features of the common user against the local user side by side, and discusses plugging operations as applicable to common users.

Common user	Local user
The scope of this user is all current and future containers.	The scope of the local user is limited to the container in which it was created.
The common user’s scope is administrative and system duties across multiple containers.	The local user does not have an existence or meaning outside of his container.
Once created, the common user will be visible across all containers. However, such a user needs explicit grants to be able to connect to and do things a pdb. And his grants can differ from pdb to pdb. The common user can switch containers - if granted the required privileges. e.g. ALTER SESSION SET CONTAINER=hrpdb;	The local user cannot connect to the root. The local user will also need explicit grants like create session, etc to do things within his container. The local user cannot connect to a container other than the one in which the user was created.
A number of common users like SYS and SYSTEM are Oracle-supplied. Non-Oracle supplied common users can be user-created but their names must begin with C##.	Local users can be Oracle-supplied (e.g. sample schemas like SCOTT,HR) or user-created. Local user’s names cannot begin with C##, because that would make them a common user.

In the remaining sections, the creation and administration of local and common users is briefly discussed.

Also covered briefly are local and common grants.

Creating users in a 12c multitenant container database

Creating a local user

Local users can be created by CDB administrative users (CDB or PDB administrator) after connecting to the specific PDB.  The steps to create a local user are shown below.

1. Connect to the specific PDB as an administrative user. To connect to the specific PDB (PDB1) as the PDB dba, you can use easy-connect syntax as shown below. But, it is very important to connect to the correct container.

2. Issuer the CREATE USER command. And issue the CREATE SESSION and CREATE TABLE GRANTS.

3. Test the connectivity. In the screen below HR is able to connect to PDB1 but not to the root CDB1. That’s because HR is a local user – local to the PDB1 container only.

Creating a common user

The common user is a new kind of user in 12c, found in 12c multitenant databases. Once created, this user will be known and exist in all containers current and future.

Such a user can receive a special kind of GRANT, known as a common grant. A common grant is respected in all containers current and future, and is a powerful privilege. It should be issued to administrative users only.

The common user’s purpose is to administer multiple pluggable databases. Such a dba may be responsible to start/stop, restore/recover multiple PDBs – but only the ones to which he has been granted the necessary access.

Whereas, administration is something a PDB administrator can do within his pluggable database only.

The steps to create a common user are shown below.

Note:

This is a created common user and not a Oracle-supplied one. Therefore, the username must begin with C##.

1. Connect to the ROOT container as a privileged user like SYS.
2. Issue the CREATE USER command, and GRANT the CREATE SESSION privilege only.

Below, the GRANT was specified with CONTAINER=ALL, therefore this is a COMMON grant, valid across all containers.

3. Test the connectivity.

In another shell, start sqlplus and connect using the credentials of the new user, to the root and to the PDB1 container. Then try to switch containers with ALTER SESSION SET CONTAINER.

C##DBA1 can directly connect to the container CDB2 and PDB1.

But SWITCHing containers is not possible yet. That needs a GRANT SET CONTAINER.

4. Still connected to the root as SYSDBA, do a local GRANT, GRANTING the SET CONTAINER privilege to C##DBA1.

5. Test the switching ability. Now, the c##dba1 user can only switch from any container to the root, not vice versa. Because of the local nature of the grant, it is only respected in the root.

6. From the root, as SYSDBA, now do a common GRANT to allow the C##DBA1 to switch containers freely.

7. Test the connectivity. Now the C##DBA1 user is able to switch containers.

8. From the PDB1 container, as SYSDBA, GRANT the SYSOPER privilege to the user C##DBA1.
   

9. Note that the grant is a local grant. It allows C##DBA1 to start and stop the only PDB1 pluggable database. Check the ability of C##DBA1 to alter the availability of PDB1.

10. Check the access to other containers – he cannot connect as SYSOPER to other containers.

Conclusion:

Due to consolidation and large number of databases inside a multitenant container database, role separation and access control become important. Hence Oracle Database 12c introduced different classes of administrative users with different job scopes and different levels of access to the database.

Common users can be created and granted privileges to do administrative tasks across multiple PDBs or specific sets of PDBs.

GRANTS are local to the current container unless CONTAINER=ALL is specified.