18 Overview of the Multitenant Architecture

Purpose of PDBs

You can use PDBs to achieve the following goals:

• Store data specific to a particular application

  For example, a sales application can have its own dedicated PDB, and a human resources application can have its own dedicated PDB.

• Move data into a different CDB

  A database is "pluggable" because you can package it as a self-contained unit, and then move it into another CDB.

• Isolate grants within PDBs

  A local or common user with appropriate privileges can grant EXECUTE privileges on a package to PUBLIC within an individual PDB.

Names for PDBs

PDBs must be uniquely named within a CDB, and follow the same naming rules as service names. Moreover, because a PDB has a service with its own name, a PDB name must be unique across all CDBs whose services are exposed through a specific listener.

The first character of a user-created PDB name must be alphanumeric, with remaining characters either alphanumeric or an underscore (_). Because service names are case-insensitive, PDB names are case-insensitive, and are in upper case even if specified using delimited identifiers.

Scope for Names and Privileges in PDBs

PDBs have separate namespaces, which has implications for the following structures:

• Schemas

  A schema contained in one PDB may have the same name as a schema in a different PDB. These two schemas may represent distinct local users, distinguished by the PDB in which the user name is resolved at connect time, or a common user (see "Overview of Common and Local Users in a CDB").

• Objects

  An object must be uniquely named within a PDB, not across all containers in the CDB. This is true both of schema objects and nonschema objects. Identically named database objects and other dictionary objects contained in different PDBs are distinct from one another.

  An Oracle Database directory is an example of a nonschema object. In a CDB, common user SYS owns directories. Because each PDB has its own SYS schema, directories belong to a PDB by being created in the SYS schema of the PDB.

During name resolution, the database consults only the data dictionary of the container to which the user is connected. This behavior applies to object names, the PUBLIC schema, and schema names.

Database Links Between PDBs

In a CDB, all database objects reside in a schema, which in turn resides in a container. Because PDBs appear to users as non-CDBs, schemas must be uniquely named within a container but not across containers. For example, the rep schema can exist in both salespdb and hrpdb. The two schemas are independent (see Figure 18-4, "Users and Schemas in a CDB" for an example).

A user connected to one PDB must use database links to access objects in a different PDB. This behavior is directly analogous to a user in a non-CDB accessing objects in a different non-CDB.

Purpose of Data Dictionary Separation

In a newly created non-CDB that does not yet contain user data, the data dictionary contains only system metadata. For example, the TAB$ table contains rows that describe only Oracle-supplied tables, for example, TRIGGER$ and SERVICE$. The following graphic depicts three underlying data dictionary tables, with the red bars indicating rows describing the system:

If users create their own schemas and tables in this non-CDB, then the data dictionary now contains some rows that describe Oracle-supplied entities, and other rows that describe user-created entities. For example, the TAB$ dictionary table now has a row describing employees and a row describing departments:

In a CDB, the data dictionary metadata is split between the root and the PDBs. In Figure 18-1, the employees and departments tables reside in a PDB. The data dictionary for this user data also resides in the PDB. Thus, the TAB$ table in the PDB has a row for the employees table and a row for the departments table.

Figure 18-1 Data Dictionary Architecture in a CDB

Description of "Figure 18-1 Data Dictionary Architecture in a CDB"

The preceding graphic shows that the data dictionary in the PDB contains pointers to the data dictionary in the root. Internally, Oracle-supplied objects such as data dictionary table definitions and PL/SQL packages are represented only in the root. This architecture achieves two main goals within the CDB:

• Reduction of duplication

  For example, instead of storing the source code for the DBMS_ADVISOR PL/SQL package in every PDB, the CDB stores it only in CDB$ROOT, which saves disk space.

• Ease of database upgrade

  If the definition of a data dictionary table existed in every PDB, and if the definition were to change in a new release, then each PDB would need to be upgraded separately to capture the change. Storing the table definition only once in the root eliminates this problem.

Metadata and Object Links

The CDB uses an internal mechanism to achieve the preceding goals. Specifically, Oracle Database uses the following automatically managed pointers:

• Metadata links

  Oracle Database stores metadata about dictionary objects only in the root. For example, the column definitions for the OBJ$ dictionary table, which underlies the DBA_OBJECTS data dictionary view, exist only in the root. As depicted in Figure 18-1, the OBJ$ table in each PDB uses an internal mechanism called a metadata link to point to the definition of OBJ$ stored in the root.

  The data corresponding to a metadata link resides in its PDB, not in the root. For example, if you create table mytable in hrpdb and add rows to it, then the rows are stored in the PDB files. The data dictionary views in the PDB and in the root contain different rows. For example, a new row describing mytable exists in the OBJ$ table in hrpdb, but not in the OBJ$ table in the root. Thus, a query of DBA_OBJECTS in the root and DBA_OBJECTS in hrdpb shows different result sets.

