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

Oracle Label Security (OLS) Clean Up in Oracle Database 11.2-12.2Oracle Label Security (OLS) clean up in Oracle Database 11.2-19c 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-19c

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 – 19c . 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 12.2.0.1

At first, please see MOS Note: 2253235.1 (Catnools.sql Does Not Exist in 12.2.0.1 ) where the author explains that the missing catnools.sql is intentionally since 12.2.0.1. I don’t know what the motivation is since I think that you should have the possibility to remove a component when you desire to. But I won’t comment this further on the blog.

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.

CDB 19.12.0

Again for Oracle 19c, please see MOS Note: 2253235.1 (Catnools.sql Does Not Exist in 12.2.0.1 ) where the author explains that the missing catnools.sql is intentionally since 12.2.0.1. I don’t know what the motivation is since I think that you should have the possibility to remove a component when you desire to. But I won’t comment this further on the blog.

But the removal process for OLS is very different in Oracle 19c. At first, there is still no catnools.sql. And the MOS notes asking you to “unlink” simply OLS are not correct either. Maybe the authors haven’t recognized that you can’t unlink OLS in 19c anymore. The chopt option is simply not there anymore.

In addition, there is now a dependency with Data Vault (DV). If you try to remove OLS as I showed in 12.2.0.1, you will end up with a ton of invalid objects, and many related to DV. So you need to remove DV at first.

This removal is a bit strange as well since the ?/rdbms/admin/dvremov.sql in 19c does not work explicitly for container databases. Luckily I found the 21c documentation example – and I have a 21c installation as well.

So this is what I did in order to remove DV at first.

Copy the Oracle 21c dvremov.sql into your 19c Oracle Home. Of course, this step is not essential as you can call it from your 21c home as well. But I like to have scripts in place:

cp $ORACLE_HOME/rdbms/admin/dvremov.sql $OH19/rdbms/admin

The execute it in the PDBs at first, afterwards in CDB$ROOT. Keep in mind that all PDBs need to be open. This step will fail if you didn’t use the 21c dvremov.sql script but the 19c version.

cd $ORACLE_HOME/rdbms/admin
$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -C 'CDB$ROOT' -e -b dvremov_pdbs -d '''.''' dvremov.sql
$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -c 'CDB$ROOT' -e -b dvremov_cdb -d '''.''' dvremov.sql

Good news: The script works flawless in 19c, and it does not leave any leftovers.

Now we can remove Label Security with the steps we used in 12.2.0.1. And again, you need to copy in either the 12.1.0.2 version of catnools.sql, or potentially the 11.2.0.4 version will work, too.

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

Then initiate the removal process:

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

And of course, there are leftovers since nobody paid attention to make this script work in 19c. It still is a 12.1.0.2 script at the end of the day.

SQL> select con_id, object_name, object_type , owner from CDB_OBJECTS where status='INVALID' order by 1,2;

CON_ID OBJECT_NAME		OBJECT_TYPE	     OWNER
------ ------------------------ -------------------- ------------------
     1 CONFIGURE_OLS		PROCEDURE	     SYS
     1 IS_OLS_SUPPORTED 	FUNCTION	     SYS
     1 LBAC_EXP 		PACKAGE BODY	     SYS
     1 OLS_ENFORCEMENT		PACKAGE BODY	     SYS
     1 VALIDATE_OLS		PROCEDURE	     SYS
     2 CONFIGURE_OLS		PROCEDURE	     SYS
     2 IS_OLS_SUPPORTED 	FUNCTION	     SYS
     2 LBAC_EXP 		PACKAGE BODY	     SYS
     2 OLS_ENFORCEMENT		PACKAGE BODY	     SYS
     2 VALIDATE_OLS		PROCEDURE	     SYS
     3 CONFIGURE_OLS		PROCEDURE	     SYS
     3 IS_OLS_SUPPORTED 	FUNCTION	     SYS
     3 LBAC_EXP 		PACKAGE BODY	     SYS
     3 OLS_ENFORCEMENT		PACKAGE BODY	     SYS
     3 VALIDATE_OLS		PROCEDURE	     SYS
     4 CONFIGURE_OLS		PROCEDURE	     SYS
     4 IS_OLS_SUPPORTED 	FUNCTION	     SYS
     4 LBAC_EXP 		PACKAGE BODY	     SYS
     4 OLS_ENFORCEMENT		PACKAGE BODY	     SYS
     4 VALIDATE_OLS		PROCEDURE	     SYS
     5 CONFIGURE_OLS		PROCEDURE	     SYS
     5 IS_OLS_SUPPORTED 	FUNCTION	     SYS
     5 LBAC_EXP 		PACKAGE BODY	     SYS
     5 OLS_ENFORCEMENT		PACKAGE BODY	     SYS
     5 VALIDATE_OLS		PROCEDURE	     SYS

25 rows selected.

So we need to create a cleanup_old.sql script:

drop PROCEDURE SYS.CONFIGURE_OLS;
drop PROCEDURE SYS.VALIDATE_OLS;
drop FUNCTION SYS.IS_OLS_SUPPORTED;
drop PACKAGE BODY SYS.LBAC_EXP;
drop PACKAGE BODY SYS.OLS_ENFORCEMENT;

Then execute it:

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

And please remember that you will need the 12.1.0.2 catnools.sql and the 21c dvremov.sql scripts. And please don’t ask me why it needs to be this complicated …

Component Clean Up Series

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

 

Share this: