DBA Hub

📋Steps in this guide1/5

Multitenant : PDB Lockdown Profiles in Oracle Database 12c Release 2 (12.2)

Restrict the operations possible inside a PDB using a lockdown profile in Oracle Database 12c Release 2 (12.2).

oracle 12cconfigurationintermediate
by OracleDba
12 views
1

Basic Commands

The basic process of creating, enabling, disabling and dropping a lockdown profile is relatively simple. The user administering the PDB lockdown profiles described here will need the and system privileges. In these examples we will perform all these operations as the user. In the following example we create two PDB lockdown profiles in the root container. One will be used as the system default and one for a specific PDB. We need to add some restrictions, but for the moment we'll keep this simple. We set the parameter in the root container to set a default lockdown profile for all PDBs. We can see this setting is in place at the PDB level, but we can also override it by setting a PDB-specific lockdown profile. We can reset the values of the parameter at the PDB level to return to the default lockdown profile. The changed doesn't appear to be visible until the PDB is restarted. Reseting the parameter in the root container disables the default lockdown profile. Once again, the change doesn't seem to take place until an instance restart takes place. PDB lockdown profiles are dropped as follows. If the instance or any PDBs references them they will still be dropped, and the lockdown profile will no longer be active, but the parameter will not be reset automatically. Information about PDB lockdown profiles can be displayed using the view. You can use variations on the following query to check the impact of some of the commands used in this article. You may want to alter the format of the columns, depending on what you are trying to display. The database comes with three default PDB lockdown profiles called , and . These are empty profiles, containing no restrictions, which you can tailor to suit your own needs if you so wish. The remainder of the article will discuss the types of restrictions available when planning a PDB lockdown profile. All the commands below reference a profile called , which can be created and dropped using the following commands.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
CONN / AS SYSDBA

CREATE LOCKDOWN PROFILE default_pdb_lockdown;
CREATE LOCKDOWN PROFILE pdb1_specfic_lockdown;

ALTER LOCKDOWN PROFILE default_pdb_lockdown DISABLE FEATURE = ('NETWORK_ACCESS');
ALTER LOCKDOWN PROFILE pdb1_specfic_lockdown DISABLE FEATURE = ('NETWORK_ACCESS', 'OS_ACCESS');

ALTER SYSTEM SET PDB_LOCKDOWN = default_pdb_lockdown;

ALTER SESSION SET CONTAINER = pdb1;
SHOW PARAMETER PDB_LOCKDOWN;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pdb_lockdown                         string      DEFAULT_PDB_LOCKDOWN
SQL>


ALTER SYSTEM SET PDB_LOCKDOWN = pdb1_specfic_lockdown;
SHOW PARAMETER PDB_LOCKDOWN;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pdb_lockdown                         string      PDB1_SPECFIC_LOCKDOWN
SQL>

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER = pdb1;
ALTER SYSTEM RESET PDB_LOCKDOWN;

SHOW PARAMETER PDB_LOCKDOWN;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pdb_lockdown                         string      PDB1_SPECFIC_LOCKDOWN
SQL>


-- Restart PDB.
SHUTDOWN IMMEDIATE;
STARTUP;

SHOW PARAMETER PDB_LOCKDOWN;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pdb_lockdown                         string      DEFAULT_PDB_LOCKDOWN
SQL>

CONN / AS SYSDBA
ALTER SYSTEM RESET PDB_LOCKDOWN;

SHOW PARAMETER PDB_LOCKDOWN;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pdb_lockdown                         string      DEFAULT_PDB_LOCKDOWN
SQL>


-- Restart the instance.
SHUTDOWN IMMEDIATE;
STARTUP;


SHOW PARAMETER PDB_LOCKDOWN;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pdb_lockdown                         string      
SQL>

CONN / AS SYSDBA

DROP LOCKDOWN PROFILE default_pdb_lockdown;
DROP LOCKDOWN PROFILE pdb1_specfic_lockdown;

SET LINESIZE 200

COLUMN profile_name FORMAT A30
COLUMN rule_type FORMAT A20
COLUMN rule FORMAT A20
COLUMN clause FORMAT A20
COLUMN clause_option FORMAT A20
COLUMN option_value FORMAT A20
COLUMN min_value FORMAT A20
COLUMN max_value FORMAT A20
COLUMN list FORMAT A20

SELECT profile_name,
       rule_type,
       rule,
       clause,
       clause_option,
       option_value,
       min_value,
       max_value,
       list,
       status
FROM   dba_lockdown_profiles
ORDER BY 1;

CREATE LOCKDOWN PROFILE my_profile;
DROP LOCKDOWN PROFILE my_profile;
2

Lockdown Options

In Oracle 12c Release 2 (12.2) there are only two options ( , ) that can be enabled or disabled in a lockdown profile, but this may increase in future. Having no specific option restrictions in place is the equivalent of using the following. Here are some examples of enabling or disabling options. Using doesn't really make sense with only two options available, but it will be useful if more options are added in future.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
ALTER LOCKDOWN PROFILE my_profile ENABLE OPTION ALL;

-- Enable.
ALTER LOCKDOWN PROFILE my_profile ENABLE OPTION = ('DATABASE QUEUING');
ALTER LOCKDOWN PROFILE my_profile ENABLE OPTION = ('PARTITIONING');
ALTER LOCKDOWN PROFILE my_profile ENABLE OPTION ALL;
ALTER LOCKDOWN PROFILE my_profile ENABLE OPTION ALL EXCEPT = ('PARTITIONING');

