Oracle 19c Manage PDB Lockdown Profiles

In Oracle 19c, managing Pluggable Database (PDB) lockdown profiles is essential for ensuring database security in a multitenant environment. PDB lockdown profiles allow database administrators to restrict user operations within PDBs, thereby enhancing the security and isolation of each PDB. This blog will explore the techniques for effective Pluggable Database lockdown management, securing Pluggable Database, and the benefits of using lockdown profiles.

Creating and Altering PDB Lockdown Profiles

Creating a Lockdown Profile

To create a Pluggable Database lockdown profile, use the CREATE LOCKDOWN PROFILE statement. This profile restricts user operations associated with specific database features, options, and SQL statements.

CREATE LOCKDOWN PROFILE my_profile;

Once created, restrictions can be added using the ALTER LOCKDOWN PROFILE statement.

Altering a Lockdown Profile

After creating a lockdown profile, all user operations are enabled by default. The ALTER LOCKDOWN PROFILE statement allows you to disable certain user operations.

ALTER LOCKDOWN PROFILE my_profile DISABLE FEATURE = ('NETWORK_ACCESS');
ALTER LOCKDOWN PROFILE my_profile DISABLE OPTION = ('DATABASE QUEUING');
ALTER LOCKDOWN PROFILE my_profile DISABLE STATEMENT = ('ALTER DATABASE');

These commands restrict the specified operations within the PDBs to which the profile applies.

Assigning Lockdown Profiles

Assign lockdown profiles to individual Pluggable Databases, all Pluggable Databases in a Container Database (CDB), or all PDBs in an application container. Use the PDB_LOCKDOWN initialization parameter to assign a profile.

Assigning to All PDBs in a CDB

To apply a lockdown profile to all Pluggable Database in a CDB, set the PDB_LOCKDOWN parameter while connected to the CDB root.

ALTER SYSTEM SET PDB_LOCKDOWN = my_profile;

Assigning to a Specific PDB

To apply a lockdown profile to a specific PDB, set the PDB_LOCKDOWN parameter while connected to that PDB.

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

📢 You might also like: Oracle 19c Audit Users in CDBs and PDBs (Category: Oracle Database Admin)

Managing Lockdown Profile Rules

Lockdown profiles support various types of rules to restrict features, options, and statements.

Restricting Features

Features can be disabled or enabled using the FEATURE clause.

ALTER LOCKDOWN PROFILE my_profile DISABLE FEATURE = ('NETWORK_ACCESS', 'OS_ACCESS');
ALTER LOCKDOWN PROFILE my_profile ENABLE FEATURE = ('UTL_HTTP');

Restricting Options

Database options can be restricted using the OPTION clause.

ALTER LOCKDOWN PROFILE my_profile DISABLE OPTION = ('DATABASE QUEUING');
ALTER LOCKDOWN PROFILE my_profile ENABLE OPTION = ('PARTITIONING');

Restricting SQL Statements

Specific SQL statements can be restricted using the STATEMENT clause.

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

PDB lockdown – Setting Up and Managing Lockdown Profiles

Here is an example of creating and managing lockdown profiles.

Create Lockdown Profiles

CREATE LOCKDOWN PROFILE default_pdb_lockdown;
CREATE LOCKDOWN PROFILE pdb1_specific_lockdown;

Add Restrictions

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

Assign Profiles

ALTER SYSTEM SET PDB_LOCKDOWN = default_pdb_lockdown;
ALTER SESSION SET CONTAINER = pdb1;
ALTER SYSTEM SET PDB_LOCKDOWN = pdb1_specific_lockdown;

Reset and Drop Profiles

ALTER SESSION SET CONTAINER = pdb1;
ALTER SYSTEM RESET PDB_LOCKDOWN;

CONN / AS SYSDBA
ALTER SYSTEM RESET PDB_LOCKDOWN;

DROP LOCKDOWN PROFILE default_pdb_lockdown;
DROP LOCKDOWN PROFILE pdb1_specific_lockdown;

Conclusion

Managing PDB lockdown profiles in Oracle 19c is crucial for maintaining a secure multitenant environment. By creating, altering, and assigning lockdown profiles, database administrators can effectively restrict user operations and enhance the security of individual PDBs. Utilizing lockdown profiles ensures a greater degree of separation between Pluggable Database, allowing different management teams to control their respective databases without compromising overall security.

See more on Oracle’s website!

Be Oracle Database Certified Professional, this world is full of opportunities for qualified DBAs!