Managing Security for a Multitenant Environment

You can manage common and local users and roles for a multitenant environment by using SQL*Plus and Oracle Enterprise Manager.

In a multitenant environment, all users, including common users, can exercise their privileges only within the current container.

However, a user connected to the root can perform certain operations that affect other pluggable databases (PDBs). These operations include ALTER PLUGGABLE DATABASE, CREATE USER, CREATE ROLE, and ALTER USER. The common user must possess the commonly granted privileges that enable these operations. A common user connected to the root can see metadata pertaining to PDBs by way of the container data objects (for example, multitenant container database (CDB) views and V$ views) in the root, provided that the common user has been granted privileges required to access these views and his CONTAINER_DATA attribute has been set to allow seeing data about various PDBs. The common user cannot query tables or views in a PDB.

Common users cannot exercise their privileges across other PDBs. They must first switch to the PDB that they want, and then exercise their privileges from there. To switch to a different container, the common user must have the SET CONTAINER privilege. The SET CONTAINER privilege must be granted either commonly or in the container to which the user is attempting to switch. Alternatively, the common user can start a new database session whose initial current container is the container this user wants, relying on the CREATE SESSION privilege in that PDB.

Be aware that commonly granted privileges may interfere with the security configured for individual PDBs. For example, suppose an application PDB database administrator wants to prevent any user in the PDB from modifying a particular application common object. A privilege (such as UPDATE) granted commonly to PUBLIC or to a common user or common role on the object would circumvent the PDB database administrator’s intent.

Commonly and Locally Granted Privileges

In a multitenant environment, both common users and local users can grant privileges to one another.

Privileges by themselves are neither common nor local. How the privileges are applied depends on whether the privilege is granted commonly or granted locally.

Users can exercise system privileges only within the PDB in which they were granted.

For example, if a system privilege is locally granted to a common user A in a PDB B, user A can exercise that privilege only while connected to PDB B.

System privileges can apply in the root and in all existing and future PDBs if the following requirements are met:

  • The system privilege grantor is a common user and the grantee is a common user, a common role, or the PUBLIC role. Do not commonly grant system privileges to the PUBLIC role, because this in effect makes the system privilege available to all users.
  • The system privilege grantor possesses the ADMIN OPTION for the commonly granted privilege
  • The GRANT statement must contain the CONTAINER=ALL clause.

The following example shows how to commonly grant a privilege to the common user c##hr_admin.

CONNECT SYSTEM 
Enter password: password
Connected.
 
GRANT CREATE ANY TABLE TO c##hr_admin CONTAINER=ALL;

Object privileges on common objects applies to the object as well as all associated links on this common object.

These links include all metadata links, data links (previously called object links), or extended data links that are associated with it in the root and in all PDBs belonging to the container (including future PDBs) if certain requirements are met.

These requirements are as follows:

  • The object privilege grantor is a common user and the grantee is a common user, a common role, or the PUBLIC role.
  • The object privilege grantor possesses the commonly granted GRANT OPTION for the privilege
  • The GRANT statement contains the CONTAINER=ALL clause.

The following example shows how to grant an object privilege to the common user c##hr_admin so that he can select from the DBA_PDBS view in the CDB root or in any of the associated PDBs that he can access.

CONNECT SYSTEM
Enter password: password
Connected.
 
GRANT SELECT ON DBA_OBJECTS TO c##hr_admin 
CONTAINER=ALL;

Granting or Revoking Privileges to Access a PDB

You can grant and revoke privileges for PDB access in a multitenant environment.

To grant a privilege in a multitenant environment:

  • Include the CONTAINER clause in the GRANT or REVOKE statement.

Setting CONTAINER to ALL applies the privilege to all existing and future containers; setting it to CURRENT applies the privilege to the local container only. Omitting the CONTAINER clause applies the privilege to the local container. If you issue the GRANT statement from the root and omit the CONTAINER clause, then the privilege is applied locally.

You can use the GRANT statement to grant privileges in a multitenant environment.

Granting a Privilege in a Multitenant Environment

CONNECT SYSTEM
Enter password: password
Connected.
 
GRANT CREATE TABLE TO c##hr_admin CONTAINER=ALL;

Enabling Common Users to View CONTAINER_DATA Object Information

You can restrict view information for the X$ table and the V$, GV$ and CDB_* views when common users perform queries.

The X$ table and these views contain information about the application root and its associated application PDBs or, if you are connected to the CDB root, the entire CDB.

Restricting this information is useful when you do not want to expose sensitive information about other PDBs. To enable this functionality, Oracle Database provides these tables and views as container data objects. You can find if a specific table or view is a container data object by querying the TABLE_NAME, VIEW_NAME, and CONTAINER_DATA columns of the USER_|DBA_|ALL_VIEWS|TABLES dictionary views.

To find information about the default (user-level) and object-specific CONTAINER_DATA attributes:

  1. In SQL*Plus or SQL Developer, log in to the root.
  2. Query the CDB_CONTAINER_DATA data dictionary view.

For example:

COLUMN USERNAME FORMAT A13
COLUMN DEFAULT_ATTR FORMAT A7
COLUMN OWNER FORMAT A11
COLUMN OBJECT_NAME FORMAT A11
COLUMN ALL_CONTAINERS FORMAT A3
COLUMN CONTAINER_NAME FORMAT A10
COLUMN CON_ID FORMAT A6
 
SELECT USERNAME, DEFAULT_ATTR, OWNER, OBJECT_NAME, 
       ALL_CONTAINERS, CONTAINER_NAME, CON_ID 
