Oracle Database 12cR2 new feature: Lockdown Profiles

Introduction

In the past, roles, system privileges, and table privileges were used to control the functionalities allowed to database users. However, roles and privileges don’t have enough granularity to effectively restrict what work a user may do.  For example, you can grant the privilege “ALTER SYSTEM” to a user, but with that, you are allowing that user to change any database parameter. “ALTER SYSTEM” is not granular enough to enable the user to change some database parameters but not others. Even worse, there is no way to allow a user to change a specific database parameter with a range or list of values but disable another range or list of values. This functionality has been requested by DBAs for years and finally Oracle has heard us.

Oracle has introduced several new features in its newest version, 12.2.0.1. One of the most important features is “Lockdown Profiles”. Lockdown Profiles provides the granularity we were talking about. With this feature you can enable and disable database functions, features and options. It even lets you specify a range or list of values that may be used.

About Lockdown Profiles creation

Lockdown Profiles can be created only in Container Databases, and you must be connected to CDB$ROOT. If you try to create a lockdown profile in a non-container database you will receive the following error:

SQL> CREATE LOCKDOWN PROFILE WANNACRY_PROFILE;
CREATE LOCKDOWN PROFILE WANNACRY_PROFILE
 *
ERROR at line 1:
ORA-65090: operation only allowed in a container database

If you try to create a lockdown profile while connected to a PDB you will get the following error:

SQL> CREATE LOCKDOWN PROFILE WANNACRY_PROFILE;
CREATE LOCKDOWN PROFILE WANNACRY_PROFILE
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database

How to create a Lockdown Profile

Connect to CDB$ROOT:

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
 
Execute the CREATE LOCKDOWN PROFILE sentence:
SQL> CREATE LOCKDOWN PROFILE WANNACRY_PROFILE;
Lockdown Profile created.

Unfortunately, you cannot specify which functionality to enable or disable along with the CREATE LOCKDOWN PROFILE sentence. To do this, you have to use the ALTER LOCKDOWN PROFILE sentence separately.

Enabling or disabling functionalities:

There are three functionalities that you can disable:

• FEATURE: Allows you to enable or disable database features. To see the full list of features that you can indicate, check here.
• OPTION: The two options you can either enable or disable are “DATABASE QUEUING” and “PARTITIONING”.
• STATEMENT: You can either enable or disable the statements “ALTER DATABASE”, “ALTER PLUGGABLE DATABASE”, “ALTER SESSION”, and “ALTER SYSTEM”. You can specify granular options along with these statements.

In the three functionalities, you can also use clauses like ALL and EXCEPT, which allows you to include or exclude a set of features instead of specifying them one by one.

In the following example we will disable two features, one option, and one statement.

The statement that we will disable is to change the parameter “nls_date_format” in an ALTER SYSTEM statement:

SQL>  ALTER LOCKDOWN PROFILE WANNACRY_PROFILE DISABLE STATEMENT = ('ALTER SYSTEM') CLAUSE = ('SET')  OPTION= ('nls_date_format');
Lockdown Profile altered.

The next example is similar to the previous one, but here we are specifying a minimum value and a maximum value. All the values between are allowed, while all the values outside of this range are disallowed.

SQL> ALTER LOCKDOWN PROFILE WANNACRY_PROFILE DISABLE STATEMENT = ('ALTER SYSTEM') CLAUSE =
    ('SET') OPTION = ('parallel_max_servers') MINVALUE = '10' MAXVALUE = '39';

Lockdown Profile altered.

In the next example I am disabling the feature “COMMON_USER_CONNECT”. This feature disallows common users to connect to pluggable databases directly. All common users must first connect to CDB$ROOT and then jump to any Pluggable Database.

SQL> ALTER LOCKDOWN PROFILE WANNACRY_PROFILE DISABLE FEATURE = ('COMMON_USER_CONNECT'); 
Lockdown Profile altered.

The last example disables the option “PARTITIONING”, which means I cannot use any operations that relies on partitioning.

SQL> ALTER LOCKDOWN PROFILE WANNACRY_PROFILE DISABLE OPTION = ('PARTITIONING'); 
Lockdown Profile altered.

Reviewing Lockdown Profiles information:

Once the lockdown profile has been created and you have enabled or disabled the required functionalities, you can review all the information using the view DBA_LOCKDOWN_PROFILES:

SQL> select rule_type, rule, clause, clause_option, option_value , min_value, max_value, 
    status from DBA_LOCKDOWN_PROFILES where profile_name='WANNACRY_PROFILE' ;

