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!
