Multitenant delivers isolation, agility and economies of scale
-
Multitenant
February 28, 2018
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:
-
We’ll first create a lockdown profile.
SQL> sho con_name CON_NAME ------------------------------ CDB$ROOT SQL> create lockdown profile sec_profile; Lockdown Profile created.
-
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.
-
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.
-
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.
-
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.