-- Disable.
ALTER LOCKDOWN PROFILE my_profile DISABLE OPTION = ('DATABASE QUEUING');
ALTER LOCKDOWN PROFILE my_profile DISABLE OPTION = ('PARTITIONING');
ALTER LOCKDOWN PROFILE my_profile DISABLE OPTION ALL;
ALTER LOCKDOWN PROFILE my_profile DISABLE OPTION ALL EXCEPT = ('DATABASE QUEUING','PARTITIONING');
3

Lockdown Features

Features can be enabled or disabled individually, or in groups known as feature bundles. The feature bundles and their individual features are listed in the ALTER LOCKDOWN PROFILE documentation. Having no specific feature restrictions in place is the equivalent of using the following. Here are some examples of enabling or disabling feature bundles and features.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
ALTER LOCKDOWN PROFILE my_profile ENABLE FEATURE ALL;

-- Enable/disable one or more features.
ALTER LOCKDOWN PROFILE my_profile ENABLE  FEATURE = ('UTL_HTTP');
ALTER LOCKDOWN PROFILE my_profile DISABLE FEATURE = ('UTL_HTTP', 'UTL_SMTP');

-- Enable/disable one or more feature bundles.
ALTER LOCKDOWN PROFILE my_profile ENABLE  FEATURE = ('NETWORK_ACCESS');
ALTER LOCKDOWN PROFILE my_profile DISABLE FEATURE = ('NETWORK_ACCESS', 'OS_ACCESS');

-- Enable/disable all features.
ALTER LOCKDOWN PROFILE my_profile ENABLE FEATURE ALL;
ALTER LOCKDOWN PROFILE my_profile DISABLE FEATURE ALL;

-- Enable/disable all features with bundle and/or feature exceptions.
ALTER LOCKDOWN PROFILE my_profile ENABLE FEATURE ALL EXCEPT = ('NETWORK_ACCESS');
ALTER LOCKDOWN PROFILE my_profile DISABLE FEATURE ALL EXCEPT = ('OS_ACCESS', 'UTL_HTTP', 'UTL_SMTP');
4

Lockdown Statements

At present four statements ( , , , ) can be restricted using a PDB lockdown profile. The following examples show how to enable or disable entire commands or groups of them using and . The scope of the restriction can be reduced using the , , , options and values. The ALTER LOCKDOWN PROFILE documentation describes the available syntax.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
ALTER LOCKDOWN PROFILE my_profile ENABLE STATEMENT = ('ALTER DATABASE', 'ALTER PLUGGABLE DATABASE');
ALTER LOCKDOWN PROFILE my_profile DISABLE STATEMENT = ('ALTER DATABASE', 'ALTER PLUGGABLE DATABASE');

ALTER LOCKDOWN PROFILE my_profile ENABLE STATEMENT ALL EXCEPT = ('ALTER DATABASE', 'ALTER PLUGGABLE DATABASE');
ALTER LOCKDOWN PROFILE my_profile DISABLE STATEMENT ALL EXCEPT = ('ALTER DATABASE', 'ALTER PLUGGABLE DATABASE');

ALTER LOCKDOWN PROFILE my_profile DISABLE STATEMENT = ('ALTER PLUGGABLE DATABASE')
  CLAUSE = ('DEFAULT TABLESPACE', 'DEFAULT TEMPORARY TABLESPACE');

ALTER LOCKDOWN PROFILE my_profile DISABLE STATEMENT = ('ALTER SYSTEM')
  CLAUSE ALL EXCEPT = ('FLUSH SHARED_POOL');

-- Can't set CPU_COUNT higher than 1.
ALTER LOCKDOWN PROFILE my_profile DISABLE STATEMENT = ('ALTER SYSTEM')
  CLAUSE = ('SET') OPTION = ('CPU_COUNT') MAXVALUE = '1';

-- Can only set CPU_COUNT to values 1, 2 or 3.
ALTER LOCKDOWN PROFILE my_profile DISABLE STATEMENT = ('ALTER SYSTEM')
  CLAUSE = ('SET') OPTION = ('CPU_COUNT') MINVALUE = '1' MAXVALUE = '3';
5

Considerations

It should be obvious from the examples in the Lockdown Profile Basics section there is a flaw in this mechanism if you define poor lockdown profiles. Imagine a scenario where you have a highly restrictive lockdown profile for one PDB, but a less restrictive default lockdown profile. If you don't restrict the ability to modify the parameter in the PDB with the highly restrictive profile, what's to stop the PDB administrator from resetting the PDB-level parameter and reverting to the less restrictive default lockdown profile? If you are planning to use a variety of PDB lockdown profiles in a single instance, you need to define your lockdown profiles very carefully to prevent this type of mistake. This is a classic case of garbage-in, garbage-out. Option, feature and statement restrictions can be combined into a single PDB lockdown profile. Whilst testing it's easy to get yourself into a bit of a mess. Remember, you can always switch back to the root container and drop the problematic lockdown profile and start again. For more information see: - Using PDB Lockdown Profiles to Restrict Operations on PDBs - CREATE LOCKDOWN PROFILE - ALTER LOCKDOWN PROFILE - DROP LOCKDOWN PROFILE - DBA_LOCKDOWN_PROFILES - Multitenant : All Articles Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!