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:
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=('sql_trace','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');
Note from Jan 29, 2018:
I did copy/paste the above example from the documentation – it missed the “underscore” in SHARED_POOL. I added it afterwards as somebody commented on it complaining that the example does not work – sorry for any inconvenience!
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
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
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>
Thanks Hermann 🙂
Mike
That’s a interesting finding …
Let me send this to the Multitenant folks.
Cheers
Mike
If I try the example from the document I get this:
SQL> ALTER LOCKDOWN PROFILE medium ENABLE STATEMENT=(‘ALTER SYSTEM’) CLAUSE=(‘FLUSH SHARED POOL’);
ALTER LOCKDOWN PROFILE medium ENABLE STATEMENT=(‘ALTER SYSTEM’) CLAUSE=(‘FLUSH SHARED POOL’)
*
ERROR at line 1:
ORA-65248: invalid clause
Should CLAUSE be SET or SUSPEND RESUME ?
Nope. Not sure what I was thinking. The Oracle example needs some work. This works:
SQL> ALTER LOCKDOWN PROFILE medium ENABLE STATEMENT=(‘ALTER SYSTEM’) CLAUSE=(‘FLUSH BUFFER_CACHE’);
Lockdown Profile altered.
Michael,
sorry, I copied/pasted without the underscore. When I tried the example I added the underscore but missed to include it when I edited the blog post.
The correct syntax for the doc example is:
SQL> alter lockdown profile medium disable statement=(‘ALTER SYSTEM’) clause=(‘FLUSH SHARED_POOL’) ;
Lockdown Profile altered.
— the underscore in SHARED_POOL was missing previously – and is still missing in the docu example – sorry for the inconvenience!
SQL> alter session set container=pdb2;
Session altered.
SQL> alter system flush shared_pool;
System altered.
SQL> alter session set container=cdb$root;
Session altered.
SQL> alter system set PDB_LOCKDOWN=medium;
System altered.
SQL> alter session set container=pdb2;
Session altered.
SQL> alter system flush shared_pool;
alter system flush shared_pool
*
ERROR at line 1:
ORA-01031: insufficient privileges
–Mike
@Mike,
Thank you for another nice post.
Would you know how PDB lockdown profiles work with Database Vault in place? I have to admit my knowledge of Database Vault is very limited but wondering whether these 2 features step over each other’s toes and if yes then how do they work (or not) when used together
Hi Narendra,
I have not heard anything AGAINST it working. So my assumption would be: Yes.
Cheers
Mike