• Object links

  In some cases, Oracle Database stores the data (not metadata) for an object only once in the root. For example, AWR data resides in the root. Each PDB uses an internal mechanism called an object link to point to the AWR data in the root, thereby making views such as DBA_HIST_ACTIVE_SESS_HISTORY and DBA_HIST_BASELINE accessible in each separate container.

Oracle Database automatically creates and manages object and metadata links. Users cannot add, modify, or remove these links.

Container Data Objects in a CDB

A container data object is a table or view containing data pertaining to multiple containers and possibly the CDB as a whole, along with mechanisms to restrict data visible to specific common users through such objects to one or more containers. Examples of container data objects are Oracle-supplied views whose names begin with V$ and CDB_.

All container data objects have a CON_ID column. Table 18-1 shows the meaning of the values for this column.

In a CDB, for every DBA_ view, a corresponding CDB_ view exists. The owner of a CDB_ view is the owner of the corresponding DBA_ view. The following graphic shows the relationship among the different categories of dictionary views:

When the current container is the root, a common user can query CDB_ views to see metadata for the root and for PDBs for which this user is privileged. When the current container is a PDB, however, a user can view data dictionary information for the current PDB only. To an application connected to a PDB, the data dictionary appears as it would for a non-CDB.

Table 18-2 shows a scenario involving queries of CDB_ views. Each row describes an action that occurs after the action in the preceding row.

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

SQL> SELECT COUNT(*) FROM CDB_USERS WHERE CON_ID=1;

COUNT(*)
--------
      41

SQL> SELECT COUNT(DISTINCT(CON_ID)) FROM CDB_USERS;
 
COUNT(DISTINCT(CON_ID))
-----------------------
                      4

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

SQL> SELECT COUNT(*) FROM CDB_USERS;
 
  COUNT(*)
----------
        45

Data Dictionary Storage in a CDB

The data dictionary that stores the metadata for the CDB as a whole is stored only in the system tablespaces. The data dictionary that stores the metadata for a specific PDB is stored in the self-contained tablespaces dedicated to this PDB. The PDB tablespaces contain both the data and metadata for an application back end. Thus, each set of data dictionary tables is stored in its own dedicated set of tablespaces.

Common Users in a CDB

A common user is a database user that has the same identity in the root and in every existing and future PDB. Every common user can connect to and perform operations within the root, and within any PDB in which it has privileges.

Every common user is either Oracle-supplied or user-created. Examples of Oracle-supplied common users are SYS and SYSTEM.

Figure 18-4 shows sample users and schemas in two PDBs: hrpdb and salespdb. SYS and c##dba are 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 18-4 Users and Schemas in a CDB

Description of "Figure 18-4 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.

• The name of every user-created common user must begin with the characters c## or C##. (Oracle-supplied common user names do not have this restriction.)

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

• The names of common users must contain only ASCII or EBCDIC characters.

• Every common user is uniquely named across all containers.

  A common user resides in the root, but must be able to connect to every PDB with the same identity.

• The schemas for a common user can differ in each container.

  For example, if c##dba is a common user that has privileges on multiple containers, then the c##dba schema in each of these containers may contain different objects.

Local Users in a CDB

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 particular PDB and owns a schema in this PDB.

  In Figure 18-4, 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 cannot be created in the root.

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

  When 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 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. Figure 18-4 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.

• Whether local users can access objects in a common schema depends on their user privileges.

  For example, the c##dba common user may create a table in the c##dba schema on hrpdb. Unless c##dba grants the necessary privileges to the local hr user on this table, hr cannot access it.

Table 18-4 describes a scenario involving the CDB in Figure 18-4. 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.

Common Roles in a CDB

A common role is a database role that exists in the root and in every existing and future PDB. Common roles are useful for cross-container operations (see "Cross-Container Operations"), ensuring that a common user has a role in every container.

Every common role is either user-created or Oracle-supplied. All Oracle-supplied roles are common, such as DBA and PUBLIC. User-created common roles must have names starting with C## or c##, and must contain only ASCII or EBCDIC characters. For example, a CDB administrator might create common user c##dba, and then grant the DBA role commonly to this user, so that c##dba has the DBA role in any existing and future PDB.

A user can only perform common operations on a common role, for example, granting privileges commonly to the role, when the following criteria are met:

• The user is a common user whose current container is root.

• The user has the SET CONTAINER privilege granted commonly, which means that the privilege applies in all containers.

• The user has privilege controlling the ability to perform the specified operation, and this privilege has been granted commonly (see "Roles and Privileges Granted Commonly in a CDB").

