Lockdown Profiles
A potential for elevation of privileges exists where identity is shared between PDBs, when it needs to be compartmentalized because the individuals using those identities are different and may have different aims or allegiances. Identity can be shared
- at the OS level when the database interacts with OS resources likes files or processes,
- at a network level when the database communicates with other systems and network identity is important, or
- inside the database, as PDBs access or create common objects or communicate across container boundaries using features like Database Links.
A lockdown profile is a declarative way to prevent a PDB to access to the network, administrative features, or common users and objects.
Limiting the access to certain administrative features within a PDB might be desirable for some customers. In this lab, we will examine the effect of a lockdown profile that is intended to prevent any attempt to change the value of the 'cursor_sharing' parameter.
In this section we have 3 labs. In the first one we will disable a statement, followed by disabling the partitioning option in the PDB. Finally we will disable the network access in the PDB.
Lab 1: Lockdown Profiles - Disable Statement 'Alter System'
Lab Details: This lab will help you to disable 'Alter System' statement for 'cursor_sharing' parameter via a lockdown profile.
Database targets to be used: cdb1
Please go through the following steps:
- $ cd /u01/HOL/lockdown_profiles
- $ source cdb1.env
- $ sqlplus /nolog
- SQL> @lab1.sql
In the following table we have listed the various commands executed by the script.
Step
|
Action
|
Comments
|
1
|
$ cd /u01/HOL/lockdown_profiles
$ source cdb1.env |
It will setup the Oracle environment so that cdb1 will be ready to run our lab script.
|
2
|
$ sqlplus / as sysdba
|
You will login to SQLPlus and go through the following steps to complete our first lockdown profiles lab.
|
3
|
SQL> set lines 200 pages 1000
SQL> column PROFILE_NAME FORMAT A15 SQL> column RULE_TYPE FORMAT A10 SQL> column RULE FORMAT A20 SQL> column CLAUSE FORMAT A10 SQL> column OPTION$ FORMAT A20 SQL> column VALUE$ FORMAT A5 SQL> column STATUS FORMAT A8 |
It will format the columns of DBA_LOCKDOWN_PROFILES table so that the available lockdown profiles can be displayed smoothly in the next steps.
|
4
|
SQL> create pluggable database SALES admin user admin identified by oracle12;
|
This will create a PDB named 'SALES' in our CDB.
|
5
|
SQL> create lockdown profile p1;
SQL> alter lockdown profile p1 disable statement=('ALTER SYSTEM') clause=('SET') option=('cursor_sharing'); |
A lockdown profile named 'p1' will be created to disable 'alter system' statement and 'set' clause for the option 'cursor_sharing'.
|
6
|
SQL> select * from DBA_LOCKDOWN_PROFILES;
|
The current list of lockdown profiles including 'p1' will be displayed.
|
7
|
SQL> alter session set container = SALES;
SQL> show parameter cursor_sharing |
You will be connected to the PDB that we created earlier and shown the current value of the parameter 'cursor_sharing'.
|
8
|
SQL> alter system set cursor_sharing = FORCE;
|
This will change the value of 'cursor_sharing' to 'FORCE'. However, please remember that the lockdown profile 'p1' is not in use yet.
|
9
|
SQL> alter system set pdb_lockdown = p1;
SQL> alter pluggable database close; SQL> alter pluggable database open; |
It will set the current lockdown profile to ‘p1’ and bounce the database in order for the changes to take effect.
|
10
|
SQL> alter system set cursor_sharing = EXACT;
|
This time, you will run into the error “ORA-01031: insufficient privileges” since the lockdown profile ‘p1’ is in effect.
|
11
|
SQL> alter session set container = cdb$root;
SQL> drop lockdown profile p1; SQL> alter session set container = SALES; SQL> alter pluggable database close; SQL> alter pluggable database open; |
It will connect to the root, drop the lockdown profile ‘p1’, and connect back to the ‘SALES’. In order to see the effect of dropped lockdown profile, it will bounce the database.
|
12
|
SQL> alter system set cursor_sharing = EXACT;
|
It will change the value of ‘cursor_sharing’ to ‘EXACT’ since we dropped ‘p1’.
|
13
|
SQL> alter pluggable database close;
SQL> exit; |
This final step will close the PDB.
|
Lab 2: Lockdown Profiles – Disable Database Option ‘Partitioning’
Shared identities across PDBs may lead to malicious activities during the operations that are used to administer certain database options such as ‘Partitioning’. In this lab, utilizing the database option ‘Partitioning’ will be illustrated in the existence and absence of a lockdown profile that controls the option.
Lab Details: This lab will help you to disable ‘Partitioning’ option via a lockdown profile.
Database targets to be used: cdb1
Please go through the following steps:
- $ cd /u01/HOL/lockdown_profiles
- $ source cdb1.env
- $ sqlplus /nolog
- SQL> @lab2.sql
A summary of the steps executed by this lab is listed below.
Step
|
Action
|
Comments
|
1
|
$ cd /u01/HOL/lockdown_profiles
$ source cdb1.env |
It will setup the Oracle environment so that cdb1 will be ready to run our lab script.
|
2
|
$ sqlplus / as sysdba
|
You will login to SQLPlus and go through the following steps to complete our second lockdown profiles lab.
|
3
|
SQL> set lines 200 pages 1000
SQL> column PROFILE_NAME FORMAT A15 SQL> column RULE_TYPE FORMAT A10 SQL> column RULE FORMAT A20 SQL> column CLAUSE FORMAT A10 SQL> column OPTION$ FORMAT A20 SQL> column VALUE$ FORMAT A5 SQL> column STATUS FORMAT A8 |
It will format the columns of DBA_LOCKDOWN_PROFILES table so that the available lockdown profiles can be displayed smoothly in the next steps.
|
4
|
SQL> create pluggable database CUSTOMERS admin user admin identified by oracle12;
|
This will create a PDB named ‘CUSTOMERS’ in our CDB.
|
5
|
SQL> create lockdown profile p2;
SQL> alter lockdown profile p2 disable option=('Partitioning'); |
A lockdown profile named ‘p2’ will be created to disable the ‘Partitioning’ option.
|
6
|
SQL> select * from DBA_LOCKDOWN_PROFILES;
|
The current list of lockdown profiles including ‘p2’ will be displayed.
|
7
|
SQL> alter session set container = CUSTOMERS;
SQL> select parameter, value from v$option where parameter = ‘Partitioning’; |
You will be connected to the PDB that we created earlier and shown the current value of the option ‘Partitioning’.
|
8
|
SQL> create table t1 (c1 number) partition by hash (c1);
SQL> drop table t1; |
This will create a partitioned table and drop it. However, please remember that the lockdown profile ‘p2’ is not in effect yet.
|
9
|
SQL> alter system set pdb_lockdown = p2;
SQL> alter pluggable database close; SQL> alter pluggable database open; |
It will set the current lockdown profile to ‘p2’ and bounce the database in order for the changes to take effect.
|
10
|
SQL> create table t1 (c1 number) partition by hash (c1);
|
This time, you will run into the error “ORA-00439: feature not enabled : Partitioning” since the lockdown profile ‘p2’ is in effect.
|
11
|
SQL> alter session set container = cdb$root;
SQL> drop lockdown profile p2; SQL> alter session set container = CUSTOMERS; SQL> alter pluggable database close; SQL> alter pluggable database open; |
It will connect to the root, drop the lockdown profile ‘p2’, and connect back to the ‘CUSTOMERS’. In order to see the effect of dropped lockdown profile, it will bounce the database.
|
12
|
SQL> create table t1 (c1 number) partition by hash (c1);
SQL> drop table t1; |
It will successfully create a partitioned table and drop it since the lockdown profile ‘p2’ was dropped.
|
13
|
SQL> alter pluggable database close;
SQL> exit; |
This final step will close the PDB.
|
Lab 3: Lockdown Profiles – Disable Feature ‘NETWORK_ACCESS’
In some cases, customers may want to restrict the operations within a PDB that use the network to communicate outside the PDB. This lab will demonstrate how a lockdown profile can disable the network access features and prevent the outgoing network traffic in a PDB.
Lab Details: This lab will help you to disable ‘NETWORK_ACCESS’ feature via a lockdown profile.
Database targets to be used: cdb1
Please go through the following steps:
- $ cd /u01/HOL/lockdown_profiles
- $ source cdb1.env
- $ sqlplus /nolog
- SQL> @lab3.sql
The following table lists the summary of steps executed in this exercise.
Step
|
Action
|
Comments
|
1
|
$ cd /u01/HOL/lockdown_profiles
$ source cdb1.env |
It will setup the Oracle environment so that cdb1 will be ready to run our lab script.
|
2
|
$ sqlplus / as sysdba
|
You will login to SQLPlus and go through the following steps to complete our third lockdown profiles lab.
|
3
|
SQL> set lines 200 pages 1000
SQL> column PROFILE_NAME FORMAT A15 SQL> column RULE_TYPE FORMAT A10 SQL> column RULE FORMAT A20 SQL> column CLAUSE FORMAT A10 SQL> column OPTION$ FORMAT A20 SQL> column VALUE$ FORMAT A5 SQL> column STATUS FORMAT A8 |
It will format the columns of DBA_LOCKDOWN_PROFILES table so that the available lockdown profiles can be displayed smoothly in the next steps.
|
4
|
SQL> create pluggable database ORDERS admin user admin identified by oracle12;
|
This will create a PDB named ‘ORDERS’ in our CDB.
|
5
|
SQL> create lockdown profile p3;
SQL> alter lockdown profile p3 disable feature=('NETWORK_ACCESS'); |
A lockdown profile named ‘p3’ will be created to disable the ‘NETWORK_ACCESS’.
|
6
|
SQL> select * from DBA_LOCKDOWN_PROFILES;
|
The current list of lockdown profiles including ‘p3’ will be displayed.
|
7
|
SQL> alter session set container = ORDERS;
|
You will be connected to the PDB that we created earlier.
|
8
|
SQL> @sendmail.sql
SQL> execute send_mail('scott.tiger@oracle.com', 'Lockdown Lab 3', 'Testing network access.'); |
This will create and execute a PL/SQL procedure to send email via ‘UTL_SMTP’ and ‘UTL_TCP’ which are part of ‘NETWORK_ACCESS’. However, please remember that the lockdown profile ‘p3’ is not in effect yet.
|
9
|
SQL> alter system set pdb_lockdown = p3;
SQL> alter pluggable database close; SQL> alter pluggable database open; |
It will set the current lockdown profile to ‘p3’ and bounce the database in order for the changes to take effect.
|
10
|
SQL> execute send_mail('scott.tiger@oracle.com', 'Lockdown Lab 3', 'Testing network access.');
|
This time, you will run into the error “ORA-01031: insufficient privileges” since the lockdown profile ‘p3’ is in effect.
|
11
|
SQL> alter session set container = cdb$root;
SQL> drop lockdown profile p3; SQL> alter session set container = ORDERS; SQL> alter pluggable database close; SQL> alter pluggable database open; |
It will connect to the root, drop the lockdown profile ‘p3’, and connect back to the ‘ORDERS’. In order to see the effect of dropped lockdown profile, it will bounce the database.
|
12
|
SQL> execute send_mail('scott.tiger@oracle.com', 'Lockdown Lab 3', 'Testing network access.');
|
It will successfully execute the procedure since the lockdown profile ‘p3’ was dropped.
|
13
|
SQL> alter pluggable database close;
SQL> alter session set container = cdb$root; SQL> drop pluggable database SALES including datafiles; SQL> drop pluggable database CUSTOMERS including datafiles; SQL> drop pluggable database ORDERS including datafiles; SQL> exit; |
This final step will close the ORDERS PDB, connect to the root and drop all PDBs that we created previously.
|