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 statement=('alter system') clause=('set') option all;
Lockdown Profile altered.

SQL> alter lockdown profile sec_profile disable option=('Partitioning');
Lockdown Profile altered.

SQL> alter lockdown profile sec_profile disable 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, rule_type, rule, clause, clause_option, status, users 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 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> 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. There is also the set of lockdown profile examples that we provide to show three different isolation levels such as low, medium, high. As the naming suggests, 'low' corresponds to restricting only the most mission critical operations or features without taking away too much while the 'high' implementation makes sure there is very little a PDB user can do to operationally affect the PDB. 'Medium', as you may expect, can be seen as a middle ground between the 'low' and 'high'. It's important to note that these examples aim to serve as a guide and should not be considered as "gold standard" by any means.