8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Multitenant : PDB Lockdown Profiles in Oracle Database 12c Release 2 (12.2)

A PDB lockdown profile allows you to restrict the operations and functionality available from within a PDB. This can be very useful from a security perspective, giving the PDBs a greater degree of separation and allowing different people to manage each PDB, without compromising the security of other PDBs with the same instance.

Basic Commands
Lockdown Options
Lockdown Features
Lockdown Statements
Considerations

Basic Commands

The basic process of creating, enabling, disabling and dropping a lockdown profile is relatively simple. The user administering the PDB lockdown profiles described here will need the CREATE LOCKDOWN PROFILE and DROP LOCKDOWN PROFILE system privileges. In these examples we will perform all these operations as the SYS user.

In the following example we create two PDB lockdown profiles in the root container. One will be used as the system default and one for a specific PDB.

CONN / AS SYSDBA

CREATE LOCKDOWN PROFILE default_pdb_lockdown;
CREATE LOCKDOWN PROFILE pdb1_specfic_lockdown;

We need to add some restrictions, but for the moment we'll keep this simple.

ALTER LOCKDOWN PROFILE default_pdb_lockdown DISABLE FEATURE = ('NETWORK_ACCESS');
ALTER LOCKDOWN PROFILE pdb1_specfic_lockdown DISABLE FEATURE = ('NETWORK_ACCESS', 'OS_ACCESS');

We set the PDB_LOCKDOWN parameter in the root container to set a default lockdown profile for all PDBs.

ALTER SYSTEM SET PDB_LOCKDOWN = default_pdb_lockdown;

We can see this setting is in place at the PDB level, but we can also override it by setting a PDB-specific lockdown profile.

ALTER SESSION SET CONTAINER = pdb1;
SHOW PARAMETER PDB_LOCKDOWN;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pdb_lockdown                         string      DEFAULT_PDB_LOCKDOWN
SQL>


ALTER SYSTEM SET PDB_LOCKDOWN = pdb1_specfic_lockdown;
SHOW PARAMETER PDB_LOCKDOWN;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pdb_lockdown                         string      PDB1_SPECFIC_LOCKDOWN
SQL>

We can reset the values of the PDB_LOCKDOWN parameter at the PDB level to return to the default lockdown profile. The changed doesn't appear to be visible until the PDB is restarted.

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER = pdb1;
ALTER SYSTEM RESET PDB_LOCKDOWN;

SHOW PARAMETER PDB_LOCKDOWN;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pdb_lockdown                         string      PDB1_SPECFIC_LOCKDOWN
SQL>


-- Restart PDB.
SHUTDOWN IMMEDIATE;
STARTUP;

SHOW PARAMETER PDB_LOCKDOWN;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pdb_lockdown                         string      DEFAULT_PDB_LOCKDOWN
SQL>

Reseting the PDB_LOCKDOWN parameter in the root container disables the default lockdown profile. Once again, the change doesn't seem to take place until an instance restart takes place.

CONN / AS SYSDBA
ALTER SYSTEM RESET PDB_LOCKDOWN;

SHOW PARAMETER PDB_LOCKDOWN;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pdb_lockdown                         string      DEFAULT_PDB_LOCKDOWN
SQL>


-- Restart the instance.
SHUTDOWN IMMEDIATE;
STARTUP;


SHOW PARAMETER PDB_LOCKDOWN;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pdb_lockdown                         string      
SQL>

PDB lockdown profiles are dropped as follows. If the instance or any PDBs references them they will still be dropped, and the lockdown profile will no longer be active, but the PDB_LOCKDOWN parameter will not be reset automatically.

CONN / AS SYSDBA

DROP LOCKDOWN PROFILE default_pdb_lockdown;
DROP LOCKDOWN PROFILE pdb1_specfic_lockdown;

Information about PDB lockdown profiles can be displayed using the DBA_LOCKDOWN_PROFILES view. You can use variations on the following query to check the impact of some of the commands used in this article. You may want to alter the format of the columns, depending on what you are trying to display.

SET LINESIZE 200

COLUMN profile_name FORMAT A30
COLUMN rule_type FORMAT A20
COLUMN rule FORMAT A20
COLUMN clause FORMAT A20
COLUMN clause_option FORMAT A20
COLUMN option_value FORMAT A20
COLUMN min_value FORMAT A20
COLUMN max_value FORMAT A20
COLUMN list FORMAT A20

SELECT profile_name,
       rule_type,
       rule,
       clause,
       clause_option,
       option_value,
       min_value,
       max_value,
       list,
       status
FROM   dba_lockdown_profiles
ORDER BY 1;

The database comes with three default PDB lockdown profiles called PRIVATE_DBAAS, PUBLIC_DBAAS and SAAS. These are empty profiles, containing no restrictions, which you can tailor to suit your own needs if you so wish.

The remainder of the article will discuss the types of restrictions available when planning a PDB lockdown profile. All the commands below reference a profile called MY_PROFILE, which can be created and dropped using the following commands.

CREATE LOCKDOWN PROFILE my_profile;
DROP LOCKDOWN PROFILE my_profile;

Lockdown Options

In Oracle 12c Release 2 (12.2) there are only two options (DATABASE QUEUING, PARTITIONING) that can be enabled or disabled in a lockdown profile, but this may increase in future.

Having no specific option restrictions in place is the equivalent of using the following.

ALTER LOCKDOWN PROFILE my_profile ENABLE OPTION ALL;

Here are some examples of enabling or disabling options.

