By Deiby Gómez
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, 184.108.40.206. 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
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:
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';
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');
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');
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 DISABLEOPTION PARTITIONING DISABLESTATEMENT ALTER SYSTEM SET NLS_DATE_FORMAT MM-DD-YYYY DISABLESTATEMENT 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”:
Set the database parameter pdb_lockdown:
SQL> alter system set pdb_lockdown='WANNACRY_PROFILE';
Verifying that the parameter is set only in “PDB1” (CON_ID=3):
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:
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 email@example.com:1521/pdb1
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;
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.
About the Author: Deiby Gómez is an Oracle ACE Director from Guatemala, he has the certifications Oracle Certified Master 11g and Oracle Certified Master 12c. Deiby Gómez currently works for Nuvola Consulting Group, a Guatemalan company that provides Consulting Services for Oracle Products. He is the winner of “SELECT Journal Editor's Choice Award 2016”. Deiby has been Speaker in Collaborate in Las Vega USA, Oracle Open World in San Francisco USA and in Sao Paolo Brazil. Twitter | LinkedIn