Having fun with PDB LOCKDOWN PROFILES

In Oracle Database 12.2 (available in the Oracle DBaaS Cloud) there’s a new functionality called “PDB LOCKDOWN PROFILES“. Actually the parameter came already in undocumented and not functional in Oracle Database 12.1.0.2 (see here).

PDB Lockdown Profiles

PDB Lockdown Profiles are meant as a granular way to restrict or enable access – mostly under the aspect of using the database in a highly shared environment but requiring security. The idea is to embed restrictions on-top of a grant, i.e. taking away certain things. As an example you could allow a user logged into a certain PDB only to change the optimizer_mode and cursor_sharing parameters when doing an ALTER SYSTEM.

The glossary in the Concepts Guide says:

A security mechanism to restrict operations that are available to local users connected to a specified PDB.

And here is a good overview in the Concepts Guide and the Security Guide for details:

Lockdown Profiles in Oracle Multitenant

Lockdown Profiles – Create, Drop, Alter

How-to-Lockdown-Profile

Lets start with a fresh PDB:

SQL> create pluggable database PDB2 
     admin user adm identified by adm file_name_convert=('/oradata/CDB2/pdbseed', '/oradata/CDB2/pdb2');

First we need to create a lockdown profile:

SQL> create lockdown profile P1;

Then we can ALTER the profile and allow only to change optimizer_mode and cursor_sharing with an ALTER SYSTEM.

SQL> alter lockdown profile P1
      disable statement=('ALTER SYSTEM') clause=('SET') OPTION ALL
       EXCEPT=('optimizer_mode','cursor_sharing');

And finally, the PDB Lockdown Profile needs to be enabled:

SQL> alter system set PDB_LOCKDOWN=P1;

Check:

SQL> show parameter pdb_l

NAME          TYPE    VALUE
------------- ------- ------
pdb_lockdown  string  P1

Where the fun begins …

Now lets connect with my default SYS user – SYS per definitionem is a common user – and switch to my PDB2:

$> sqlplus / as sysdba

SQL> alter session set container=PDB2;

SQL> alter system set sql_trace=TRUE;
*
ERROR at line 1:
ORA-01031: insufficient privileges

Aha! Ok, so let’s try …

SQL> alter system set cursor_sharing='FORCE';
System altered.

SQL> alter system set optimizer_mode='FIRST_ROWS_10';
System altered.

Ok, this works. But can I still change SQL_TRACE on the session level? Of course I can …

SQL> alter session set SQL_TRACE=TRUE;
Session altered.

Makes sense as I restricted only ALTER SYSTEM but not ALTER SESSION.
So let’s do this as well:

SQL> alter session set container=cdb$root;
Session altered.

SQL> alter lockdown profile P1 disable statement=(‘ALTER SESSION’) clause=(‘SET’) OPTION ALL EXCEPT=(‘optimizer_mode’,’cursor_sharing’)
Lockdown Profile altered.

Another tiny exercise taken directly from the documentation:

CREATE LOCKDOWN PROFILE medium;
ALTER LOCKDOWN PROFILE medium DISABLE STATEMENT=('ALTER SYSTEM');
ALTER LOCKDOWN PROFILE medium ENABLE STATEMENT=('ALTER SYSTEM') CLAUSE=('FLUSH SHARED POOL');

This will allow ONLY the ALTER SYSTEM FLUSH SHARED POOL command with ALTER SYSTEM.

SQL>   alter system set pdb_lockdown='MEDIUM';
  alter system set pdb_lockdown='MEDIUM'
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> alter system set sql_trace=true;
alter system set sql_trace=true
*
ERROR at line 1:
ORA-01031: insufficient privileges

And of course you can add more things to an existing profiles such as disabling certain features:

SQL> alter session set container=cdb$root;
Session altered.

SQL> ALTER LOCKDOWN PROFILE medium DISABLE FEATURE=('XDB_PROTOCOLS');
Lockdown Profile altered

Which profiles do exist and what’s defined?

