Blog

A PDB lockdown profile is a named set of features that control operations available to users connected to a PDB. For example, a PDB lockdown profile can disable privileges that come with the ALTER SYSTEM statement.

Lockdown profiles are created via the CREATE LOCKDOWN PROFILE statement while connected to the root CDB. Once lockdown profile has been created, required restrictions or limits are added which we would like to enforce via the ALTER LOCKDOWN PROFILE statement.

PDB_LOCKDOWN initialization parameter is used to assign lockdown profile to a pluggable database.

Setting PDB_LOCKDOWN parameter at the CDB level, will apply it to all PDBs in that CDB. However, if we want to apply it to a single PDB, then we must specify the PDB name as shown in the example below:

SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT

SQL> create lockdown profile pdbx_profile;
Lockdown Profile created.

SQL> create lockdown profile pdby_profile;
Lockdown Profile created.

SQL> alter lockdown profile pdbx_profile disable statement = 
('ALTER SYSTEM') clause = ('SET') OPTION = ('SGA_TARGET');
Lockdown Profile altered.

SQL> alter lockdown profile pdbx_profile disable statement = 
('ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE');
Lockdown Profile altered.

SQL> alter lockdown profile pdby_profile disable option = 
('PARTITIONING');
Lockdown Profile altered.

SQL> alter session set container = pdbx;
Session altered.

SQL> show parameter pdb_lockdown;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------
pdb_lockdown                         string

SQL> alter system set pdb_lockdown=pdbx_profile;
System altered.

SQL> show parameter pdb_lockdown;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------
pdb_lockdown                         string      PDBX_PROFILE

SQL> alter session set container = pdby;
Session altered.

SQL> show parameter pdb_lockdown;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------
pdb_lockdown                         string

SQL> alter system set pdb_lockdown=pdby_profile;
System altered.

SQL>
SQL> show parameter pdb_lockdown;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------
pdb_lockdown                         string      PDBY_PROFILE

SQL> alter session set container = pdbx;
Session altered.

SQL> alter system set sga_target = 200M;
alter system set sga_target = 200M
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> alter session set container = pdby;
Session altered.
SQL> CREATE TABLE alchemist
        (id NUMBER,
        name VARCHAR2 (60))
        PARTITION BY HASH (id)
        PARTITIONS 4;
CREATE TABLE alchemist
*
ERROR at line 1:
ORA-00439: feature not enabled: Partitioning

SQL> alter session set container = pdbx;
Session altered.
SQL> CREATE TABLE alchemist
        (id NUMBER,
        name VARCHAR2 (60))
        PARTITION BY HASH (id)
        PARTITIONS 4;
Table created.

SQL> alter session set container = pdbx;
Session altered.
SQL> ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL>

SQL> alter session set container = pdby;
Session altered.
SQL> ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE;
Pluggable database altered.

Dictionary Views to Check Lockdown Profiles

set lines 150
col profile_name for a15
col rule_type for a10
col rule for a45
col clause for a7
col clause_option for a13
col option_value for a12
col min_value for a10
col max_value for a10
SELECT profile_name, rule_type, rule, clause, clause_option, option_value, min_value, max_value, list, status
FROM dba_lockdown_profiles
ORDER BY 1;

Happy Learning !!!

DB Alchemist Academy