For example, to create a common role, a common user must have the CREATE ROLE and the SET CONTAINER privileges granted commonly. In the CREATE ROLE statement, the CONTAINER=ALL clause specifies that the role is common.

Local Roles in a CDB

A local role exists only in a single PDB, just as a role in a non-CDB exists only in the non-CDB. A local role can only contain roles and privileges that apply within the container in which the role exists.

PDBs in the same CDB may contain local roles with the same name. For example, the user-created role pdbadmin may exist in both hrpdb and salespdb. These roles are completely independent of each other, just as they would be in separate non-CDBs.

Principles of Privilege and Role Grants in a CDB

In a CDB, every act of granting, whether local or common, occurs within a specific container. The basic principles of granting are as follows:

• Both common and local phenomena may grant and be granted locally.

• Only common phenomena may grant or be granted commonly.

Local users, roles, and privileges are by definition restricted to a particular container. Thus, local users may not grant roles and privileges commonly, and local roles and privileges may not be granted commonly.

The following graphic illustrates these principles. In the top, a common user commonly grants a role or privilege to a common user or role. Consequently, the grant recipient has the privilege or role (p/r box) in all containers.

In the bottom section of the diagram, local users (L boxes) and common users (C boxes) make local grants to one another. Consequently, each user receives a grant of a privilege or role (p/r box) that is restricted to the container in which the grant occurred. The local grants have no applicability to common or local users and roles in other containers.

The following sections describe the implications of the preceding principles.

Privileges and Roles Granted Locally in a CDB

Roles and privileges may be granted locally to users and roles regardless of whether the grantees, grantors, or roles being granted are local or common. Table 18-5 explains the valid possibilities for locally granted roles and privileges.

^Footnote 1 Privileges in this role are available to the grantee only in the container in which the role was granted, regardless of whether the privileges were granted to the role locally or commonly.

^Footnote 2 Privileges in this role are available to the grantee only in the container in which the role was granted and created.

Roles and Privileges Granted Commonly in a CDB

Privileges and common roles may be granted commonly. According to the principles of granting in a CDB, users or roles may be granted roles and privileges commonly only if the grantees and grantors are both common; and if a role is being granted commonly, then the role itself must be common. Table 18-6 explains the possibilities for common grants.

^Footnote 1 Privileges that were granted commonly to a common role are available to the grantee across all containers. In addition, any privilege granted locally to a common role is available to the grantee only in the container in which that privilege was granted to the common role.

Grants to PUBLIC in a CDB

In a CDB, PUBLIC is a common role. In a PDB, privileges granted locally to PUBLIC enable all local and common users to exercise these privileges in this PDB only.

Assume that local user hr exists in hrpdb. This user locally grants the SELECT privilege on hr.employees to PUBLIC. Common and local users in hrpdb may exercise the privilege granted to PUBLIC. Users in salespdb or any other PDB do not have the privilege to query hr.employees in hrpdb.

Privileges granted commonly to PUBLIC enable all local users to exercise the granted privilege in their respective PDBs and enable all common users to exercise this privilege in the PDBs to which they have access. Oracle recommends that users do not commonly grant privileges and roles to PUBLIC.

Grants of Privileges and Roles: Scenario

Table 18-7 shows a scenario in which SYSTEM creates common user c##dba and tries to give this user privileges to query a table in the hr schema in hrpdb. The scenario shows how the CONTAINER clause affects grants of roles and privileges. The first column shows operations in CDB$ROOT. The second column shows operations in hrpdb.

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

SQL> CREATE USER c##dba 
IDENTIFIED BY password CONTAINER=ALL;

SQL> GRANT CREATE SESSION TO c##dba;

SQL> CREATE ROLE c##admin CONTAINER=ALL;

SQL> GRANT SELECT ANY TABLE 
  TO c##admin;
Grant succeeded.

SQL> GRANT c##admin TO c##dba;
SQL> EXIT;

SQL> CONNECT c##dba@hrpdb
Enter password: *******
ERROR: ORA-01045: user c##dba lacks 
CREATE SESSION privilege; logon denied

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

SQL> GRANT CONNECT,RESOURCE TO c##dba;
Grant succeeded.
SQL> EXIT

SQL> CONNECT c##dba@hrpdb
Enter password: *******
Connected.

SQL> SELECT COUNT(*) FROM hr.employees;
select * from hr.employees
                 *
ERROR at line 1:
ORA-00942: table or view does not exist

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

SQL> GRANT SELECT ANY TABLE TO
 c##admin CONTAINER=ALL;
Grant succeeded.

SQL> SELECT COUNT(*) FROM hr.employees;
select * from hr.employees
                 *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> GRANT c##admin TO c##dba 
  CONTAINER=ALL;
Grant succeeded.

SQL> SELECT COUNT(*) FROM hr.employees;

  COUNT(*)
----------
       107