DBA Hub

📋Steps in this guide1/3

PDB Lockdown Profiles in Oracle 12.2 DBACLASS

PDB Lockdown Profiles in Oracle 12.2 is a newfeature. Use PDB lockdown profiles in a multitenant environment to restrict user operations in pluggable databases (PDBs).

oracle clusteringintermediate
by OracleDba
13 views
1

Overview

Use the ALTER LOCKDOWN PROFILE statement to alter a PDB lockdown profile. You can use PDB lockdown profiles in a multitenant environment to restrict user operations in pluggable databases (PDBs). Pluggable databases bring a new separation of database administrator roles. The DBA administers the container database, the CDB, but can then delegate the administration of individual pluggable databases. Let’s take an example of a CDB that is a dedicated development environment. The fast and thin provisioning features (snapshot clones) make it possible to give a PDB to each developer. Because it is their database, the CDB administrator can grant them the DBA role for the PDB, so that they can do whatever they wish there, as long as their privileges are limited to this pluggable database. Now we are going to create LockDown Profile and test this feature. Create a lockdown profile:

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
SQL> conn / as SYSDBA

SQL> create lockdown profile skant;

Lockdown Profile created.

SQL> ALTER LOCKDOWN PROFILE skant DISABLE STATEMENT = ('ALTER SYSTEM');

Lockdown Profile altered.
2

Section 2

Connect in PDB database and check the default pdb_lockdown setting: We can now apply the lockdown profile to the pluggable database PDB1, simply by setting the pdb_lockdown parameter for that container:

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
SQL> ALTER SESSION SET CONTAINER = pdb1;

System altered.

SQL> SHOW PARAMETER PDB_LOCKDOWN

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pdb_lockdown                         string      DEFAULT_PDB_LOCKDOWN
SQL> alter system checkpoint;

System altered.

SQL> ALTER SESSION SET CONTAINER = pdb1;

System altered.

SQL> SHOW PARAMETER PDB_LOCKDOWN

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pdb_lockdown                         string      DEFAULT_PDB_LOCKDOWN

SQL> ALTER SYSTEM SET PDB_LOCKDOWN = skant;

System altered.

SQL> SHOW PARAMETER PDB_LOCKDOWN;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pdb_lockdown                         string      SKANT
3

Section 3

Now from this PDB, fire ALTER SYSTEM statement. It is throwing an error as expected because ALTER SYSTEM is disabled due to the PDB_LOCKDOWN setting.

Code/Command (click line numbers to comment):

1
2
3
4
5
SQL> alter system checkpoint;
alter system checkpoint
*
ERROR at line 1:
ORA-01031: insufficient privileges

Comments (0)

Please to add comments

No comments yet. Be the first to comment!