EasyReliableDBA: Manage Common and Local users, Roles, Privileges For Container Databases (CDB) and Pluggable Databases (PDB) in 19c

Sunday, 21 March 2021

Manage Common and Local users, Roles, Privileges For Container Databases (CDB) and Pluggable Databases (PDB) in 19c

From Oracle Database 12c onward, There is new terminology being used for user accounts. Oracle has come up with a concept called COMMON USERS and LOCAL USERS.
A common user can connect to any container on which it has sufficient privileges, whereas a local user is restricted to a specific PDB. Administrators can divide duties as follows:

An administrator uses a common account to manage a CDB or application container. Because a privilege is contained within the container in which it is granted, a local user on one PDB does not have privileges on other PDBs within the same CDB.
An administrator uses a local account to manage an individual PDB.

Ease of performance tuning
It is easier to collect performance metrics for a single database than for multiple databases. It is easier to size one SGA than 100 SGAs.
Fewer database patches and upgrades
It is easier to apply a patch to one database than to 100 databases, and to upgrade one database than to upgrade 100 databases.

Local user accounts are created individually at the PDB level
The standard user naming convention applies to local user names. The same local user can exist on different PDBs, but these USERIDs have no other commonality between them.
Local users can access only the PDB in which they are created and have no access to any of the other PDBs or even to the CDB.
You cannot create local users in the root.

The syntax is the same as in non-CDB.

SQL> CONNECT system@pdb1

SQL> CREATE USER george IDENTIFIED BY x;

A COMMON USER can perform administrative tasks that are specific to the CDB or PDB, such as plugging and unplugging a PDB.
COMMON USERs are the only ones that can navigate between containers that belong to a CDB. A common user can only be created in the root container
When creating a COMMON USER account there are specific rules that must be followed:

Must be connected to the root and have the commonly granted CREATE USER system privilege
The session’s current container must be CDB$ROOT
The user name must start with C## or c##
Explicitly designate a user account as a common user, specify the CONTAINER=ALL as part of the CREATE USER statement

Create a common user in the root container:

SQL> CREATE USER C##_GEORGE IDENTIFIED BY x

CONTAINER=ALL;

A common role is a database role, defined and created in the root container. A common role can be assigned only to common users and all the privileges cascade down to the PDB level. A local role, on the other hand, is created at the individual PDB level and can only contain roles and privileges applicable to administering at the individual PDB.
Common Roles Besides the local role, you can create common roles that are defined in every container. This way, it is easy to create at once a role that is replicated in all PDBs. It is at the creation time that you specify the nature of the role: local or common. Common roles as well as common users can only be created in the root by common users. Moreover, there cannot be any local roles defined in the root. All Oracle-supplied predefined roles are common roles.

The privileges are commonly referred to as local or common privileges, but to be more precise a privilege is either granted locally with the clause CONTAINER=CURRENT or commonly with the clause CONTAINER=ALL.
The same rule applies to roles: common roles can be granted commonly or locally to common users or roles. Common roles may contain privileges that apply across the CDB, that is, commonly granted for all containers, and can also contain locally granted privileges that apply only to an individual PDB, whereas local roles do not contain any commonly granted privileges.

EasyReliableDBA