Oracle Database 12.2 New Feature – PDB Lockdown Profiles

In an earlier post I had mentioned one of the new features in Oracle Database 12.2 was the ability to set SGA and PGA memory related parameters even at the individual PDB level. So it enables us to further limit or define the resources which a particular PDB can use and enable a more efficient management of resources in a multitenant environment.

We can further in Oracle 12c Release 2 now even limit the operations which can be performed within a particular PDB as well as restrict features which can be used or enabled – all at the individual PDB level. We can also limit network connectivity a PDB can have by enabling or disabling the use of network related packages like UTL_SMTP,UTL_HTTP, UTL_TCP at the PDB level.

This is done via the new 12.2 feature called Lockdown Profiles.

We create lockdown profiles via the CREATE LOCKDOWN PROFILE statement while connected to the root CDB and after the lockdown profile has been created, we add the required restrictions or limits which we would like to enforce via the ALTER LOCKDOWN PROFILE statement.

To assign the lockdown profile to a particular PDB, we use the PDB_LOCKDOWN initialization parameter which will contain the name of the lockdown profile we have earlier created.

If we set the PDB_LOCKDOWN parameter at the CDB level, it will apply to all the PDB’s in the CDB. We can also set the PDB_LOCKDOWN parameter at the PDB level and we can maybe have different PDB_LOCKDOWN values for different PDB’s as we will see in the example below.

Let us have a look at an example of PDB Lockdown Profiles at work.

In our CDB, we have two pluggable databases PDB1 and PDB2. We want to limit some kind of operations depending on the PDB involved.

Our requirements are the following:

• We want to ensure that in PDB1 the value for SGA_TARGET cannot be altered – so even a privileged user cannot allocate additional memory to the PDB. However if memory is available, then PGA allocation can be altered.
• To shutdown PDB1, it can only be done if connected to the root container and not from within the Pluggable Database itself
• The Partitioning feature is not available in PDB2

Create the Lockdown Profiles

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> create lockdown profile pdb1_profile;

Lockdown Profile created.

SQL> create lockdown profile pdb2_profile;

Lockdown Profile created.

Alter Lockdown Profile pdb1_profile

SQL> alter lockdown profile pdb1_profile
     disable statement =('ALTER SYSTEM') 
     clause=('SET')
     OPTION = ('SGA_TARGET');

Lockdown Profile altered.



SQL> alter lockdown profile pdb1_profile 
     disable statement =('ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE');

Lockdown Profile altered.

Alter Lockdown Profile pdb2_profile

SQL> alter lockdown profile pdb2_profile 
     DISABLE OPTION = ('PARTITIONING');

Lockdown Profile altered.

Enable the Lockdown Profiles for both PDB1 and PDB2 pluggable databases

SQL> conn / as sysdba
Connected.

SQL> alter session set container=PDB1;

Session altered.

SQL> alter system set PDB_LOCKDOWN='PDB1_PROFILE';

System altered.

SQL> alter session set container=PDB2;

Session altered.

SQL> alter system set PDB_LOCKDOWN='PDB2_PROFILE';

System altered.

Connect to PDB1 and try and increase the value of the parameter SGA_TARGET and PGA_AGGREGATE_TARGET

Note that we cannot alter SGA_TARGET because it is prevented by the lockdown profile in place, but we can alter PGA_AGGREGATE_TARGET because the lockdown profile clause only applies to the ALTER SYSTEM SET SGA_TARGET command.

SQL> alter session set container=PDB1;

Session altered.

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

SQL> alter system set pga_aggregate_target=200m;

System altered.

Connect to PDB2 and try and create a partitioned table

SQL> CREATE TABLE testme
     (id NUMBER,
      name VARCHAR2 (60))
   PARTITION BY HASH (id)
   PARTITIONS 4    ;
CREATE TABLE testme
*
ERROR at line 1:
ORA-00439: feature not enabled: Partitioning

Connect to PDB1 and try to shutdown the pluggable database

Note that while we cannot shutdown PDB1, we are able to shutdown PDB2.

SQL> alter session set container=pdb1;

Session altered.

SQL> ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> alter session set container=pdb2;

Session altered.

SQL> ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE;

Pluggable database altered.