There are two types of users in a pluggable environment: local and common. A local user is nothing more than a regular user that is created in a PDB. The local type of user in a PDB behaves the same as a user in a non-CDB environment. There is nothing special about administering local users; you learned how to set up users in the first couple of chapters.
A common user is one that exists in the root container and in every PDB. This type of user must be initially created in the root container and is automatically created in all existing PDBs as well as in any PDBs created in the future.
The SYS and SYSTEM accounts are common users that Oracle creates automatically in a pluggable environment.
Common users are created with the string C## or c## at the start of the username. There is a parameter called COMMON_USER_PREFIX where you can change the prefix for the common user. They are easily identifiable with a C## prefix, and no local user can use the prefix C##, which will help enforce that the user is uniquely named across all PDBs. For instance, the following command creates a common user in all PDBs:
SQL> create user c##dba identified by “Cr4zyPa$$word!”
Common users must be granted privileges from within each pluggable database. In other words, if you grant privileges to a common user while connected to the root container, this does not cascade to the PDBs.
If you need to grant a common user a privilege that spans PDBs, then create a common role, and assign it to the common user. A common user can log in to any container in which it has the CREATE SESSION privilege.
What use is there for a common user? One situation would be the performance of common DBA maintenance activities across PDBs not requiring SYSDBA-level privileges.
For example, you want to set up a DBA account that has the privileges to create users, grants, and so on, but you don’t want to use an account such as SYS (which has all privileges in all databases).
In this scenario, you would create a common DBA user and also create a DBA common role that contains the appropriate privileges. The common role would then be assigned to the common DBA.
Creating Common Roles
Much like you can create a common user that spans all PDBs, you can, in the same manner, create a common role. Common roles provide a single object to which you can grant privileges that are valid within all pluggable databases associated with the root container.
A common role is created in the root container and is automatically created in all associated PDBs as well as any PDBs created in the future. Like common users, common roles start with the same prefix string as the user C## or c##; for example:
SQL> create role c##dbaprivs container=all;
Next, you can assign privileges, as desired, to the common role. Here, the DBA role is assigned to the previously created role:
SQL> grant dba to c##dbaprivs container = all;
Now, if you assign this common role to a common user, the privileges associated with the role are in effect when the common user connects to any pluggable database associated with the root container:
SQL> grant c##dbaprivs to c##dba container = all;
Creating Local Users and Roles
A local user is a user that is only for that container and “local” to the container. This is especially used for application users and users that are authorized only in specific PDBs. The PDB may own a schema or can even be a schema-only account.
You can create a local user in a PDB by specifying the CONTAINER clause or without it. Connect to the PDB for which local users are to be created:
SQL> alter session set container=MMPDB; Session altered.
SQL> create user pdbdba identified by “Cr4zyPa$$word1” container=current; SQL> create user appuser identified by “Cr4zyPa$$word1”;
Roles are also common or local. All Oracle-supplied roles are common but can be granted to a local user.
