8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Multitenant : PDB Lockdown Profile Enhancements in Oracle Database 18c
Oracle database 18c extends the PDB Lockdown Profiles functionality introduced in Oracle database 12.2, making it even easier to restrict the operations and functionality available from within a PDB.
- Create PDB Lockdown Profiles in Application Root Containers
- Create Lockdown Profiles Based on Existing Profiles
- Default Lockdown Profiles
- View Enhancements
- Thoughts
Related articles.
- Multitenant : PDB Lockdown Profiles in Oracle Database 12c Release 2 (12.2)
- Multitenant : Application Containers in Oracle Database 12c Release 2 (12.2)
- Multitenant : All Articles
Create PDB Lockdown Profiles in Application Root Containers
Oracle 18c allows you to create PDB lockdown profiles in an application root container, so they can be applied to dependent PDBs. The SYS user can create PDB lockdown profiles in an application root, but it can only reference those in the root container, so you should work with an application user.
Create a lockdown profile in the root container and set it as the default lockdown profile.
CONN / AS SYSDBA CREATE LOCKDOWN PROFILE default_cdb_lockdown; ALTER LOCKDOWN PROFILE default_cdb_lockdown DISABLE FEATURE = ('NETWORK_ACCESS'); ALTER SYSTEM SET PDB_LOCKDOWN = default_cdb_lockdown;
Create an application root container.
CONN / AS SYSDBA CREATE PLUGGABLE DATABASE appcon1 AS APPLICATION CONTAINER ADMIN USER app_admin IDENTIFIED BY Password1; ALTER PLUGGABLE DATABASE appcon1 OPEN;
Switch to the application root container and check what rules are being enforced using the V$LOCKDOWN_RULES
view. The following query is available as the lockdown_rules.sql script.
CONN / AS SYSDBA ALTER SESSION SET CONTAINER = appcon1; COLUMN rule_type FORMAT A20 COLUMN rule FORMAT A20 COLUMN clause FORMAT A20 COLUMN clause_option FORMAT A20 COLUMN pdb_nameFORMAT A30 SELECT lr.rule_type, lr.rule, lr.status, lr.clause, lr.clause_option, lr.users, lr.con_id, p.pdb_name FROM v$lockdown_rules lr LEFT OUTER JOIN cdb_pdbs p ON lr.con_id = p.con_id ORDER BY 1, 2; RULE_TYPE RULE STATUS CLAUSE CLAUSE_OPTION USERS CON_ID PDB_NAME -------------------- -------------------- ------- -------------------- -------------------- ------ ---------- ------------------------------ FEATURE NETWORK_ACCESS DISABLE ALL 4 APPCON1 SQL>
Connect to the application container and create a new local user.
CONN / AS SYSDBA ALTER SESSION SET CONTAINER = appcon1; CREATE USER lockdown_admin IDENTIFIED BY lockdown_admin; GRANT DBA TO lockdown_admin;
Connect as the local user in the application container and create a PDB lockdown profile.
CONN lockdown_admin/lockdown_admin@//localhost:1521/appcon1 CREATE LOCKDOWN PROFILE default_approot_lockdown; ALTER LOCKDOWN PROFILE default_approot_lockdown DISABLE FEATURE = ('NETWORK_ACCESS', 'OS_ACCESS'); ALTER SYSTEM SET PDB_LOCKDOWN = default_approot_lockdown;
We can display information about the lockdown profiles using the CDB_LOCKDOWN_PROFILES
view. We need to connect to the root container if we want to see all the lockdown profiles. The following query is available as the lockdown_profiles.sql script.
CONN / AS SYSDBA SET LINESIZE 250 COLUMN pdb_name FORMAT A30 COLUMN profile_name FORMAT A30 COLUMN rule_type FORMAT A20 COLUMN rule FORMAT A20 COLUMN clause FORMAT A20 COLUMN clause_option FORMAT A20 COLUMN option_value FORMAT A20 COLUMN min_value FORMAT A20 COLUMN max_value FORMAT A20 COLUMN list FORMAT A20 SELECT lp.con_id, p.pdb_name, lp.profile_name, lp.rule_type, lp.status, lp.rule, lp.clause, lp.clause_option, lp.option_value, lp.min_value, lp.max_value, lp.list FROM cdb_lockdown_profiles lp LEFT OUTER JOIN cdb_pdbs p ON lp.con_id = p.con_id ORDER BY 1, 3; CON_ID PDB_NAME PROFILE_NAME RULE_TYPE STATUS RULE CLAUSE CLAUSE_OPTION OPTION_VALUE MIN_VALUE MAX_VALUE LIST ---------- ------------------------------ ------------------------------ -------------------- ------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- 1 DEFAULT_CDB_LOCKDOWN FEATURE DISABLE NETWORK_ACCESS 1 PRIVATE_DBAAS EMPTY 1 PUBLIC_DBAAS EMPTY 1 SAAS EMPTY 4 APPCON1 DEFAULT_APPROOT_LOCKDOWN FEATURE DISABLE OS_ACCESS 4 APPCON1 DEFAULT_APPROOT_LOCKDOWN FEATURE DISABLE NETWORK_ACCESS SQL>
We can now create an application PDB under the application container, switch to it and check what rules are being applied.
CONN / AS SYSDBA ALTER SESSION SET container = appcon1; CREATE PLUGGABLE DATABASE apppdb1 ADMIN USER pdb_admin IDENTIFIED BY Password1; ALTER PLUGGABLE DATABASE apppdb1 OPEN; ALTER SESSION SET container = apppdb1; @lockdown_rules RULE_TYPE RULE STATUS CLAUSE CLAUSE_OPTION USERS CON_ID PDB_NAME -------------------- -------------------- ------- -------------------- -------------------- ------ ---------- ------------------------------ FEATURE OS_ACCESS DISABLE ALL 5 APPPDB1 FEATURE NETWORK_ACCESS DISABLE ALL 5 APPPDB1 SQL>
We can see the application root lockdown profile is being used, because we have the additional restriction.
Personally, I think not being able to perform all these actions from "/ AS SYSDBA" is an ugly solution. I would rather keep all the lockdown profiles in the root container and just apply them to the application root or individual PDBs as required.
CONN / AS SYSDBA -- Create new lockdown profile for the application root container. CREATE LOCKDOWN PROFILE default_appcon1_lockdown; ALTER LOCKDOWN PROFILE default_appcon1_lockdown DISABLE FEATURE = ('NETWORK_ACCESS', 'OS_ACCESS'); --Switch to the application root container. ALTER SESSION SET CONTAINER = appcon1; -- Get rid of the local lockdown profile. DROP LOCKDOWN PROFILE default_approot_lockdown; -- Use the new root lockdown profile in the application root. ALTER SYSTEM SET PDB_LOCKDOWN = default_appcon1_lockdown; @lockdown_rules RULE_TYPE RULE STATUS CLAUSE CLAUSE_OPTION USERS CON_ID PDB_NAME -------------------- -------------------- ------- -------------------- -------------------- ------ ---------- ------------------------------ FEATURE OS_ACCESS DISABLE ALL 4 APPCON1 FEATURE NETWORK_ACCESS DISABLE ALL 4 APPCON1 SQL>
It gives a similar result, but we don't have to worry about which users can/can't perform specific operations.
Let's clean up the containers and lockdown profiles used in this section.
CONN / AS SYSDBA ALTER SESSION SET container = appcon1; ALTER PLUGGABLE DATABASE apppdb1 CLOSE; DROP PLUGGABLE DATABASE apppdb1 INCLUDING DATAFILES; CONN / AS SYSDBA ALTER PLUGGABLE DATABASE appcon1 CLOSE; DROP PLUGGABLE DATABASE appcon1 INCLUDING DATAFILES; DROP LOCKDOWN PROFILE default_cdb_lockdown; DROP LOCKDOWN PROFILE default_appcon1_lockdown;
Create Lockdown Profiles Based on Existing Profiles
Oracle 18c allows us to create new PDB lockdown profiles based on existing profiles. We have two options, depending on if we want a static copy of a profile, or a dynamic link to another profile. In the case of the dynamic link, changes in the base profile will be reflected in rule set applied.
-- Static CREATE LOCKDOWN PROFILE {new-profile-name} FROM {base-profile-name}; -- Dynamic CREATE LOCKDOWN PROFILE {new-profile-name} USING {base-profile-name};
To test these, create a new base profile, and two new profiles based off it. In both cases adding an extra restriction.
CONN / AS SYSDBA CREATE LOCKDOWN PROFILE base_lockdown; ALTER LOCKDOWN PROFILE base_lockdown DISABLE FEATURE = ('OS_ACCESS'); CREATE LOCKDOWN PROFILE static_lockdown FROM base_lockdown; ALTER LOCKDOWN PROFILE static_lockdown DISABLE FEATURE = ('NETWORK_ACCESS'); CREATE LOCKDOWN PROFILE dynamic_lockdown INCLUDING base_lockdown; ALTER LOCKDOWN PROFILE dynamic_lockdown DISABLE FEATURE = ('NETWORK_ACCESS'); @lockdown_profiles CON_ID PDB_NAME PROFILE_NAME RULE_TYPE STATUS RULE CLAUSE CLAUSE_OPTION OPTION_VALUE MIN_VALUE MAX_VALUE LIST ---------- ------------------------------ ------------------------------ -------------------- ------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- 1 BASE_LOCKDOWN FEATURE DISABLE OS_ACCESS 1 DYNAMIC_LOCKDOWN FEATURE DISABLE NETWORK_ACCESS 1 PRIVATE_DBAAS EMPTY 1 PUBLIC_DBAAS EMPTY 1 SAAS EMPTY 1 STATIC_LOCKDOWN FEATURE DISABLE OS_ACCESS 1 STATIC_LOCKDOWN FEATURE DISABLE NETWORK_ACCESS SQL>
This looks promising. The STATIC_LOCKDOWN
profile has two rules, one from the copy during creation, and one that was added subsequently. The DYNAMIC_LOCKDOWN
profile has one rule, which was added after creation, but supposedly is still linked to the BASE_LOCKDOWN
profile, so includes that rule too. Let's test this by setting each profile in a PDB and checking the effective rules.
-- Test STATIC_LOCKDOWN Profile CONN / AS SYSDBA ALTER SESSION SET CONTAINER = pdb1; ALTER SYSTEM SET PDB_LOCKDOWN = static_lockdown; @lockdown_rules; RULE_TYPE RULE STATUS CLAUSE CLAUSE_OPTION USERS CON_ID PDB_NAME -------------------- -------------------- ------- -------------------- -------------------- ------ ---------- ------------------------------ FEATURE OS_ACCESS DISABLE ALL 3 PDB1 FEATURE NETWORK_ACCESS DISABLE ALL 3 PDB1 SQL> -- Test DYNAMIC_LOCKDOWN Profile CONN / AS SYSDBA ALTER SESSION SET CONTAINER = pdb1; ALTER SYSTEM SET PDB_LOCKDOWN = dynamic_lockdown; @lockdown_rules; RULE_TYPE RULE STATUS CLAUSE CLAUSE_OPTION USERS CON_ID PDB_NAME -------------------- -------------------- ------- -------------------- -------------------- ------ ---------- ------------------------------ FEATURE OS_ACCESS DISABLE ALL 3 PDB1 SQL>
It appears the STATIC_LOCKDOWN
profile works as expected, but the DYNAMIC_LOCKDOWN
profile is not using the linked rules from the BASE_LOCKDOWN
as expected.
Let's add another restriction to the BASE_LOCKDOWN
profile.
CONN / AS SYSDBA ALTER LOCKDOWN PROFILE base_lockdown DISABLE FEATURE = ('XDB_PROTOCOLS'); @lockdown_profiles CON_ID PDB_NAME PROFILE_NAME RULE_TYPE STATUS RULE CLAUSE CLAUSE_OPTION OPTION_VALUE MIN_VALUE MAX_VALUE LIST ---------- ------------------------------ ------------------------------ -------------------- ------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- 1 BASE_LOCKDOWN FEATURE DISABLE OS_ACCESS 1 BASE_LOCKDOWN FEATURE DISABLE XDB_PROTOCOLS 1 DYNAMIC_LOCKDOWN FEATURE DISABLE NETWORK_ACCESS 1 PRIVATE_DBAAS EMPTY 1 PUBLIC_DBAAS EMPTY 1 SAAS EMPTY 1 STATIC_LOCKDOWN FEATURE DISABLE NETWORK_ACCESS 1 STATIC_LOCKDOWN FEATURE DISABLE OS_ACCESS SQL>
We now run the same test as before.
-- Test STATIC_LOCKDOWN Profile CONN / AS SYSDBA ALTER SESSION SET CONTAINER = pdb1; ALTER SYSTEM SET PDB_LOCKDOWN = static_lockdown; @lockdown_rules; RULE_TYPE RULE STATUS CLAUSE CLAUSE_OPTION USERS CON_ID PDB_NAME -------------------- -------------------- ------- -------------------- -------------------- ------ ---------- ------------------------------ FEATURE OS_ACCESS DISABLE ALL 3 PDB1 FEATURE NETWORK_ACCESS DISABLE ALL 3 PDB1 SQL> -- Test DYNAMIC_LOCKDOWN Profile CONN / AS SYSDBA ALTER SESSION SET CONTAINER = pdb1; ALTER SYSTEM SET PDB_LOCKDOWN = dynamic_lockdown; @lockdown_rules; RULE_TYPE RULE STATUS CLAUSE CLAUSE_OPTION USERS CON_ID PDB_NAME -------------------- -------------------- ------- -------------------- -------------------- ------ ---------- ------------------------------ FEATURE NETWORK_ACCESS DISABLE ALL 3 PDB1 SQL>
Once again the STATIC_LOCKDOWN
profile works as expected, but the DYNAMIC_LOCKDOWN
profile is still not using the linked rules from the BASE_LOCKDOWN
as expected.
Maybe the restriction is in place, but the V$LOCKDOWN_RULES
view is not displaying it properly. Let's try an OS access to see if the BASE_LOCKDOWN
profile restriction is being enforced within the PDB.
-- Test file access with no lockdown profile. CONN / AS SYSDBA ALTER SESSION SET CONTAINER = pdb1; ALTER SYSTEM RESET PDB_LOCKDOWN; SHUTDOWN; STARTUP; SHOW PARAMETER PDB_LOCKDOWN; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ pdb_lockdown string SQL> CREATE OR REPLACE DIRECTORY tmp_dir AS '/tmp/'; GRANT READ, WRITE ON DIRECTORY tmp_dir TO test; DECLARE l_file UTL_FILE.file_type; BEGIN l_file := UTL_FILE.fopen('TMP_DIR', 'test.txt', 'a', 32767); UTL_FILE.put_line(l_file, 'Hello test 1'); UTL_FILE.fclose(l_file); END; / HOST cat /tmp/test.txt Hello test 1 SQL> -- Test file access with the static lockdown profile. ALTER SYSTEM SET PDB_LOCKDOWN = static_lockdown; SHOW PARAMETER PDB_LOCKDOWN; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ pdb_lockdown string STATIC_LOCKDOWN SQL> DECLARE l_file UTL_FILE.file_type; BEGIN l_file := UTL_FILE.fopen('TMP_DIR', 'test.txt', 'a', 32767); UTL_FILE.put_line(l_file, 'Hello test 2'); UTL_FILE.fclose(l_file); END; / DECLARE * ERROR at line 1: ORA-29280: invalid directory path ORA-06512: at "SYS.UTL_FILE", line 41 ORA-06512: at "SYS.UTL_FILE", line 478 ORA-06512: at line 4 SQL> -- Test file access with the dynamic lockdown profile. ALTER SYSTEM SET PDB_LOCKDOWN = dynamic_lockdown; SHOW PARAMETER PDB_LOCKDOWN; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ pdb_lockdown string DYNAMIC_LOCKDOWN SQL> DECLARE l_file UTL_FILE.file_type; BEGIN l_file := UTL_FILE.fopen('TMP_DIR', 'test.txt', 'a', 32767); UTL_FILE.put_line(l_file, 'Hello test 3'); UTL_FILE.fclose(l_file); END; / DECLARE * ERROR at line 1: ORA-29280: invalid directory path ORA-06512: at "SYS.UTL_FILE", line 41 ORA-06512: at "SYS.UTL_FILE", line 478 ORA-06512: at line 4 SQL>
It does appear the BASE_LOCKDOWN
profile restriction is working correctly when using the DYNAMIC_LOCKDOWN
profile.
Let's enable OS access on the BASE_LOCKDOWN
profile and check the effects.
CONN / AS SYSDBA ALTER LOCKDOWN PROFILE base_lockdown ENABLE FEATURE = ('OS_ACCESS'); ALTER SESSION SET CONTAINER = pdb1; -- Test file access with the static lockdown profile. ALTER SYSTEM SET PDB_LOCKDOWN = static_lockdown; SHOW PARAMETER PDB_LOCKDOWN; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ pdb_lockdown string STATIC_LOCKDOWN SQL> DECLARE l_file UTL_FILE.file_type; BEGIN l_file := UTL_FILE.fopen('TMP_DIR', 'test.txt', 'a', 32767); UTL_FILE.put_line(l_file, 'Hello test 4'); UTL_FILE.fclose(l_file); END; / DECLARE * ERROR at line 1: ORA-29280: invalid directory path ORA-06512: at "SYS.UTL_FILE", line 41 ORA-06512: at "SYS.UTL_FILE", line 478 ORA-06512: at line 4 SQL> -- Test file access with the dynamic lockdown profile. ALTER SYSTEM SET PDB_LOCKDOWN = dynamic_lockdown; SHOW PARAMETER PDB_LOCKDOWN; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ pdb_lockdown string DYNAMIC_LOCKDOWN SQL> DECLARE l_file UTL_FILE.file_type; BEGIN l_file := UTL_FILE.fopen('TMP_DIR', 'test.txt', 'a', 32767); UTL_FILE.put_line(l_file, 'Hello test 5'); UTL_FILE.fclose(l_file); END; / HOST cat /tmp/test.txt Hello test 1 Hello test 5 SQL>
So the DYNAMIC_LOCKDOWN
profile is working as expected, combining its rules with those of the BASE_LOCKDOWN
profile. It seems it's the V$LOCKDOWN_RULES
view that is misreporting what rules are in place within the PDB.
Let's clean up the lockdown profiles we created for these tests.
CONN / AS SYSDBA ALTER SESSION SET CONTAINER = pdb1; ALTER SYSTEM RESET PDB_LOCKDOWN; SHUTDOWN; STARTUP; CONN / AS SYSDBA DROP LOCKDOWN PROFILE static_lockdown; DROP LOCKDOWN PROFILE dynamic_lockdown; DROP LOCKDOWN PROFILE base_lockdown;
Default Lockdown Profiles
The database comes with three default PDB lockdown profiles called PRIVATE_DBAAS, PUBLIC_DBAAS and SAAS. These are empty profiles, containing no restrictions, which you can tailor to suit your own needs if you so wish. There is a description of how these should be used here.
These are listed as a new feature in 18c, but they were already present in the same form in Oracle 12.2.
View Enhancements
The V$LOCKDOWN_RULES
view has been added to display the PDB lockdown profile rules that are relevant to the current container. An example of the usage can be seen above. It does seem there is a problem with what is displays when using dynamic profiles.
The CDB_LOCKDOWN_PROFILES
view is unchanged. This was present in Oracle 12.2 and included the CON_ID
column, even though lockdown profiles could only be created in the root container at the time. Now the CON_ID
has more value.
Thoughts
The 18c additions to this functionality feel like a collection of half-finished features.
- In MOS Doc ID 2454387.1 it explains that "/ AS SYSDBA" not being able to set the
PDB_LOCKDOWN
parameter in an application root container is not a bug, but a feature. I disagree. I think the current way this functionality works is messy and confusing, when it should super simple. It needs to be fixed. - Creating lockdown profiles based on other profiles works fine, but because of the misreporting of the
V$LOCKDOWN_RULES
view when using dynamic lockdown profiles, it's quite difficult to see what is going on. - The interaction of the lockdown profiles at different levels, labelled as PDB Lockdown Profile inheritance is unclear. The documentation seems a little contradictory, and with the
V$LOCKDOWN_RULES
view not working properly, it's hard to confirm what his happening without performing lots of different test. - The
V$LOCKDOWN_RULES
view is a great addition, but it needs to work properly with dynamic lockdown profiles, and it would be good if it told you which profile was being used to provide each a specific rule, so you could see what, if anything was happening in the way of inheritance. For example, does this rule come from the PDB, application root or CDB root lockdown profile?
For more information see:
- Multitenant : PDB Lockdown Profiles in Oracle Database 12c Release 2 (12.2)
- Multitenant : Application Containers in Oracle Database 12c Release 2 (12.2)
- Multitenant : All Articles
Hope this helps. Regards Tim...