Avoid orphaned Lockdown Profiles

Posted by

Most administrators are now aware of the lockdown profile facility in the Oracle database (available since 12c). They allow a more granular level of privilege control of components and functionality within the database. I’m not going to spend more time than that on what they do – this post is just to highlight a small “gotcha” that you need to be aware of when farming out lockdown profiles to pluggable databases.

For example, perhaps one your pluggable databases should not be using UTL_HTTP and you’ve created a lockdown profile called “P1” to handle that. You would then set that profile at the PDB level via something such as:


SQL> conn / as sysdba
Connected.

SQL> alter session set container = pdb1;

Session altered.

SQL> alter system set pdb_lockdown='P1' scope=both;

System altered.

Now we have created a linkage between the existence of a database object (the lockdown profile in DBA_LOCKDOWN_PROFILES) and an initialization parameter in the spfile. What happens if that linkage is broken? Lets find out Smile.

I’ll jump back into the container database and drop the profile.


SQL>  conn / as sysdba
Connected.

SQL>  drop lockdown profile p1;

Lockdown Profile dropped.

Our pluggable database PDB1 now has an invalid initialisation parameter, so what will happen if you try to restart that pluggable database?


SQL> alter session set container = pdb1;

Session altered.

SQL> shutdown
Pluggable Database closed.
SQL> startup
Pluggable Database opened.

As you can see … nothing (bad) happens. Well, nothing immediately visible to you as a DBA that would make you take some reparative action. Administrators may be familiar with the warning message you might get when a pluggable database is opened with the “alter” command, ie


SQL> alter pluggable database MYPD open;

Warning: PDB altered with errors.

but if you try that approach in this instance, no such warning is returned.


SQL> alter pluggable database pdb1 close;

Pluggable database altered.

SQL> alter pluggable database pdb1 open;

Pluggable database altered.

So how do you even know if you are impacted by this? There are two places you can see this issue. First of these is the alert log


PDB1(3):***************************************************************
PDB1(3):WARNING: Pluggable Database PDB1 with pdb id - 3 is
PDB1(3):         altered with errors or warnings. Please look into
PDB1(3):         PDB_PLUG_IN_VIOLATIONS view for more details.
PDB1(3):***************************************************************
2020-01-13T15:49:19.449164+08:00
PDB1(3):Opening pdb with Resource Manager plan: DEFAULT_MAINTENANCE_PLAN
Pluggable database PDB1 opened read write
PDB1(3):Completed: ALTER PLUGGABLE DATABASE  OPEN 

which also tells us the other location that the warning will be logged, namely PDB_PLUG_IN_VIOLATIONS


SQL> select * from PDB_PLUG_IN_VIOLATIONS
  2  @pr
==============================
TIME                          : 13-JAN-20 03.49.17.957000 PM
NAME                          : PDB1
CAUSE                         : Lockdown Profile
TYPE                          : WARNING
ERROR_NUMBER                  : 0
LINE                          : 1
MESSAGE                       : Lockdown Profile P1 set for the PDB does not exist in CDB$ROOT.
STATUS                        : PENDING
ACTION                        : Set the pdb_lockdown parameter in the PDB to a valid lockdown profile 
                                or create a lockdown profile in CDB$ROOT container with the same name 
                                as pdb_lockdown parameter in the PDB.
CON_ID                        : 3

TL;DR

If you are using lockdown profiles for your pluggable database, make sure you keep an eye on PDB_PLUG_IN_VIOLATIONS or the alert log to make sure DBA_LOCKDOWN_PROFILES and the SPFILE entries are in sync.

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.