FROM   CDB_CONTAINER_DATA 
ORDER BY OBJECT_NAME;
 
USERNAME    DEFAULT OWNER OBJECT_NAME ALL CONTAINERS CON_ID
----------- ------- ----- ----------- --- ---------- ------
C##HR_ADMIN N       SYS   V$SESSION   N   CDB$ROOT        1
C##HR_ADMIN N       SYS   V$SESSION   N   SALESPDB        1
C##HR_ADMIN Y                         N   HRPDB           1
C##HR_ADMIN Y                         N   CDB$ROOT        1
DBSNMP      Y                         Y                   1
SYSTEM      Y                         Y                   1
Enabling Common Users to Query Data in Specific PDBs

You can enable common users to access data pertaining to specific PDBs by adjusting the users’ CONTAINER_DATA attribute.

To enable common users to access data about specific PDBs:

  • Issue the ALTER USER statement in the root.

Setting the CONTAINER_DATA Attribute

This example shows how to issue the ALTER USER statement to enable the common user c##hr_admin to view information pertaining to the CDB$ROOT, SALES_PDB, and HRPDB containers in the V$SESSION view (assuming this user can query that view).

CONNECT SYSTEM
Enter password: password
Connected.
 
ALTER USER c##hr_admin
SET CONTAINER_DATA = (CDB$ROOT, SALESPDB, HRPDB) 
FOR V$SESSION CONTAINER=CURRENT;

In this specification:

  • SET CONTAINER_DATA lists containers, data pertaining to which can be accessed by the user.
  • FOR V$SESSION specifies the CONTAINER_DATA dynamic view, which common user c##hr_admin will query.
  • CONTAINER = CURRENT must be specified because when you are connected to the root, CONTAINER=ALL is the default for the ALTER USER statement, but modification of the CONTAINER_DATA attribute must be restricted to the root.

If you want to enable user c##hr_admin to view information that pertains to the CDB$ROOT, SALES_PDB, HRPDB containers in all CONTAINER_DATA objects that this user can access, then omit FOR V$SESSION. For example:

ALTER USER c##hr_admin
SET CONTAINER_DATA = (CDB$ROOT, SALESPDB, HRPDB) 
CONTAINER=CURRENT;

Managing Common Roles and Local Roles

In a multitenant environment, database roles can be specific to a PDB or used throughout the entire system container or application container.

A common role is a role whose identity and (optional) password are created in the root of a container and will be known in the root and in all existing and future PDBs belonging to that container.

A local role exists in only one PDB and can only be used within this PDB. It does not have any commonly granted privileges.

Privileges Required to Create, Modify, or Drop a Common Role

Only common users who have the commonly granted CREATE ROLE, ALTER ROLE, and DROP ROLE privileges can create, alter, or drop common roles.

Common users can also create local roles, but these roles are available only in the PDB in which they were created.

Rules for Creating Common Roles

  • Ensure that you are in the correct root. For the creation of common roles, you must be in the correct root, either the CDB root or the application root. You cannot create common roles from a PDB. To check if you are in the correct root, run one of the following:
    • To confirm that you are in the CDB root, you can issue the show_con_name command. The output should be CDB$ROOT.
    • To confirm that you are in an application root, verify that the following query returns YES:
