A Simple Guide to Lockdown Profiles

In the Multitenant architecture, economies of scale are achieved by sharing the key infrastructure and memory components. However, these are not the only resources that tenants share. Besides sharing the host environment, PDBs also share the OS, network, and common objects. Considering how certain privileges might let database users perform cross-PDB operations, there is a possibility that PDBs can be exposed to some vulnerabilities. Especially in any private or public cloud environment, tenant isolation is a key requirement for security.  Therefore, we introduced Lockdown Profiles starting with Oracle Database 12c Release 2 (12.2).

A lockdown profile is a mechanism to restrict certain operations or functionalities in a PDB. This new Multitenant feature is managed by a CDB administrator and can be used to restrict user access in a particular PDB. A lockdown profile can prevent PDB users from:

• Executing certain SQL statements, such as ALTER SYSTEM and ALTER SESSION,
• Running procedures that access the network (e.g. UTL_SMTP, UTL_HTTP),
• Accessing a common user’s objects,
• Interacting with the OS (In addition to the capabilities covered by PDB_OS_CREDENTIAL),
• Making unrestricted cross-PDB connections in a CDB,
• Taking AWR snapshots,
• Using JAVA partially or as a whole,
• Using certain database options such as Advanced Queueing and Partitioning.

In order to explore these capabilities, let’s take a look at a sample use case in which we want to enforce the following restrictions in our PDB:

• Disable ALTER SYSTEM statement altogether
• Disable Partitioning database option
• Disable network access

We can fulfill these requirements by creating a lockdown profile in our CDB Root and adding these restrictions to it. Before we move onto the “How?” part of this discussion, it’s worth mentioning a couple of important details about lockdown profiles:

• In order to be able to create a lockdown profile, you have to be a common user with CREATE LOCKDOWN PROFILE privilege and in order to enable a lockdown profile (either at the CDB or PDB level), you have to be common user with common ALTER SYSTEM or common SYSDBA privilege.
• A single lockdown profile can have several rules defined in it. In other words, you don’t have to create a lockdown profile for every restriction you want to implement.
• A PDB can have only one lockdown profile active at a time.
• The restrictions enforced by a lockdown profile are PDB-wide, they affect every single user including the SYS and SYSTEM.
• If you enable a lockdown profile in CDB Root, it affects all PDBs in the CDB. If you enable it in an Application Root (App Root), it affects all Application PDBs (App PDBs) under that App Root. If you enable it within a PDB, it only affects that PDB.

Now, let’s proceed with our example:

1. We’ll first create a lockdown profile.

SQL> sho con_name

CON_NAME

------------------------------

CDB$ROOT

SQL> create lockdown profile sec_profile;

Lockdown Profile created.

2. Now is a good time to add our three restrictions to the profile.

SQL> alter lockdown profile sec_profile disable

  2  statement=('alter system') clause=('set')

  3  option all;

Lockdown Profile altered.

SQL> alter lockdown profile sec_profile disable

  2  option=('Partitioning');

Lockdown Profile altered.

SQL> alter lockdown profile sec_profile disable

  2  feature=('NETWORK_ACCESS');

Lockdown Profile altered.

3. Let’s query DBA_LOCKDOWN_PROFILES to see the details of our lockdown profile.

SQL> select profile_name,

  2  rule_type,

  3  rule,

  4  clause,

  5  clause_option,

  6  status,

  7  users

  8  from DBA_LOCKDOWN_PROFILES;

PROFILE_NAME    RULE_TYPE     RULE                            CLAUSE     CLAUSE_OPTION   STATUS       USERS

---------------             ----------              ---------------                     ----------       ---------------               -------             ------

SEC_PROFILE      FEATURE         NETWORK_ACCESS                                                        DISABLE      ALL

SEC_PROFILE      OPTION           PARTITIONING                                                                  DISABLE      ALL

SEC_PROFILE      STATEMENT   ALTER SYSTEM                                                                DISABLE      ALL

3 rows selected.

4. As a next step, let’s create a PDB and enable our lockdown profile within the PDB.

SQL> create pluggable database PDB1

  2  admin user pdbadmin identified by oracle18;

Pluggable database created.

SQL> alter pluggable database PDB1 open;

Pluggable database altered.

SQL> sho pdbs

    CON_ID CON_NAME             OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

      2 PDB$SEED                READ ONLY  NO

      3 PDB1                    READ WRITE NO

SQL> alter session set container=PDB1;

Session altered.

SQL> alter system set pdb_lockdown=sec_profile;

System altered.

5. This is the fun part! Let’s try to perform the operations that we are not allowed to. In other words, we will try to:

• Execute an Alter System statement to change the value of a parameter,
• Create a partitioned table, and
• Execute a PL/SQL procedure that sends an email using the network.

SQL> alter session set container=PDB1;

Session altered.

SQL> sho user

USER is "SYS"

SQL> alter system set pdb_lockdown=sec_profile;

System altered.

SQL> sho parameter pdb_lockdown

NAME                     TYPE VALUE

------------------------------------ ----------- ------------------------------

pdb_lockdown                  string    SEC_PROFILE

SQL>

SQL> alter system set cursor_sharing=EXACT;

alter system set cursor_sharing=EXACT

*

ERROR at line 1:

ORA-01031: insufficient privileges

SQL> create table t1 (c1 number) partition by hash (c1);

create table t1 (c1 number) partition by hash (c1)

*

ERROR at line 1:

ORA-00439: feature not enabled: Partitioning

SQL> @sendmail.sql

Procedure created.

SQL> execute send_mail('scott.tiger@oracle.com', 'Lockdown Profiles', 'Testing network access.');

BEGIN send_mail('scott.tiger@oracle.com', 'Lockdown Profiles', 'Testing network access.'); END;

*

ERROR at line 1:

ORA-01031: insufficient privileges

ORA-06512: at "SYS.UTL_TCP", line 19

ORA-06512: at "SYS.UTL_TCP", line 295

ORA-06512: at "SYS.UTL_SMTP", line 164

ORA-06512: at "SYS.UTL_SMTP", line 201

ORA-06512: at "SYS.SEND_MAIL", line 12

ORA-06512: at line 1

As we can see, a lockdown profile is fundamentally a security mechanism to limit certain operations in a PDB. It can restrict the scope of powerful privileges such as ALTER SYSTEM. Moreover, lockdown profiles can make CDB management significantly easier by disabling access to certain resources and administrative features. These are essential aspects of tenant isolation and help us deliver a world-class database cloud architecture.

If you would like to see the content of the ‘sendmail.sql’ PL/SQL procedure and have more hands-on experience with this new cool feature, you can download our workshop materials from our OTN page. Additionally, you can also take a look at the white paper that I published last year, for more details on Lockdown Profiles.