Within the context of either the system container (CDB) or an application container, a common user is a database user that has the same identity in the root and in every existing and future PDB within this container.

Every common user can connect to and perform operations within the root of its container, and within any PDB in which it has sufficient privileges. Some administrative tasks must be performed by a common user. Examples include creating a PDB and unplugging a PDB.

For example, SYSTEM is a CDB common user with DBA privileges. Thus, SYSTEM can connect to the CDB root and any PDB in the database. You might create a common user saas_sales_admin in the saas_sales application container. In this case, the saas_sales_admin user could only connect to the saas_sales application root or to an application PDB within the saas_sales application container.

Every common user is either Oracle-supplied or user-created. Examples of Oracle-supplied common users are SYS and SYSTEM. Every user-created common user is either a CDB common user, or an application common user.

The following figure shows sample users and schemas in two PDBs: hrpdb and salespdb. SYS and c##dba are CDB common users who have schemas in CDB$ROOT, hrpdb, and salespdb. Local users hr and rep exist in hrpdb. Local users hr and rep also exist in salespdb.

Figure 1-6 Users and Schemas in a CDB

Description of "Figure 1-6 Users and Schemas in a CDB"

Common users have the following characteristics:

• A common user can log in to any container (including CDB$ROOT) in which it has the CREATE SESSION privilege.

  A common user need not have the same privileges in every container. For example, the c##dba user may have the privilege to create a session in hrpdb and in the root, but not to create a session in salespdb. Because a common user with the appropriate privileges can switch between containers, a common user in the root can administer PDBs

• An application common user does not have the CREATE SESSION privilege in any container outside its own application container.

  Thus, an application common user is restricted to its own application container. For example, the application common user created in the saas_sales application can connect only to the application root and the PDBs in the saas_sales application container.

• The names of user-created CDB common users must follow the naming rules for other database users. Additionally, the names must begin with the characters specified by the COMMON_USER_PREFIX initialization parameter, which are c## or C## by default. Oracle-supplied common user names and user-created application common user names do not have this restriction.

  No local user name may begin with the characters c## or C##.

• Every common user is uniquely named across all PDBs within the container (either the system container or a specific application container) in which it was created.

  A CDB common user is defined in the CDB root, but must be able to connect to every PDB with the same identity. An application common user resides in the application root, and may connect to every application PDB in its container with the same identity.

A local user is a database user that is not common and can operate only within a single PDB.

Local users have the following characteristics:

• A local user is specific to a PDB and may own a schema in this PDB.

  In the example shown in "Characteristics of Common Users", local user hr on hrpdb owns the hr schema. On salespdb, local user rep owns the rep schema, and local user hr owns the hr schema.

• A local user can administer a PDB, including opening and closing it.

  A common user with SYSDBA privileges can grant SYSDBA privileges to a local user. In this case, the privileged user remains local.

• A local user in one PDB cannot log in to another PDB or to the CDB root.

  For example, when local user hr connects to hrpdb, hr cannot access objects in the sh schema that reside in the salespdb database without using a database link. In the same way, when local user sh connects to the salespdb PDB, sh cannot access objects in the hr schema that resides in hrpdb without using a database link.

• The name of a local user must not begin with the characters c## or C##.

• The name of a local user must only be unique within its PDB.

  The user name and the PDB in which that user schema is contained determine a unique local user. "Characteristics of Common Users" shows that a local user and schema named rep exist on hrpdb. A completely independent local user and schema named rep exist on the salespdb PDB.

The following table describes a scenario involving the CDB in "Characteristics of Common Users". Each row describes an action that occurs after the action in the preceding row. Common user SYSTEM creates local users in two PDBs.

SQL> CONNECT SYSTEM@hrpdb
Enter password: ********
Connected.

SQL> CREATE USER rep IDENTIFIED BY password; 

User created.

SQL> GRANT CREATE SESSION TO rep;

Grant succeeded.

SQL> CONNECT rep@salespdb
Enter password: *******
ERROR:
ORA-01017: invalid username/password; logon denied

SQL> CONNECT SYSTEM@salespdb
Enter password: ********
Connected.

SQL> CREATE USER rep IDENTIFIED BY password; 

User created.

SQL> GRANT CREATE SESSION TO rep;

Grant succeeded.

SQL> CONNECT rep@salespdb
Enter password: *******
Connected.

A common role exists either in the CDB root or an application root, and applies to every PDB within the root container (either the CDB or the application container).

Common roles are useful for cross-container operations, ensuring that a common user has a role in every PDB. Every common role is one of the following types:

• Oracle-supplied

  All Oracle-supplied roles, such as DBA and PUBLIC, are common to the CDB.

• User-created

  Create a common role by executing CREATE ROLE ... CONTAINER=ALL in either the CDB root or application root, which determines the container to which the role is common. The standard naming conventions apply. Additionally, the names of CDB common roles must begin with the characters specified by the COMMON_USER_PREFIX initialization parameter, which are c## or C## by default.

The scope of the role is the scope of the root within which it is defined. If you define the role in CDB$ROOT, then its scope is the entire CDB. If you define the role within application root, then its scope is the application container.

A local role exists only in a single PDB, and is thus completely independent of local roles in any other PDBs.

A local role can only contain roles and privileges that apply within the container in which the role exists. For example, if you create the local role pdbadmin in hrpdb, then the scope of this role is restricted to this PDB.

PDBs in the same CDB, or in the same application container, may contain local roles with the same name. For example, the user-created role pdbadmin may exist in both hrpdb and salespdb. However, these roles are completely independent of each other.

An unplugged PDB is a self-contained set of data files, and an XML metadata file that specifies the locations of the PDB files. To plug in an unplugged PDB, use the CREATE PLUGGABLE DATABASE statement with the USING clause.

When plugging in an unplugged PDB, you have the following options:

• Specify the XML metadata file that describes the PDB and the files associated with the PDB.

• Specify a PDB archive file, which is a compressed file that contains both the XML file and PDB data files. You can create a PDB by specifying the archive file, and thereby avoid copying the XML file and the data files separately.

The following graphic illustrates plugging in an unplugged PDB using the XML file.

Figure 1-15 Plugging In an Unplugged PDB

Description of "Figure 1-15 Plugging In an Unplugged PDB"

CREATE PLUGGABLE DATABASE salespdb USING '/disk1/usr/salespdb.xml' NOCOPY;