PDB Lockdown profiles allow, on a multitenant database, to limit what a user can do inside a PDB.
One use case is when we want to avoid high privileged users (like Application DBAs) to perform ALTER SYSTEM or ALTER SESSION commands.
Quickly we would think we can do a PDB Lockdown profile like:
SQL> create lockdown profile lock_test;
SQL> alter lockdown profile lock_test disable statement=('ALTER SESSION');
SQL> alter lockdown profile lock_test disable statement=('ALTER SYSTEM');
The problem of this simple profile is that we can lock ourselves, also as common user, inside the lock profile.
Image that you want to enable this profile on several PDBs:
SQL> alter session set container=pdb01;
Session altered.
SQL> alter system set pdb_lockdown=lock_test;
System altered.
SQL> alter session set container=samplepdb;
ERROR:
ORA-01031: insufficient privileges
Oups, you cannot anymore change the active container!
One has to reconnect to the CDB and then can go to the second PDB and apply the lockdown profile:
SQL> connect / as sysdba
Connected.
SQL> alter session set container=samplepdb;
Session altered.
SQL> alter system set pdb_lockdown=lock_test;
System altered.
What if now I (I’m the CDB DBA and have SYSDBA rights) want to change a parameter on one of the PDBs?
It is not possible to disable the pdb_lockdown profile:
SQL> connect / as sysdba
Connected.
SQL> alter session set container=samplepdb;
Session altered.
SQL> alter session set pdb_lockdown='';
ERROR:
ORA-01031: insufficient privileges
SQL> alter system set pdb_lockdown='';
alter system set pdb_lockdown=''
*
ERROR at line 1:
ORA-01031: insufficient privileges
Two possible options are:
- drop the PDB Lockdown on Root container
SQL> connect / as sysdba
Connected.
SQL> drop lockdown profile lock_test ;
Lockdown Profile dropped.
SQL> alter session set container=samplepdb;
Session altered.
SQL> alter system set pdb_lockdown='';
System altered.
- change the existing lockdown profile temporarily to allow operations
SQL> connect / as sysdba
Connected.
SQL> alter lockdown profile lock_test enable statement=('ALTER SYSTEM') users=common;
Lockdown Profile altered.
SQL> alter session set container=samplepdb;
Session altered.
SQL> alter system set pdb_lockdown='';
System altered.
The problem of this two solutions is that the relax of the lockdown profile will apply to all the PDBs where it is enabled.
If we want just temporarily be super users and skip the lockdown profile, the solution is to disable on the session on the cdb$root and then change to another container. During our session we will not have any lockdown profile enabled.
SQL> -- Connected to CDB$ROOT
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> -- Lockdown profiles enabled on PDBs
SQL> select b.name PDB,a.name, value$ from pdb_spfile$ a join v$pdbs b on b.con_uid=a.pdb_uid where a.name='pdb_lockdown';
PDB NAME VALUE$
---------- -------------------- ---------------
SAMPLEPDB pdb_lockdown 'LOCK_TEST'
PDB01 pdb_lockdown 'LOCK_TEST'
SQL> -- Lockdown profile disables alter system and alter session
SQL> select con_id,profile_name,rule,clause,status,users,except_users from cdb_lockdown_profiles;
CON_ID PROFILE_NA RULE CLAUS STATUS USERS EXCEPT_USE
---------- ---------- ------------------------- ----- ------- ------ ----------
1 LOCK_TEST ALTER SESSION DISABLE ALL
1 LOCK_TEST ALTER SYSTEM DISABLE ALL
SQL> -- As common user on cdb$root we can change the session pdb_lockdown profile
SQL> alter session set pdb_lockdown='';
Session altered.
SQL> -- And this will go with us to the next container
SQL> alter session set container=PDB01;
Session altered.
SQL> show parameter pdb_lockdown
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pdb_lockdown string
SQL> alter system set open_cursors=300;
System altered.
SQL> -- If we reconnect, the lockdown profile is still there
SQL> connect / as sysdba
Connected.
SQL> alter session set container=PDB01;
Session altered.
SQL> show parameter pdb_lockdown
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pdb_lockdown string LOCK_TEST
SQL> alter system set open_cursors=300;
alter system set open_cursors=300
*
ERROR at line 1:
ORA-01031: insufficient privileges
What is interesting to notice from this behaviour is:
when changing a session parameter on cdb$root, this parameter will remain active also when going to another containers within the same session.
The parameters that were not changed on session level, will get adapted when changing to another container, with the values set specifically for that container.