With oracle 12c Multitenant architecture, New terminologies for users has been introduced. LOCAL USER and COMMON USER.

SEE ALSO: Oracle 12c Multitenant Architecture

COMMON USER:

1.A common user is a DB user, which work perform an activity in all the containers including root container of the CDB.
2.A common user can only be created in root container.
3.Common username must start with C##.
4.While creating a common user, we can’t mention container=CURRENT. Either mention container=ALL or don’t use container keyword.
5.It is not recommended to create objects under common user

Create a common user:

If you are creating the user, by explicitly mentioning the default tablespace, then that tablespace should be present in all the containers i.e root container and PDBS.
If the tablespace is not present in any of the PDBs, then it will throw an error as below. check_tablespace_info_CDB

GRANTING PRIVILEGE TO COMMON USER:

Now let’s try to connect to a PDB:

Let’s check the privileges for the user in each PDB.

We can see only root container has this privilege, despite it is a common user.

Now I tried to grant privilege with the container=ALL option.

So, for a common user, we need to grant privilege with container=ALL, else PDBS will not inherit that privilege.

LOCAL USER:

1.Local users are the normal database user, which can be created only in PDBs. it is dedicated for that PDB. ( Means this user can’t be created in other PDBS).
2.With the appropriate privileges, a local user can access object in a common user’s schema

SEE ALSO – ORACLE DBA SCRIPT COLLECTION