PDB Lockdown Profiles

PDB Lockdown Profiles are a security feature in Oracle’s multitenant architecture that allows administrators to restrict specific operations and features within a pluggable database (PDB) . They act as an additional layer of control, effectively limiting what users can do even if they have been granted broad privileges . You can also restrict execution of any packages that allow network access, for example, UTL_SMTP.

What Can a Lockdown Profile Restrict?

A lockdown profile can impose restrictions across several categories, helping you enforce fine-grained security policies. The main types of rules you can set are:

ALTER SYSTEM

How to Create and Use a Lockdown Profile

The process involves three main steps, typically performed by a common user with the necessary privileges (like SYS) .

1. Create the Profile: Connect to the CDB root (CDB$ROOT) or an Application Root and create a new, empty profile .

   CREATE LOCKDOWN PROFILE my_secure_profile;

2. Add Restrictions: Use the ALTER LOCKDOWN PROFILE command to define the specific rules you need. Here are a couple of examples :

   • To disable network access and the Partitioning option:

     ALTER LOCKDOWN PROFILE my_secure_profile DISABLE FEATURE = ('NETWORK_ACCESS'); 
     
     ALTER LOCKDOWN PROFILE my_secure_profile DISABLE OPTION = ('PARTITIONING');

   • To prevent anyone in the PDB from altering the SGA_TARGET parameter:

     ALTER LOCKDOWN PROFILE my_secure_profile DISABLE STATEMENT = ('ALTER SYSTEM') CLAUSE = ('SET') OPTION = ('SGA_TARGET');

3. Assign the Profile to a PDB: Finally, apply the profile to one or more PDBs by setting the PDB_LOCKDOWN initialization parameter . You can do this at different levels:

   • For a single PDB: Connect to the target PDB and set the parameter.

     ALTER SESSION SET CONTAINER = my_pdb; 
     ALTER SYSTEM SET PDB_LOCKDOWN = my_secure_profile;

   • For all PDBs: Set the parameter while connected to the CDB root. This sets a default profile for all PDBs in the entire CDB .

Key Considerations and Inheritance Rules

To use lockdown profiles effectively, it’s important to understand their behavior:

• Scope and Precedence: A PDB can have only one active lockdown profile at a time . If a profile is set at the CDB root level, it applies to all PDBs. However, if you then set a different profile directly on a specific PDB, the PDB-level setting overrides the CDB-wide one .

• Profile Inheritance: When creating a new profile, you can base it on an existing one .

  • Static (FROM): The new profile is a copy of the base profile’s rules at the moment of creation. Later changes to the base profile do not affect the new profile .

  • Dynamic (INCLUDING): The new profile will inherit future changes made to the base profile. If a rule conflicts, the base profile’s rule takes precedence .

• Impact: Restrictions are PDB-wide and affect all users, including powerful ones like SYS and SYSTEM when they are connected to that PDB .

• Viewing Profiles: You can query the DBA_LOCKDOWN_PROFILES view to see which profiles exist and what rules they contain .

Example of creating and using PDB Lockdown Profiles :

We’ll build a profile that implements three common security restrictions:

1. Prevent users from changing the SGA_TARGET memory parameter.

2. Disable the Partitioning database option.

3. Disable all network access packages (like UTL_HTTP, UTL_SMTP).

This exercise will show you how to create a profile, add restrictions, apply it to a PDB, and then verify that the restrictions are working.

You will need to connect as a common user with SYSDBA privilege, typically the SYS user, to perform these steps .

Step 1: Create a New Lockdown Profile

First, connect to the CDB root (CDB$ROOT) and create an empty lockdown profile. We’ll call it secure_pdb_profile .

-- Connect to the CDB root 
CONNECT / AS SYSDBA 
ALTER SESSION SET CONTAINER = CDB$ROOT; 

-- Create the profile 
CREATE LOCKDOWN PROFILE secure_pdb_profile;

Step 2: Add Restrictions to the Profile

Now, we’ll use the ALTER LOCKDOWN PROFILE command to add our three desired restrictions to the profile .

• Restrict 1: Prevent changes to SGA_TARGET. This rule disables the ALTER SYSTEM SET command specifically for the SGA_TARGET parameter. All other ALTER SYSTEM SET operations remain allowed .

  ALTER LOCKDOWN PROFILE secure_pdb_profile DISABLE STATEMENT = ('ALTER SYSTEM') CLAUSE = ('SET') OPTION = ('SGA_TARGET');

• Restrict 2: Disable the Partitioning option. This rule makes the Partitioning feature unavailable in any PDB using this profile .

  ALTER LOCKDOWN PROFILE secure_pdb_profile DISABLE OPTION = ('PARTITIONING');

• Restrict 3: Disable network access packages. This rule restricts the use of PL/SQL packages that enable network communication, such as UTL_HTTP and UTL_SMTP .

  ALTER LOCKDOWN PROFILE secure_pdb_profile DISABLE FEATURE = ('NETWORK_ACCESS');

Step 3: Assign the Profile to a PDB

The final step is to apply the profile to a specific PDB (we’ll use pdb1 in this example). You do this by setting the PDB_LOCKDOWN initialization parameter while connected to the target PDB .

-- Connect to the target 
PDB ALTER SESSION SET CONTAINER = pdb1; 

-- Assign the lockdown profile 
ALTER SYSTEM SET PDB_LOCKDOWN = secure_pdb_profile; 

-- Verify the profile is set 
SHOW PARAMETER PDB_LOCKDOWN

The SHOW PARAMETER command should output the name of your profile, confirming it’s active .

Testing Your Lockdown Profile

Now, let’s connect to pdb1 (even as a powerful user like SYS) and try to perform the restricted operations to see the profile in action.

• Test 1: Alter SGA_TARGET

  -- Attempt to change the SGA_TARGET 
  ALTER SYSTEM SET sga_target = 800M;

  You should see an error: ORA-01031: insufficient privileges .

• Test 2: Create a Partitioned Table

  -- Attempt to create a table that uses partitioning 
  CREATE TABLE part_table (id NUMBER) PARTITION BY HASH (id) PARTITIONS 4;

  You should see an error: ORA-00439: feature not enabled: Partitioning .

• Test 3: Execute a Network-Accessing Procedure
  First, create a simple procedure that tries to use a network package:

  CREATE OR REPLACE PROCEDURE test_http AS 
  BEGIN 
  utl_http.request('http://example.com'); 
  END; 
  /

  Then, execute it:

  EXEC test_http;

  You should see an error, such as ORA-01031: insufficient privileges, indicating that the network access feature is disabled .

How to View Existing Profiles

You can query the DBA_LOCKDOWN_PROFILES view to see all profiles and their specific rules .

-- Connect to the CDB root or the PDB 
SELECT profile_name, rule_type, rule, status, clause, clause_option FROM dba_lockdown_profiles ORDER BY profile_name, rule_type;