Oracle Label Security (OLS) Clean Up in Oracle Database 11.2-12.2

Posted on by

Oracle Label Security (OLS) Clean Up in Oracle Database 11.2-12.2Oracle Label Security (OLS) clean up in Oracle Database 11.2-12.2 is almost as simple and straight forward as removing OWM or APEX. You can do it online without downtime. But in older releases a relink operation did cause downtime. Oracle Label Security is an extra cost option. More information is available on oracle.com.

Oracle Label Security (OLS) Clean Up in Oracle Database 11.2-12.2

Oracle Label Security (OLS) Clean Up in Oracle Database 11.2-12.2Oracle Label Security is dependent on Oracle Database Vault (DV). In case DV is present you must remove it first if you plan to remove Oracle Label Security.

Before you start removing anything from your database please make sure you read my introductory blog post about how to Remove and Clean Up Components from Oracle 11.2 – 12.2 . You’ll find links to monitor proper component removal there as well as in the SQL Scripts Repository on this blog.

Oracle Database 11.2.0.4

The removal process is documented in the official Oracle Documentation.

$ sqlplus / as sysdba
SQL> @?/rdbms/admin/catnools.sql

Afterwards there will be 21 objects invalid.

SQL> col object_name format a30
SQL> col object_type format a15
SQL> set pages 2000
SQL> set line 200
SQL> select object_type, object_name from dba_objects where status='INVALID';

OBJECT_TYPE	OBJECT_NAME
--------------- ------------------------------
VIEW		DBA_AUDIT_TRAIL
SYNONYM 	DBA_AUDIT_TRAIL
VIEW		USER_AUDIT_TRAIL
SYNONYM 	USER_AUDIT_TRAIL
VIEW		DBA_AUDIT_SESSION
SYNONYM 	DBA_AUDIT_SESSION
VIEW		USER_AUDIT_SESSION
SYNONYM 	USER_AUDIT_SESSION
VIEW		DBA_AUDIT_STATEMENT
SYNONYM 	DBA_AUDIT_STATEMENT
VIEW		USER_AUDIT_STATEMENT
SYNONYM 	USER_AUDIT_STATEMENT
VIEW		DBA_AUDIT_OBJECT
SYNONYM 	DBA_AUDIT_OBJECT
VIEW		USER_AUDIT_OBJECT
SYNONYM 	USER_AUDIT_OBJECT
VIEW		DBA_AUDIT_EXISTS
SYNONYM 	DBA_AUDIT_EXISTS
VIEW		DBA_COMMON_AUDIT_TRAIL
SYNONYM 	DBA_COMMON_AUDIT_TRAIL
PACKAGE BODY	MGMT_DB_LL_METRICS

21 rows selected.

Recompile to clean up.

$ sqlplus / as sysdba
SQL> @?/rdbms/admin/utlrp.sql

Finally unlink the option once downtime is acceptable. This is optional but I recommend it.

$ chopt disable lbac

Oracle Database 12.1.0.2

OLS removal Oracle 12.1.0.2

Oracle Label Security clean up in Oracle Database 12.1.0.2 is absolutely flawless and completes without any errors (at least in a standard installation where OLS isn’t configured).

$ sqlplus / as sysdba
SQL> @?/rdbms/admin/catnools.sql

That’s it. No errors, no recompilation, no restart required.

Oracle Database 12.2.0.1

OLS Removal 12.2 non-CDB

The removal of Oracle Label Security in Oracle Database 12.2.0.1 is a bit more tricky. The removal script catnools.sql does not exist. As a workaround I’m using the catnools.sql from an Oracle Database 12.1.0.2 installation. You’ll find more information in the Oracle Label Security Admin Guide.

The official take (thanks to Andreas Groetz for the hint to the document) on this is in MOS Note:2253235.1 – catnools.sql does not exist in 12.2.0.1:

  • There’s no catnools.sql intentionally as the removal is not supported anymore
  • Therefore you should follow this procedure to disable Label security in your database: 
    EXEC LBACSYS.OLS_ENFORCEMENT.DISABLE_OLS;
  • A restart of the database is required according to the documentation – and the procedure should be executed on all nodes in a RAC environment (??)

