Restricting Operations on PDBs Using PDB Lockdown Profiles

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

About PDB Lockdown Profiles

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

A PDB lockdown profile restricts the features and options available to users in a PDB. The PDB_OS_CREDENTIAL initialization parameter can specify a unique operating system user for a PDB to limit operating system access. Also, when the PATH_PREFIX and CREATE_FILE_DEST clauses are specified during PDB creation, they limit file system access.

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.

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.

How PDB Lockdown Profiles Work

PDB lockdown profiles are designed to restrict access at different levels for features that use shared identities.

A use case for might be the creation of lockdown profiles at high, medium, and low levels. The high level might greatly restrict access, whereas the low level might enable access.

When logged in to the CDB root or application root, create a lockdown profile by issuing the CREATE LOCKDOWN PROFILE statement, which supports the following optional clauses:

The user issuing the statement must have the CREATE LOCKDOWN PROFILE system privilege in the current container. You can add and remove restrictions with the ALTER LOCKDOWN PROFILE statement. The user must issue the ALTER statement in the CDB root or application root and must have the have ALTER LOCKDOWN PROFILE system privilege in the current container.

Specify a lockdown profile by using the PDB_LOCKDOWN initialization parameter. This parameter determines whether the PDB lockdown profile applies to a given PDB. You can set this parameter at the following levels:

If the PDB_LOCKDOWN parameter in a PDB is set to the name of a lockdown profile different from the container for this PDB (CDB or application container), then a set of rules govern the interaction between restrictions.

The following example shows how to create a PDB lockdown profile. You connect to the CDB root as a common user with the CREATE LOCKDOWN PROFILE privilege. You create a profile called medium that disables all ALTER SYSTEM statements except for ALTER SYSTEM FLUSH SHARED POOL:

CREATE LOCKDOWN PROFILE medium;
ALTER LOCKDOWN PROFILE medium DISABLE STATEMENT=('ALTER SYSTEM');
ALTER LOCKDOWN PROFILE medium ENABLE STATEMENT=('ALTER SYSTEM') CLAUSE=('FLUSH SHARED POOL');

You can connect as the same common user to each PDB that requires this profile, and then use ALTER SYSTEM to set the PDB_LOCKDOWN initialization parameter to medium. For example, you could set PDB_LOCKDOWN to medium for hrpdb, but not salespdb.

The following example creates a medium2 profile from medium:

CREATE LOCKDOWN PROFILE medium2 FROM medium;

PDB_OS_CREDENTIAL Initialization Parameter

When the database accesses an external procedure with the extproc agent, the PDB_OS_CREDENTIAL initialization parameter determines the identity of the operating system user employed when interacting with the operating system from a PDB.

Using an operating system user described by a credential whose name is specified as a value of the PDB_OS_CREDENTIAL initialization parameter can ensure that operating system interactions are performed as a less powerful user. In this way, the feature protects data belonging to one PDB from being accessed by users connected to another PDB. A credential is an object that is created using the CREATE_CREDENTIAL procedure in the DBMS_CREDENTIAL package.

The Oracle operating system user is usually a highly privileged user. Using this account for operating system interactions is not recommended. Also, using the same OS user for operating system interactions from different PDBs might compromise data belonging to a given PDB.

Features That Benefit from PDB Lockdown Profiles

Features that use shared identities benefit from PDB lockdown profiles.

A potential for elevation of privileges exists when PDBs share an identity. For example, identity can be shared at a network level, or when PDBs access common objects or connect through database links. To increase security, a CDB administrator may want to compartmentalize access, thereby restricting the operations that a user can perform in a PDB.

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:

The features that use shared identifies and that benefit from PDB lockdown profiles are in several categories.

PDB Lockdown Profile Inheritance

PDB lockdown profiles have inheritance behaviors between the CDB root, the application root, and their associated PDBs.

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:

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

  2. 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.

  3. Run the ALTER LOCKDOWN PROFILE statement to provide restrictions for the profile.

    For example:

    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:

Note: It is not necessary to restart the instance to enable the profile. When the ALTER SYSTEM SET PDB_LOCKDOWN statement completes, the profile rules take effect immediately.

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

  2. 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.

  3. 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.

    For example, to connect to the CDB root:

    CONNECT c##sec_admin
Enter password: password

  2. Run the DROP LOCKDOWN_PROFILE statement.

    For example:

    DROP LOCKDOWN PROFILE hr_prof2;

  3. Optionally, review the current list of 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
PRIVATE_DBAAS                                                    EMPTY
PUBLIC_DBAAS                                                     EMPTY
SAAS                                                             EMPTY