SELECT APPLICATION_ROOT FROM V$PDBS WHERE CON_ID=SYS_CONTEXT('USERENV', 'CON_ID');
  • Ensure that the name that you give the common role starts with the value of the COMMON_USER_PREFIX parameter (which defaults to C##). Note that this requirement does not apply to the names of existing Oracle-supplied roles, such as DBA or RESOURCE.
  • Optionally, set the CONTAINER clause to ALL. As long as you are in the root, if you omit the CONTAINER = ALL clause, then by default the role is created as a common role for the CDB root or the application root.

Creating a Common Role

You can use the CREATE ROLE statement to create a common role.

  1. Connect to the root of the CDB or the application container in which you want to create the common role.
CONNECT SYSTEM
Enter password: password
Connected.
  1. Run the CREATE ROLE statement with the CONTAINER clause set to ALL.
CREATE ROLE c##sec_admin IDENTIFIED BY password CONTAINER=ALL;

Rules for Creating Local Roles

  • You must be connected to the PDB in which you want to create the role, and have the CREATE ROLE privilege.
  • The name that you give the local role must not start with the value of the COMMON_USER_PREFIX parameter (which defaults to C##).
  • You can include CONTAINER=CURRENT in the CREATE ROLE statement to specify the role as a local role. If you are connected to a PDB and omit this clause, then the CONTAINER=CURRENT clause is implied.
  • You cannot have common roles and local roles with the same name. However, you can use the same name for local roles in different PDBs. To find the names of existing roles, query the CDB_ROLES and DBA_ROLES data dictionary views.

Creating a Local Role

You can use the CREATE ROLE statement to create a role.

  1. Connect to the PDB in which you want to create the local role.
CONNECT [email protected]
Enter password: password
Connected.
  1. Run the CREATE ROLE statement with the CONTAINER clause set to CURRENT.
CREATE ROLE sec_admin CONTAINER=CURRENT;

Role Grants and Revokes for Common Users and Local Users

Role grants and revokes apply only to the scope of access of the common user or the local user.

Common users can grant and revoke common roles to and from other common users. A local user can grant a common role to any user in a PDB, including common users, but this grant applies only within the PDB.

The following example shows how to grant the common user c##sec_admin the AUDIT_ADMIN common role for use in all containers.

CONNECT SYSTEM
Enter password: password
Connected.
 
GRANT AUDIT_ADMIN TO c##sec_admin CONTAINER=ALL;

Similarly, the next example shows how local user aud_admin can grant the common user c##sec_admin the AUDIT_ADMIN common role for use within the hrpdb PDB.

CONNECT [email protected]
Enter password: password
Connected.
 
GRANT AUDIT_ADMIN TO c##sec_admin CONTAINER=CURRENT;

This example shows how a local user aud_admin can revoke a role from another user in a PDB. If you omit the CONTAINER clause, then CURRENT is implied.

CONNECT [email protected]
Enter password: password
Connected.
 
REVOKE sec_admin FROM psmith CONTAINER=CURRENT;

Restricting Operations on PDBs Using PDB Lockdown Profiles

You can use PDB lockdown profiles in a multitenant environment to restrict sets of user operations in pluggable databases (PDBs).

A PDB lockdown profile is a named set of features that controls a group of operations.

In some cases, you can enable or disable operations individually. For example, a PDB lockdown profile can contain settings to disable specific clauses that come with the ALTER SYSTEM statement.

PDB lockdown profiles restrict user access to the functionality the features provided, similar to resource limits that are defined for users. As the name suggests, you use PDB lockdown profiles in a CDB, for an application container, or for a PDB or application PDB. You can create custom profiles to accommodate the requirements of your site. PDB profiles enable you to define custom security policies for an application. In addition, you can create a lockdown profile that is based on another profile, called a base profile. You can configure this profile to be dynamically updated when the base profile is modified, or configure it to be static (unchanging) when the base profile is updated. Lockdown profiles are designed for both Oracle Cloud and on-premises environments.

When identities are shared between PDBs, elevated privileges may exist. You can use lockdown profiles to prevent this elevation of privileges. Identities can be shared in the following situations:

  • At the operating system level, when the database interacts with operating system resources such as files or processes
  • At the network level, when the database communicates with other systems, and network identity is important
  • Inside the database, as PDBs access or create common objects or they communicate across container boundaries using features such as database links

The features that use shared identifies and that benefit from PDB lockdown profiles are in the following categories:

  • Network access features. These are operations that use the network to communicate outside the PDB. For example, the PL/SQL packages UTL_TCP, UTL_HTTP, UTL_MAIL, UTL_SNMP, UTL_INADDR, and DBMS_DEBUG_JDWP perform these kinds of operations. Currently, ACLs are used to control this kind of access to share network identity.
  • Common user or object access. These are operations in which a local user in the PDB can proxy through common user accounts or access objects in a common schema. These kinds of operations include adding or replacing objects in a common schema, granting privileges to common objects, accessing common directory objects, granting the INHERIT PRIVILEGES role to a common user, and manipulating a user proxy to a common user.
  • Operating System access. For example, you can restrict access to the UTL_FILE or DBMS_FILE_TRANSFER PL/SQL packages.
  • Connections. For example, you can restrict common users from connecting to the PDB or you can restrict a local user who has the SYSOPER administrative privilege from connecting to a PDB that is open in restricted mode.

The general procedure for creating a PDB lockdown profile is to first create it in the CDB root or the application root using the CREATE LOCKDOWN PROFILE statement, and then use the ALTER LOCKDOWN PROFILE statement to add the restrictions.

To enable a PDB lockdown profile, you can use the ALTER SYSTEM statement to set the PDB_LOCKDOWN parameter. You can find information about existing PDB lockdown profiles by connecting to CDB or application root and querying the DBA_LOCKDOWN_PROFILES data dictionary view. A local user can find the contents of a PDB lockdown parameter by querying the V$LOCKDOWN_RULES dynamic data dictionary view.

Default PDB Lockdown Profiles

Oracle Database provides a set of default PDB lockdown profiles that you can customize for your site requirements.

By default, most of these profiles are empty. They are designed to be a placeholder or template for you to configure, depending on your deployment requirements.

Detailed information about these profiles is as follows:

  • PRIVATE_DBAAS incorporates restrictions that are suitable for private Cloud Database-as-a-Service (DBaaS) deployments. These restrictions are:
    • Must have the same database administrator for each PDB
    • Different users permitted to connect to the database
    • Different applications permitted

PRIVATE_DBAAS permits users to connect to the PDBs but prevents them from using Oracle Database administrative features.

  • SAAS incorporates restrictions that are suitable for Software-as-a-Service (SaaS) deployments. These restrictions are:
    • Must have the same database administrator for each PDB
    • Different users permitted to connect to the database
    • Must use the same application

The SAAS lockdown profile is more restrictive than the PRIVATE_DBAAS profile. Users can be different, but the application code is the same; users are prevented from directly connecting and must connect only through the application; and users are not granted the ability to perform any administrative features.

  • PUBLIC_DBAAS incorporates restrictions that are suitable for public Cloud Database-as-a-Service (DBaaS) deployments. The restrictions are as follows:
    • Different DBAs in each PDB
    • Different users
    • Different applications

The PUBLIC_DBAAS lockdown profile is the most restrictive of the lockdown profiles.

Creating a PDB Lockdown Profile

To create a PDB lockdown profile, you must have the CREATE LOCKDOWN PROFILE system privilege.

After you create the lockdown profile, you can add restrictions before enabling it.

  1. Connect to the CDB root or the application root as a user who has the CREATE LOCKDOWN PROFILE system privilege.

For example, to connect to the CDB root:

CONNECT c##sec_admin
Enter password: password
  1. Run the CREATE LOCKDOWN PROFILE statement to create the profile by using the following syntax:
CREATE LOCKDOWN PROFILE profile_name
[FROM static_base_profile | INCLUDING dynamic_base_profile];

In this specification:

  • profile_name is the name that you assign the lockdown profile. You can find existing names by querying the PROFILE_NAMES column of the DBA_LOCKDOWN_PROFILES data dictionary view.
    • FROM static_base_profile creates a new lockdown profile by using the values from an existing profile. Any subsequent changes to the base profile will not affect the new profile.
    • INCLUDING dynamic_base_profile also creates a new lockdown profile by using the values from an existing base profile, except that this new lockdown profile will inherit the DISABLE STATEMENT rules that comprise the base profile, as well as any subsequent changes to the base profile. If rules that are explicitly added to the new profile conflict with the rules in the base profile, then the rules in the base profile take precedence. For example, an OPTION_VALUE clause in the base profile takes precedence over the OPTION_VALUE clause in the new profile.

The following two PDB lockdown profile statements demonstrate how the inheritance works:

CREATE LOCKDOWN PROFILE hr_prof INCLUDING PRIVATE_DBAAS;
CREATE LOCKDOWN PROFILE hr_prof2 FROM hr_prof;

In the first statement, hr_prof inherits any changes made to the PRIVATE_DBAAS base profile. If a new statement is enabled for PRIVATE_DBAAS, then it is enabled for hr_prof. In the second statement, in contrast, when hr_prof changes, then hr_prof2 does not change because it is independent of its base profile.

  1. Run the ALTER LOCKDOWN PROFILE statement to provide restrictions for the profile.
ALTER LOCKDOWN PROFILE hr_prof DISABLE STATEMENT  = ('ALTER SYSTEM');
ALTER LOCKDOWN PROFILE hr_prof ENABLE STATEMENT = ('ALTER SYSTEM') clause = ('flush shared_pool');
ALTER LOCKDOWN PROFILE hr_prof DISABLE FEATURE = ('XDB_PROTOCOLS');

In the preceding example:

  • DISABLE STATEMENT = ('ALTER SYSTEM') disables the use of all ALTER SYSTEM statements for the PDB.
    • ENABLE STATEMENT = ('ALTER SYSTEM') clause = ('flush shared_pool') enables only the use of the FLUSH_SHARED_POOL clause for ALTER SYSTEM.
    • DISABLE FEATURE = ('XDB_PROTOCOLS') prohibits the use of the XDB protocols (FTP, HTTP, HTTPS) by this PDB

After you create a PDB lockdown profile, you are ready to enable it by using the ALTER SYSTEM SET PDB_LOCKDOWN SQL statement.

Enabling or Disabling a PDB Lockdown Profile

To enable or disable a PDB lockdown profile, use the PDB_LOCKDOWN initialization parameter

You can use ALTER SYSTEM SET PDB_LOCKDOWN to enable a lockdown profile in any of the following contexts:

  • CDB (affects all PDBs)
  • Application root (affects all application PDBs in the container)
  • Application PDB
  • PDB

When you set PDB_LOCKDOWN in the CDB root, every PDB and application root inherits this setting unless PDB_LOCKDOWN is set at the container level. To disable lockdown profiles, set PDB_LOCKDOWN to null. If you set this parameter to null in the CDB root, then lockdown profiles are disabled for all PDBs except those that explicitly set a profile within the PDB.

A CDB common user who has been commonly granted the SYSDBA administrative privilege or the ALTER SYSTEM system privilege can set PDB_LOCKDOWN only to a lockdown profile that was created in the CDB root. An application common user with the application common SYSDBA administrative privilege or the ALTER SYSTEM system privilege can set PDB_LOCKDOWN only to a lockdown profile created in an application root.

  1. Log in to the desired container as a user who has the commonly granted ALTER SYSTEM or commonly granted SYSDBA privilege.

For example, to enable the profile for all PDBs, log in to the CDB root:

CONNECT c##sec_admin
Enter password: password
  1. Run the ALTER SYSTEM SET PDB_LOCKDOWN statement.

For example, the following statement enables the lockdown profile named hr_prof for all PDBs:

ALTER SYSTEM SET PDB_LOCKDOWN = hr_prof;

The following statement resets the PDB_LOCKDOWN parameter:

ALTER SYSTEM RESET PDB_LOCKDOWN;

This variation of the preceding statement includes the SCOPE clause::

ALTER SYSTEM RESET PDB_LOCKDOWN SCOPE = BOTH;

The following statement disables all lockdown profiles in the CDB except those that are explicitly set at the PDB level:

ALTER SYSTEM SET PDB_LOCKDOWN = '' SCOPE = BOTH;

To find the names of PDB lockdown profiles, query the PROFILE_NAME column of the DBA_LOCKDOWN_PROFILES data dictionary view.

  1. Optionally, review information about the profiles by querying DBA_LOCKDOWN_PROFILES.

For example, run the following query:

SET LINESIZE 150
COL PROFILE_NAME FORMAT a20
COL RULE FORMAT a20
COL CLAUSE FORMAT a25
 
SELECT PROFILE_NAME, RULE, CLAUSE, STATUS FROM CDB_LOCKDOWN_PROFILES;

Sample output appears below:

PROFILE_NAME      RULE                 CLAUSE                    STATUS
----------------- -------------------- ------------------------- -------
HR_PROF           XDB_PROTOCOLS                                  DISABLE
HR_PROF           ALTER SYSTEM                                   DISABLE
HR_PROF           ALTER SYSTEM         FLUSH SHARED_POOL         ENABLE
HR_PROF2                                                         EMPTY
PRIVATE_DBAAS                                                    EMPTY
PUBLIC_DBAAS                                                     EMPTY
SAAS                                                             EMPTY

Dropping a PDB Lockdown Profile

To drop a PDB lockdown profile, you must have the DROP LOCKDOWN PROFILE system privilege and be logged into the CDB or application root.

You can find the names of existing PDB lockdown profiles by querying the DBA_LOCKDOWN_PROFILES data dictionary view.

  1. Connect to the CDB root or the application root as a user who has the DROP LOCKDOWN PROFILE system privilege.
CONNECT c##sec_admin
Enter password: password
  1. Run the DROP LOCKDOWN_PROFILE statement.
DROP LOCKDOWN PROFILE hr_prof2;
  1. Optionally, review the current list of profiles by querying DBA_LOCKDOWN_PROFILES.
SET LINESIZE 150
COL PROFILE_NAME FORMAT a20
COL RULE FORMAT a20
COL CLAUSE FORMAT a25
 
SELECT PROFILE_NAME, RULE, CLAUSE, STATUS FROM CDB_LOCKDOWN_PROFILES;

Sample output appears below:

PROFILE_NAME      RULE                 CLAUSE                    STATUS
----------------- -------------------- ------------------------- -------
HR_PROF           XDB_PROTOCOLS                                  DISABLE
HR_PROF           ALTER SYSTEM                                   DISABLE
HR_PROF           ALTER SYSTEM         FLUSH SHARED_POOL         ENABLE
PRIVATE_DBAAS                                                    EMPTY
PUBLIC_DBAAS                                                     EMPTY
SAAS                                                             EMPTY

Configuring Operating System Users for a PDB

Instead the oracle operating system user, you can set a specific user account to be the operating system user for that PDB.

If you do not set a specific user to be the operating system user for the PDB, then by default the PDB uses the oracle operating system user. For the root, you can use the oracle operating system user when you must interact with the operating system.

For better security, Oracle recommends that you set a unique operating system user for each PDB in a multitenant environment. Doing so helps to ensure that operating system interactions are performed as a less powerful user than the oracle operating system user, and helps to protect data that belongs to one PDB from being accessed by users who are connected to other PDBs.

Configuring an Operating System User for a PDB

The DBMS_CREDENTIAL.CREATE_CREDENTIAL procedure can set an operating system user for a PDB.

  1. Log in to the database instance root as a user who has the EXECUTE privilege for the DBMS_CREDENTIAL PL/SQL package and the ALTER SYSTEM system privilege.
sqlplus c##sec_admin
Enter password: password
  1. Run the DBMS_CREDENTIAL.CREATE_CREDENTIAL procedure to create an Oracle credential for the operating system user.

For example, to set the credential for a user named os_admin:

BEGIN 
 DBMS_CREDENTIAL.CREATE_CREDENTIAL (
    credential_name => 'PDB1_OS_USER',
    username        => 'os_admin',
    password        => 'password');
END;
/
  1. Connect to the PDB for which the operating system user will be used.
CONNECT cc##[email protected]
Enter password: password

To find the available PDBs, run the show pdbs command. To check the current PDB, run the show con_name command.

  1. Set the PDB_OS_CREDENTIAL initialization parameter for the user whose credential was set in Step 2.
ALTER SYSTEM SET PDB_OS_CREDENTIAL = PDB1_OS_USER SCOPE = SPFILE;

The PDB_OS_CREDENTIAL parameter is a static parameter, so you must set it using the SCOPE = SPFILE clause.

  1. Restart the database instance.
SHUTDOWN IMMEDIATE
STARTUP

Setting the Default Credential in a PDB

You can set the database property DEFAULT_CREDENTIAL for a specified PDB.

A default credential is useful when importing files from an object store into a PDB. If you do not specify a credential name when using impdp, then Oracle Data Pump and the object store module can use the DEFAULT_CREDENTIAL object to retrieve the user name and password. When running impdp without specifying a credential, you must prefix the dump file name with DEFAULT_CREDENTIAL:.

To set the default credential:

  1. Log in to a PDB with administrator privileges.
  2. Use an ALTER DATABASE statement to set the default credential.

For example, enter the following statement to set the credential to SYSTEM.HR_CRED.

ALTER DATABASE PROPERTY SET DEFAULT_CREDENTIAL = 'SYSTEM.HR_CRED';

Importing Data into a PDB Using the Default Credential

This example assumes that a default credential exists. The following command imports data from an object store , prefacing the URL with the string DEFAULT_CREDENTIAL:

impdp [email protected] table_exists_action=replace \
  dumpfile=DEFAULT_CREDENTIAL:https://example.com/ostore/obucket/myt.dmp

Using Application Contexts in a Multitenant Environment

An application context is a set of name-value pairs that Oracle Database stores in memory.

The context has a label called a namespace (for example, empno_ctx for an application context that retrieves employee IDs). This context enables Oracle Database to find information about both database and nondatabase users during authentication.

Inside the context are the name-value pairs (an associative array): the name points to a location in memory that holds the value. An application can use the application context to access session information about a user, such as the user ID or other user-specific information, or a client ID, and then securely pass this data to the database.

You can then use this information to either permit or prevent the user from accessing data through the application. You can use application contexts to authenticate both database and non-database users.

Application Contexts in a Multitenant Environment

Where you create an application in a multitenant environment determines where you must create the application context.

If an application is installed in the application root or CDB root, then it becomes accessible across the application container or system container and associated application PDBs. You will need to create a common application context in this root.

When you create a common application context for use with an application container, note the following:

  • You can create application contexts in a multitenant environment by setting the CONTAINER clause in the CREATE CONTEXT SQL statement. For example, to create a common application context in the application root, you must execute CREATE CONTEXT with CONTAINER set to ALL. To create the application context in a PDB, set CONTAINER to CURRENT.
  • You cannot use the same name for a local application context for a common application context. You can find the names of existing application contexts by running the following query:
SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE OBJECT_TYPE ='CONTEXT';
  • The PL/SQL package that you create to manage a common application context must be a common PL/SQL package. That is, it must exist in the application root or CDB root. If you create the application context for a specific PDB, then you must store the associated PL/SQL package in that PDB.
  • The name-value pairs that you set under a common session application context from an application container or a system container for a common application context are not accessible from other application containers or system containers when a common user accesses a different container.
  • The name-value pairs that you set under a common global application context from an application container or a system container, are accessible only within the same container in the same user session.
  • An application can retrieve the value of an application context whether it resides in the application root, the CDB root, or a PDB.
  • During a plug-in operation of a PDB into a CDB or an application container, if the name of the common application context conflicts with a PDB’s local application context, then the PDB must open in restricted mode. A database administrator would then need to correct the conflict before opening the PDB in normal mode.
  • During an unplug operation, a common application context retains its common semantics, so that later on, if the PDB is plugged into another CDB where a common application context with the same name exists, it would continue to behave like a common object. If a PDB is plugged into an application container or a system container, where the same common application context does not exist, then it behaves like a local object.

To find if an application context is a local application context or an application common application context, query the SCOPE column of the DBA_CONTEXT or ALL_CONTEXT data dictionary view.

Using Oracle Virtual Private Database in a Multitenant Environment

Oracle Virtual Private Database (VPD) creates security policies to control database access at the row and column level.

Essentially, Oracle Virtual Private Database adds a dynamic WHERE clause to a SQL statement that is issued against the table, view, or synonym to which an Oracle Virtual Private Database security policy was applied.

Oracle Virtual Private Database enforces security, to a fine level of granularity, directly on database tables, views, or synonyms. Because you attach security policies directly to these database objects, and the policies are automatically applied whenever a user accesses data, there is no way to bypass security.

When a user directly or indirectly accesses a table, view, or synonym that is protected with an Oracle Virtual Private Database policy, Oracle Database dynamically modifies the SQL statement of the user. This modification creates a WHERE condition (called a predicate) returned by a function implementing the security policy. Oracle Database modifies the statement dynamically, transparently to the user, using any condition that can be expressed in or returned by a function. You can apply Oracle Virtual Private Database policies to SELECT, INSERT, UPDATE, INDEX, and DELETE statements.

For example, suppose a user performs the following query:

SELECT * FROM OE.ORDERS;

The Oracle Virtual Private Database policy dynamically appends the statement with a WHERE clause. For example:

SELECT * FROM OE.ORDERS 
WHERE SALES_REP_ID = 159;

In this example, the user can only view orders by Sales Representative 159.

If you want to filter the user based on the session information of that user, such as the ID of the user, then you can create the WHERE clause to use an application context. For example:

SELECT * FROM OE.ORDERS 
 WHERE SALES_REP_ID = SYS_CONTEXT('USERENV','SESSION_USER');

Oracle Virtual Private Database in a Multitenant Environment

You can create Virtual Private Database policies in an application root for use throughout any associated application PDBs.

The CDB restriction applies to shared context sensitive policies and views related to Virtual Private Database policies as well. You cannot create a Virtual Private Database policy for an entire multitenant environment.

With regard to application containers, you can create Virtual Private Database policies to protect application common objects by applying the common policy to all PDBs that belong to the application root. In other words, when you install an application in the application root, all the common Virtual Private Database policies that protect the common objects will be applied to and immediately enforced for all PDBs in the application container.

Using Transport Layer Security in a Multitenant Environment

Transport Layer Security (TLS) can be used in a multitenant environment for application containers.

If you want to use Transport Layer Security (TLS) in a multitenant environment for an application container, then you must ensure that each PDB is able to use its own wallet with its own certificates for TLS authentication.

  • Because there is no individual sqlnet.ora file for each PDB, place the wallet in a subdirectory of the wallet directory where the name of the subdirectory is the GUID of the PDB that uses the wallet.

For example, suppose the WALLET_LOCATION parameter in sqlnet.ora is set as follows:

(SOURCE=(METHOD=FILE)(METHOD_DATA=
   (DIRECTORY=/home/oracle/wallet)))

Place each PDB’s wallet in the /home/oracle/wallet/PDB_GUID directory. You can find the existing PDBs and their GUIDs by querying the DBA_PDBS data dictionary view.

If the WALLET_LOCATION parameter is not specified, then you must place the PDB wallet in a leaf subdirectory of the default wallet path where the name of the subdirectory is the GUID of the PDB, and the name of the leaf subdirectory is wallet. For example:

$ORACLE_BASE/admin/db_unique_name/PDB_GUID/wallet

Or if the ORACLE_BASE environment variable is not set, then you can use the Oracle home:

$ORACLE_HOME/admin/db_unique_name/PDB_GUID/wallet

These default locations correspond to the default that is used by Oracle Enterprise User Security to locate wallets for authentication to LDAP.

Oracle Data Redaction in a Multitenant Environment

In a multitenant environment, Oracle Data Redaction policies apply only to the objects within the current pluggable database (PDB).

You cannot create a Data Redaction policy for a multitenant container database (CDB). This is because the objects for which you create Data Redaction policies typically reside in a PDB. If you have the SYSDBA privilege, then you can list all the PDBs in a CDB by running the SHOW PDBS command.

As with the CDB root, you cannot create Data Redaction policies in an application root.

Outline of Auditing in a Multitenant Environment

Auditing is the monitoring and recording of configured database actions, from both database users and nondatabase users. Nondatabase users are application users who are recognized in the database using the CLIENT_IDENTIFIER attribute.

You can base auditing on individual actions, such as the type of SQL statement executed, or on combinations of data that can include the user name, application, time, and so on. You can configure auditing for both successful and failed activities, and include or exclude specific users from the audit. In a multitenant environment, you can audit individual actions of the pluggable database (PDB) or individual actions in the entire multitenant container database (CDB).

Auditing is enabled by default. Audit records are written to the unified audit trail in a uniform format and are made available through the UNIFIED_AUDIT_TRAIL view.

Unified Auditing in a Multitenant Environment

You can use unified auditing in a multitenant environment.

You can apply audit settings to individual PDBs or to the CDB, depending on the type of policy. In a multitenant environment, each PDB, including the root, has its own unified audit trail.

Audit settings in a multitenant environment affect the following areas:

  • Unified audit policies created with the CREATE AUDIT POLICY and AUDIT statements: You can create policies for both the root and individual PDBs.
  • Audit records written to the syslog: On UNIX platforms, you can set the UNIFIED_AUDIT_COMMON_SYSTEMLOG initialization parameter in the CDB root to enable certain unified audit trail columns to be written to SYSLOG. On both Windows and UNIX, you can set the UNIFIED_AUDIT_SYSTEMLOG parameter in both the root and PDB level.
  • Fine-grained audit policies: You can create policies for individual PDBs only, not the root.
  • Purging the audit trail: You can perform purge operations for both the root and individual PDBs.

Example: Auditing the DBA Role in a Multitenant Environment

The CREATE AUDIT POLICY statement can audit roles in a multitenant environment.

The following example shows how to audit a predefined common role DBA in a multitenant environment.

Auditing the DBA Role in a Multitenant Environment

CREATE AUDIT POLICY role_dba_audit_pol 
 ROLES DBA
 CONTAINER = ALL;
 
AUDIT POLICY role_dba_audit_pol;

Unified Audit Policies or AUDIT Settings in a Multitenant Environment

In a multitenant environment, you can create unified audit policies for individual PDBs and in the root.

Local, CDB Common, and Application Common Audit Policies

An audit policy can be either a local audit policy, a CDB common audit policy, or an application common audit policy.

This applies to both unified audit policies and policies that are created using the AUDIT SQL statement.

  • Local audit policy. This type of policy can exist in either the root (CDB or application) or the PDB (CDB or application). A local audit policy that exists in the root can contain object audit options for both local and common objects. Both local and common users who have been granted the AUDIT_ADMIN role can enable local policies: local users from their PDBs and common users from the root or the PDB to which they have privileges. You can enable a local audit policy for both local and common users and roles.

You can create local audit policies for application local objects and application local roles, as well as system action options and system privilege options. You cannot enforce a local audit policy for a common user across all containers, nor can you enforce a common audit policy for a local user.

  • CDB common audit policy. This type of policy is available to all PDBs in the multitenant environment. Only common users who have been granted the AUDIT_ADMIN role can create and maintain common audit policies. You can enable common audit policies only for common users. You must create common audit policies only in the root. This type of policy can contain object audit options of only common objects, and be enabled only for common users. You can enable a common audit policy for common users and roles only.

You cannot enforce a common audit policy for a local user across all containers.

  • Application common audit policy. Similar to CDB common audit policies, this type of policy is available to all PDBs in the multitenant environment. You can create common audit policies for application common objects and application common roles, as well as system action options and system privilege options. You can only create this type of policy in the application root container, but you can enable it on both application common users and CDB common users. If you want to audit objects, then ensure that these objects are application common objects. You can determine whether an object is an application common object by querying the SHARING column of the DBA_OBJECTS data dictionary view.

By default, audit policies are local to the current PDB, for both CDB and application scenarios.

The following table explains how audit policies apply in different multitenant environments.

How Audit Policies Apply to the CDB Root, Application Root, and Individual PDBs

Audit Option TypeCDB RootApplication RootIndividual PDB
Common audit statement or audit policyApplies to CDB common usersApplies to CDB common usersApplies to CDB common users
Application container common audit statement or audit policyNot applicableApplies to CDB common users and are valid for the current application container onlyApplies to application container common usersApplies to CDB common users and are valid for this application container onlyApplies to application common users
Local audit statement or audit policyLocal configurations not allowedLocal configurations not allowedApplies to CDB common usersApplies to application common users
Traditional Auditing in a Multitenant Environment

In traditional auditing (not unified auditing), the AUDIT and NOAUDIT statements can audit statements and privileges in a multitenant environment.

To configure the audit policy to be either a local audit policy or a common audit policy, you must include the CONTAINER clause, as you normally do for other SQL creation or modification statements. If you want to audit an application container, then you can audit SQL statement and system privileges performed by local and common users and roles. The audit record will be created in the container in which the action was performed.

  • If you want to apply the AUDIT or NOAUDIT statement to the current CDB or application PDB, then in this PDB, you must set CONTAINER to CURRENT. For example:
AUDIT DROP ANY TABLE BY SYSTEM BY ACCESS CONTAINER = CURRENT;
  • If you want to apply the AUDIT or NOAUDIT statement to the entire multitenant environment, then in the CDB root, then you must set CONTAINER to ALL. For an application container, you would set it in the application root. For example:
AUDIT DROP ANY TABLE BY SYSTEM BY ACCESS CONTAINER = ALL;

To find if a traditional audit option is designed for use in an application container, perform a join query with the DBA_OBJ_AUDIT_OPTS and DBA_OBJECTS data dictionary views, by using the OWNER and OBJECT_NAME columns in both views, and the APPLICATION column in DBA_OBJECTS.

Configuring a Local Unified Audit Policy or Common Unified Audit Policy

The CONTAINER clause is specific to multitenant environment use for the CREATE AUDIT POLICY statement.

To create a local or common (CDB or application) unified audit policy in either the CDB environment or an application container environment, include the CONTAINER clause in the CREATE AUDIT POLICY statement.

  • Use the following syntax to create a local or common unified audit policy:
CREATE AUDIT POLICY policy_name
 action1 [,action2 ]
 [CONTAINER = {CURRENT | ALL}];

In this specification:

  • CURRENT sets the audit policy to be local to the current PDB.
  • ALL makes the audit policy a common audit policy, that is, available to the entire multitenant environment.

For example, for a common unified audit policy:

CREATE AUDIT POLICY dict_updates 
 ACTIONS UPDATE ON SYS.USER$, 
  DELETE ON SYS.USER$, 
  UPDATE ON SYS.LINK$, 
  DELETE ON SYS.LINK$ 
  CONTAINER = ALL;
Local Unified Audit Policy

The CREATE AUDIT POLICY statement can create a local unified audit policy in either the root or a PDB.

When you create a local unified audit policy in the root, it only applies to the root and not across the multitenant environment.

The following example shows a local unified audit policy that has been created by the common user c##sec_admin from a PDB and applied to common user c##hr_admin.

Local Unified Audit Policy

CONNECT c##[email protected]
Enter password: password
Connected.
 
CREATE AUDIT POLICY table_privs
 PRIVILEGES CREATE ANY TABLE, DROP ANY TABLE
 CONTAINER = CURRENT;
 
AUDIT POLICY table_privs BY c##hr_admin;
CDB Common Unified Audit Policy

The CREATE AUDIT POLICY statement can create a CDB common unified audit policy.

Common Unified Audit Policy

CONNECT c##sec_admin
Enter password: password
Connected.
 
CREATE AUDIT POLICY admin_pol
 ACTIONS CREATE TABLE, ALTER TABLE, DROP TABLE
 ROLES c##hr_mgr, c##hr_sup
 CONTAINER = ALL;
 
AUDIT POLICY admin_pol BY c##hr_admin;
Application Common Unified Audit Policy

For application container common unified audit policies, you can audit action options and system privilege options, and refer to common objects and roles.

You can create the application common audit policy only from the application root, and enable the policy for both application common users and CDB common users.

The following example shows how to create a policy that audits the application common user SYSTEM for the application container app_pdb. The audit policy audits SELECT actions on the SYSTEM.utils_tab table and on DROP TABLE actions on any of the PDBs in the container database, including the CDB root. The policy also audits the use of the SELECT ANY TABLE system privilege across all containers.

Application Common Unified Audit Policy

CONNECT c##[email protected]_pdb
Enter password: password
Connected.
 
CREATE AUDIT POLICY app_pdb_admin_pol
 ACTIONS SELECT ON hr_app_cdb.utils_tab, DROP TABLE
 PRIVILEGES SELECT ANY TABLE
 CONTAINER = ALL;
 
AUDIT POLICY app_pdb_admin_pol by SYSTEM, c##hr_admin;

In the preceding example, setting CONTAINER to ALL applies the policy only to all the relevant object accesses in the application root and on all the application PDBs that belong to the application root. It does not apply the policy outside this scope.

How Local or Common Audit Policies or Settings Appear in the Audit Trail

You can query unified audit policy views from either the root or the PDB in which the action occurred.

You can perform the following types of queries:

  • Audit records from all PDBs. The audit trail reflects audited actions that have been performed in the PDBs. For example, if user lbrown in PDB1 performs an action that has been audited by either a common or a local audit policy, then the audit trail will capture this action. The DBID column in the UNIFIED_AUDIT_TRAIL data dictionary view indicates the PDB in which the audited action takes place and to which the policy applies. If you want to see audit records from all PDBs, you should query the CDB_UNIFIED_AUDIT_TRAIL data dictionary view from the root.
  • Audit records from common audit policies. This location is where the common audit policy results in an audit record. The audit record can be generated anywhere in the multitenant environment—the root or the PDBs, depending on where the action really occurred. For example, the common audit policy fga_pol audits the EXECUTE privilege on the DBMS_FGA PL/SQL package, and if this action occurs in PDB1, then the audit record is generated in PDB1 and not in the root. Hence, the audit record can be seen in PDB1.

You can query the UNIFIED_AUDIT_TRAIL data dictionary view for the policy from either the root or a PDB if you include a WHERE clause for the policy name (for example, WHERE UNIFIED_AUDIT_POLICIES = 'FGA_POL').

The following example shows how to find the results of a common unified audit policy:

CONNECT c##sec_admin
Enter password: password
Connected.
 
SELECT DBID, ACTION_NAME, OBJECT_SCHEMA, OBJECT_NAME FROM CDB_UNIFIED_AUDIT_TRAIL WHERE DBUSERNAME = 'c##hr_admin';
46892-1
DBID        ACTION_NAME  OBJECT_SCHEMA  OBJECT_NAME
----------- -----------  -------------  -----------
653916017   UPDATE       HR             EMPLOYEES
653916018   UPDATE       HR             JOB_HISTORY
653916017   UPDATE       HR             JOBS