First of all, the parameter PDB_LOCKDOWN is modifiable on a PDB level meaning you can have different profiles for different PDBs. But as far as I tested only one profile per PDB can be active and enabled.

You can look up existing profiles and their contents?

SQL>  select profile_name, rule_type, rule, clause, clause_option, status from DBA_LOCKDOWN_PROFILES order by 1;

PROFILE_NAME   RULE_TYPE  RULE           CLAUSE     CLAUSE_OPTION   STATUS
-------------- ---------- -------------- ---------- --------------- -------
MEDIUM         STATEMENT  ALTER SYSTEM                              DISABLE
MEDIUM         FEATURE    XDB_PROTOCOLS                             DISABLE
P1             STATEMENT  ALTER SESSION  SET        CURSOR_SHARING  ENABLE
P1             STATEMENT  ALTER SYSTEM   SET        OPTIMIZER_MODE  ENABLE
P1             STATEMENT  ALTER SYSTEM   SET        CURSOR_SHARING  ENABLE
P1             STATEMENT  ALTER SESSION  SET        OPTIMIZER_MODE  ENABLE
P1             STATEMENT  ALTER SESSION  SET                        DISABLE
P1             STATEMENT  ALTER SYSTEM   SET                        DISABLE
PRIVATE_DBAAS                                                       EMPTY
PUBLIC_DBAAS                                                        EMPTY
SAAS                                                                EMPTY

11 rows selected.

Summary

This is a very powerful feature – but it can be a bit tricky in the future to find out why certain things don’t work. The ORA-1031 error may now always guide into the correct direction.

One thing which puzzles me a bit:
We speak always about simplifying things, easing administration. And with PDB Lockdown Profiles you can highly complicate things. Or tease your colleagues 🙂 (please don’t, ok?!). Just don’t over-complicate things … 🙂

–Mike

4 thoughts on “Having fun with PDB LOCKDOWN PROFILES

  1. Hi Mike –

    nice writeup. Learn something new every day (in this case the detailed mechanics). Just one quick comment about "complexity" ..

    Especially when talking about multitenant, there’s two sides of the coin when it comes to the aspect of complexity: the CDB and the PDB view. While it looks like you are adding complexity for the CDB/App Admin you are actually increasing the flexibility in terms of service definition. And with taking away things, like your ALTER SYSTEM SET SQL_TRACE you are ultimately simplifying things. Taking away the capability from the end user to shoot themselves in the foot is a good thing, for both the end user and the admin .. 😉

    Cheers,
    — Hermann

  2. hi,mike,
    in my test with DBaaS on OPC, the default pdb1 does’t work with lockdown profile, but all other new create pdb works well, how about this?

    ===============
    SQL> show pdbs

    CON_ID CON_NAME OPEN MODE RESTRICTED
    ———- —————————— ———- ———-
    2 PDB$SEED READ ONLY NO
    3 PDB1 READ WRITE NO
    4 PDB4 READ WRITE NO
    5 PDB2 READ WRITE NO
    6 PDB3 READ WRITE NO
    SQL> show parameter pdb_lockdown

    NAME TYPE VALUE
    ———————————— ———– ——————————
    pdb_lockdown string LIMIT_PARAM
    SQL> create lockdown profile limit_param;

    Lockdown Profile created.

    SQL> alter lockdown profile limit_param disable statement=(‘ALTER SYSTEM’) clause ALL;

    Lockdown Profile altered.

    SQL> show parameter pdb_lockdown

    NAME TYPE VALUE
    ———————————— ———– ——————————
    pdb_lockdown string
    SQL>
    SQL> alter system set pdb_lockdown=limit_param;

    System altered.

    SQL>
    SQL> alter session set container=pdb1;

    Session altered.

    SQL> alter system checkpoint;

    System altered.

    SQL> alter session set container=pdb2;

    Session altered.

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

    SQL> alter session set container=pdb3;

    Session altered.

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

    SQL>

Leave a Reply

Your email address will not be published. Required fields are marked *