RULE_TYPE  RULE                CLAUS    CLAUSE_OPTION        OPTION_VAL MIN MA STATUS
---------- ------------------- -------- -------------------- ---------- --- -- ----------
FEATURE    COMMON_USER_CONNECT DISABLE
OPTION     PARTITIONING        DISABLE
STATEMENT  ALTER SYSTEM        SET      NLS_DATE_FORMAT      MM-DD-YYYY         DISABLE
STATEMENT  ALTER SYSTEM        SET      PARALLEL_MAX_SERVERS            40  60  DISABLE

Enable Lockdown Profile:

As we have seen, I created the lockdown profile directly without specifying whether I want that lockdown profile in one specific PDB, or in all the PDBs, etc., I just created it. Don’t worry about it: The creation of a lockdown profile doesn’t mean it is enabled by default. Lockdown profile works like a Database Resource Manager Plan; you can create as many as you want, but only one is enabled and it must be enabled explicitly. And enabling a lockdown profile is similar to enabling a Database Resource Manager Plan; it is enabled by a database parameter.

So far we have created the lockdown profile “WANNACRY_PROFILE” and we have customized it but we haven’t enabled it yet.  You can enable a lockdown profile in one specific PDB, in a set of them or in all PDBs. If you want to enable the lockdown profile in all the PDBs you have to be connected to CDB$ROOT and set the database parameter “pdb_lockdown” to the name of your lockdown profile; in this case, “WANNACRY_PROFILE”. If you want to enable the lockdown profile in a specific PDB, first you have to connect to the specific PDB and then you have to set the database parameter “pdb_lockdown”. 

In the following example we have a CDB called “db12c” with two PDBs, one named “PDB1” and the second one named “PDB2”. We will enable the lockdown profile “WANNACRY_PROFILE” only in “PDB1”.

Checking out that the parameter is not set in any container:

SQL> select con_id, name, value from gv$system_parameter where name='pdb_lockdown';

CON_ID     NAME            VALUE
---------- --------------- ----------
 0          pdb_lockdown
 
Connecting to “PDB1”:
SQL> show con_name
CON_NAME
-----------------------------
PDB1

Set the database parameter pdb_lockdown:

SQL> alter system set pdb_lockdown='WANNACRY_PROFILE';
System altered.

Verifying that the parameter is set only in “PDB1” (CON_ID=3):

SQL> select con_id, name, value from gv$system_parameter where name='pdb_lockdown';

CON_ID     NAME VALUE
---------- -------------- ------------------------------
 0          pdb_lockdown
 3          pdb_lockdown   WANNACRY_PROFILE

Confirming whether the functionalities were successfully disabled:

Testing to change the parameter nls_date_format:

Connecting to “PDB1”:
SQL> show con_name
CON_NAME
------------------------------
PDB1

I am using a common user with “alter system” privileges:

SQL> show user
USER is "C##DGOMEZ"

As you see, even if the user has “alter system” privilege it is not allowed to change the database parameter because of the lockdown profile.

SQL> alter system set nls_date_format='mmddyyyy' scope=spfile;
alter system set nls_date_format='mm-dd-yyyy' scope=spfile
*
ERROR at line 1:
ORA-01031: insufficient privileges

Testing the feature 'COMMON_USER_CONNECT'. Without the lockdown profile, I was able to connect directly to a PDB with a common user, however now it is not allowed because of the lockdown profile:

[oracle@nuvola2 ~]$ sqlplus c##dgomez/dgomez@192.168.1.22:1521/pdb1 
ERROR:
ORA-01017: invalid username/password; logon denied

Testing the parameter parallel_max_servers. The range we specified in the lockdown profile was [10,39]. As we explained before, all the values outside of this range are disabled, while the values between these values are allowed.

SQL> alter system set parallel_max_servers=9;
alter system set parallel_max_servers=9
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> alter system set parallel_max_servers=10;
System altered.

SQL> alter system set parallel_max_servers=39;
System altered.

SQL> alter system set parallel_max_servers=40;
alter system set parallel_max_servers=40
*
ERROR at line 1:
ORA-01031: insufficient privileges

How to drop a lockdown profile:

To drop a lockdown profile is easy. You just have to execute the following sentence from CDB$ROOT. You don’t have to reset or clean the parameter pdb_lockdown in all the PDBs that are using this lockdown profile (although I strongly think it should not be this way). When you execute this sentence, all the PDBs using the lockdown profile will automatically stop using the settings provided by this lockdown profile.   

DROP LOCKDOWN PROFILE WANNACRY_PROFILE;

Conclusion

In this article, I outlined the required steps to create a new lockdown profile, I explained which kind of functionalities we can enable and disable, and I provided several examples. I provided comments to help you quickly understand how to use lockdown profiles and take advantage of them; very important in an era where the security is of utmost value and a finer granularity is needed to restrict people to only those tasks necessary for their role.