The same procedure – but for each PDB separately – will apply in case of Oracle Multitenant.

Below are the unsupported ways to clean up OLS completely.

non-CDB

If you’d like to go the old way (which does not seem to be supported anymore), you must copy or access the catnools.sql from an Oracle Database 12.1.0.2 installation.

$ cp $OH12102/rdbms/admin/catnools.sql  $ORACLE_HOME/rdbms/admin
$ sqlplus / as sysdba
SQL> @$ORACLE_HOME12102/rdbms/admin/catnools.sql

You’ll find two invalid packages and package bodies afterwards. This is expected as I used an older catnools.sql from a previous installation. Clean it up with:

SQL> drop package LBAC_EXP;
SQL> drop package OLS_ENFORCEMENT;
CDB

The OLS removal from a Multitenant database works the same way with one exception: you must use catcon.pl and you must remove OLS from all PDBs including the PDB$SEED first before removing it from CDB$ROOT. Otherwise you will see ORA-06598: insufficient INHERIT PRIVILEGES privilege errors.

At first you must copy or access the catnools.sql from an Oracle Database 12.1.0.2 installation.

$ cp $OH12102/rdbms/admin/catnools.sql $ORACLE_HOME/rdbms/admin

Afterwards start to remove OLS from all pluggable databases including the PDB$SEED:

$ cd $ORACLE_HOME/rdbms/admin
$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -C 'CDB$ROOT' -e -b catnools_pdbs -d '''.''' catnools.sql

In addition remove OLS now from CDB$ROOT:

$ cd $ORACLE_HOME/rdbms/admin
$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -c 'CDB$ROOT' -e -b catnools_cdb -d '''.''' catnools.sql

This leads to a number of invalid objects:

CON_ID OWNER	    OBJECT_TYPE  OBJECT_NAME			STATUS
------ ------------ ------------ ------------------------------ --------
     1 SYS	    PACKAGE BODY LBAC_EXP			INVALID
     1 SYS	    PACKAGE BODY OLS_ENFORCEMENT		INVALID
     2 SYS	    PACKAGE BODY LBAC_EXP			INVALID
     2 SYS	    PACKAGE BODY OLS_ENFORCEMENT		INVALID
     3 SYS	    PACKAGE BODY LBAC_EXP			INVALID
     3 SYS	    PACKAGE BODY OLS_ENFORCEMENT		INVALID
     4 SYS	    PACKAGE BODY LBAC_EXP			INVALID
     4 SYS	    PACKAGE BODY OLS_ENFORCEMENT		INVALID

8 rows selected.

Clean them up with a simple script containing:

$ vi clean_ols.sql
    drop package LBAC_EXP;
    drop package OLS_ENFORCEMENT;

Finally execute clean_ols.sql with catcon.pl.

$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b clean_ols -d '''.''' clean_ols.sql

Afterwards OLS is completely removed from all containers and there are no invalid objects anymore.

Component Clean Up Series

Finally download a slide deck about the Component Clean Up on this blog:
https://mikedietrichde.com/slides/

 

Share this:

2 thoughts on “Oracle Label Security (OLS) Clean Up in Oracle Database 11.2-12.2”

  1. Hi Mike,

    I was looking into removing OLS from my 12.2 CDB but according to “Catnools.sql Does Not Exist in 12.2.0.1 (Doc ID 2253235.1)” catnools.sql has been remove from 12.2 intentionally because it´s not supported to remove OLS.
    How do you see this topic?

    br
    Andreas

    Reply

    • Andreas,

      many thanks for the hint – I wasn’t aware that there’s a DISABLE procedure in 12.2. I updated the blog post with your input. My way still will work of course. But if the official way provides a bullet proof environment as well in terms of license questions all should be fine (and of course the documented way is much easier and shorter than mine). I just wonder why you need to restart the database. And why you should repeat the action on all RAC nodes. The call IMHO gets executed in the database – and there’s only one package call EXEC LBACSYS.OLS_ENFORCEMENT.DISABLE_OLS;.

      Furthermore I wonder where the user psmith_ols is coming from 😉 You may not need to login with this one I’d guess …

      Thanks again!
      Mike

      Reply

Leave a Reply

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

* Checkbox to comply with GDPR is required

*

I agree