Home » Articles » 18c » Here

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...

Back to the Top.

Created: 2019-01-17  Updated: 2019-01-17

Contact Us

Home | Articles | Scripts | Blog | Certification | Videos | Misc | About

About Tim Hall
Copyright & Disclaimer