-- Enable.
ALTER LOCKDOWN PROFILE my_profile ENABLE OPTION = ('DATABASE QUEUING');
ALTER LOCKDOWN PROFILE my_profile ENABLE OPTION = ('PARTITIONING');
ALTER LOCKDOWN PROFILE my_profile ENABLE OPTION ALL;
ALTER LOCKDOWN PROFILE my_profile ENABLE OPTION ALL EXCEPT = ('PARTITIONING');

-- Disable.
ALTER LOCKDOWN PROFILE my_profile DISABLE OPTION = ('DATABASE QUEUING');
ALTER LOCKDOWN PROFILE my_profile DISABLE OPTION = ('PARTITIONING');
ALTER LOCKDOWN PROFILE my_profile DISABLE OPTION ALL;
ALTER LOCKDOWN PROFILE my_profile DISABLE OPTION ALL EXCEPT = ('DATABASE QUEUING','PARTITIONING');

Using ALL EXCEPT doesn't really make sense with only two options available, but it will be useful if more options are added in future.

Lockdown Features

Features can be enabled or disabled individually, or in groups known as feature bundles. The feature bundles and their individual features are listed in the ALTER LOCKDOWN PROFILE documentation.

Having no specific feature restrictions in place is the equivalent of using the following.

ALTER LOCKDOWN PROFILE my_profile ENABLE FEATURE ALL;

Here are some examples of enabling or disabling feature bundles and features.

-- Enable/disable one or more features.
ALTER LOCKDOWN PROFILE my_profile ENABLE  FEATURE = ('UTL_HTTP');
ALTER LOCKDOWN PROFILE my_profile DISABLE FEATURE = ('UTL_HTTP', 'UTL_SMTP');

-- Enable/disable one or more feature bundles.
ALTER LOCKDOWN PROFILE my_profile ENABLE  FEATURE = ('NETWORK_ACCESS');
ALTER LOCKDOWN PROFILE my_profile DISABLE FEATURE = ('NETWORK_ACCESS', 'OS_ACCESS');

-- Enable/disable all features.
ALTER LOCKDOWN PROFILE my_profile ENABLE FEATURE ALL;
ALTER LOCKDOWN PROFILE my_profile DISABLE FEATURE ALL;

-- Enable/disable all features with bundle and/or feature exceptions.
ALTER LOCKDOWN PROFILE my_profile ENABLE FEATURE ALL EXCEPT = ('NETWORK_ACCESS');
ALTER LOCKDOWN PROFILE my_profile DISABLE FEATURE ALL EXCEPT = ('OS_ACCESS', 'UTL_HTTP', 'UTL_SMTP');

Lockdown Statements

At present four ALTER statements (ALTER DATABASE, ALTER PLUGGABLE DATABASE, ALTER SESSION, ALTER SYSTEM) can be restricted using a PDB lockdown profile.

The following examples show how to enable or disable entire commands or groups of them using ALL and ALL EXCEPT.

ALTER LOCKDOWN PROFILE my_profile ENABLE STATEMENT = ('ALTER DATABASE', 'ALTER PLUGGABLE DATABASE');
ALTER LOCKDOWN PROFILE my_profile DISABLE STATEMENT = ('ALTER DATABASE', 'ALTER PLUGGABLE DATABASE');

ALTER LOCKDOWN PROFILE my_profile ENABLE STATEMENT ALL EXCEPT = ('ALTER DATABASE', 'ALTER PLUGGABLE DATABASE');
ALTER LOCKDOWN PROFILE my_profile DISABLE STATEMENT ALL EXCEPT = ('ALTER DATABASE', 'ALTER PLUGGABLE DATABASE');

The scope of the restriction can be reduced using the CLAUSE, OPTION, MINVALUE, MAXVALUE options and values.

ALTER LOCKDOWN PROFILE my_profile DISABLE STATEMENT = ('ALTER PLUGGABLE DATABASE')
  CLAUSE = ('DEFAULT TABLESPACE', 'DEFAULT TEMPORARY TABLESPACE');

ALTER LOCKDOWN PROFILE my_profile DISABLE STATEMENT = ('ALTER SYSTEM')
  CLAUSE ALL EXCEPT = ('FLUSH SHARED_POOL');

-- Can't set CPU_COUNT higher than 1.
ALTER LOCKDOWN PROFILE my_profile DISABLE STATEMENT = ('ALTER SYSTEM')
  CLAUSE = ('SET') OPTION = ('CPU_COUNT') MAXVALUE = '1';

-- Can only set CPU_COUNT to values 1, 2 or 3.
ALTER LOCKDOWN PROFILE my_profile DISABLE STATEMENT = ('ALTER SYSTEM')
  CLAUSE = ('SET') OPTION = ('CPU_COUNT') MINVALUE = '1' MAXVALUE = '3';

The ALTER LOCKDOWN PROFILE documentation describes the available syntax.

Considerations

It should be obvious from the examples in the Lockdown Profile Basics section there is a flaw in this mechanism if you define poor lockdown profiles.

Imagine a scenario where you have a highly restrictive lockdown profile for one PDB, but a less restrictive default lockdown profile. If you don't restrict the ability to modify the PDB_LOCKDOWN parameter in the PDB with the highly restrictive profile, what's to stop the PDB administrator from resetting the PDB-level parameter and reverting to the less restrictive default lockdown profile?

If you are planning to use a variety of PDB lockdown profiles in a single instance, you need to define your lockdown profiles very carefully to prevent this type of mistake. This is a classic case of garbage-in, garbage-out.

Option, feature and statement restrictions can be combined into a single PDB lockdown profile.

Whilst testing it's easy to get yourself into a bit of a mess. Remember, you can always switch back to the root container and drop the problematic lockdown profile and start again.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.