Oracle Database 12cR2 new feature: Lockdown Profiles

    May 24, 2017 10:03:00 AM by Deiby Gomez

    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. 

    Tags: Oracle

    Deiby Gomez

    Written by Deiby Gomez

    Deiby Gómez is the youngest Oracle ACE and Oracle ACE Director in the world and the first Guatemalan with these awards. Deiby is the youngest Latin American with the highest certifications “Oracle Certified Master 11g” and “Oracle Certified Master 12c”. He received In Vegas, United States the "SELECT Journal Editor’s Choice Award 2016", he became the first Guatemalan with that award. He is a frequent speaker in several Oracle Events around the world like “Technology Network Latin American Tour 2013, 2014, 2015 and 2016 in several countries like Guatemala, Costa Rica, Nicaragua, El Salvador, Uruguay, Argentina, Mexico, Brazil, Ecuador, Colombia, Peru; Collaborate in Vegas, USA; Latin American Oracle Open World in Brazil (2015 and 2016) and Oracle Open World in San Francisco, USA (2015 and 2016). He is the first Guatemalan who was accepted by Oracle Corporation as “Beta Tester” for the version “12cR2” in 2015. He is the official Technical Reviewer of the Book “Oracle Database 12c Release 2 Multitenant (1st Edition, McGraw-Hill)" and Co-Author of the book "Oracle Database 12c Release 2 Testing Tools and Techniques for Performance and Scalability", both can be found in Amazon. He is the first Guatemala who appeared as an outstanding expert in the official magazine of Oracle Corporation called “Oracle Magazine” in the Edition November/December in 2014, this magazine is delivered around the world in several countries. He has published several articles in the Official Website of Oracle (www.oracle.com) in Portuguese, Spanish and English. Currently he is President of Guatemalan Oracle Users Group (GOUG). Director of Support Quality en Latin American Oracle Users Group Community (LAOUC) for 2016-2017, founder of a very well known group in Oracle Community called “Oraworld Team” that has members from India, Guatemala, Brazil, France and Switzerland having in total 4 Oracle ACE Directors, 3 Oracle ACE, 5 Oracle Certified Masters (OCM) and 1 PhD. Deiby was part of Oracle ACE Hackaton in Amsterdam, Netherlands in April 2016 where he was building several solutions using Oracle Cloud Products. Currently Deiby Gómez is CEO in Nuvola Consulting Group, a company that provides excellence on Support and Consulting services with Oracle Technology like Databases, Middleware, Cloud and Engineered systems. Deiby is well known in the community because of his resilience, entrepreneurship and his availability to help and share his knowledge. Deiby loves to travel, to play chess and to enjoy a good cup of coffee with friends. Oracle ACE (at the age of 23) Oracle Certified Master 11g (at the age of 24) Oracle ACE Director (at the age of 25) Oracle Certified Master 12c (at the age 26) SELECT Journal Editor’s Choice Award 2016 Speaker in several Universities, OTN Tour 2013,2014,2015. Collaborate15, LA Oracle Open World. Technical Reviewer of the Book "Oracle Database 12c Release 2 Multitenant (Oracle Press) 1st Edition" Blogger. Oracle Certifications: Oracle Linux Certified Implementation Specialist. Oracle Database 11g Administrator Certified Professional. Oracle Database 11g Administrator Certified Master (OCM 11g) Oracle Database 12c Administrator Certified Master (OCM 12c) Oracle Database 12c Maximum Availability Architecture Certified Master (MAA OCM 12c) Oracle Database 12c Maximum Availability Architecture Certified Expert (MAA OCE 12c) Oracle Database 12c Administrator Certified Professional. Oracle Service Oriented Architecture Infrastructure Implementation Certified Expert. Oracle Exadata Database Machine Administrator. Oracle RAC 11g and Grid Infraestructure Administrator Oracle RAC 12c and Grid Infraestructure Administrator Oracle Real Application Clusters 12c Certified Implementation Specialist Oracle Database 12c: Data Guard Administrator