May 24, 2017 9:03:00 AM by Deiby Gomez
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.
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
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.
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';
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.
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
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
SQL> alter system set pdb_lockdown='WANNACRY_PROFILE';
System altered.
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
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
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.
Tags: Oracle
Written by Deiby Gomez
Deiby Gómez is the first Oracle ACE Director of Guatemala. He has the highest technical certification in the world: "Oracle Certified Master 11g", "Oracle Certified Master 12c" and "Maximum Availability Architecture Oracle Certified Master 12c", he is the first person ever in Central America with all these certifications. Deiby likes to work with complex scenarios, huge and highly available critical databases where a deep knowledge of Oracle is needed. Deiby also has strong knowledge on Oracle Fusion Middleware and Oracle Cloud (PaaS & IaaS). Deiby was the winner of "IOUG SELECT Journal Editor’s Choice Award 2016" in Las Vegas, USA. He is a frequent speaker in Oracle Events around the World like OTN LAD Tour '13, '14, '15, '16, '17 (Colombia, Guatemala, El Salvador, Ecuador, Uruguay, Argentina, Brazil, Perú, Mexico, Costa Rica); Collaborate in Las Vegas, USA and Oracle Open World '15, '16, '17 (Brazil and USA). He was the first Guatemalan accepted as Beta Tester (12cR2) in San Francisco in 2015. Several articles have been published by him in English, Spanish and Portuguese in Oracle’s website, Toad World, and his own blog. Deiby appeared in the Official "Oracle Magazine" in Nov/Dec 2014 Edition as an outstanding expert. Deiby is the Technical Reviewer of the book “Oracle Database 12cR2 Multitenant - Oracle Press” and he is co-author of the book “Oracle Database 12cR2 Testing Tools and Techniques for Performance and Scalability - Oracle Press”. He loves to share his knowledge, to help people, to solve problems, to make friends and to play Chess.
Certifications:
We use cookies to improve your experience with our site. By continuing to use this site, you consent to our use of cookies. Learn more.