Oracle 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 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
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
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.sqlintentionally 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
- Remove and Clean Up Components from Oracle 11.2 – 12.2 – General Guidelines and Precautions
- APEX – Oracle Application Express Clean Up
- OWM – Oracle Workspace Manager Clean Up
- DV – Oracle Database Vault Clean Up
- OLS – Oracle Label Security Clean Up
- SDO – Oracle Spatial Data Option Clean Up
- CONTEXT – Oracle Text Clean Up
- ORDIM – Oracle Multi Media Clean Up
- XOQ – Oracle OLAP API Clean Up
- APS – Oracle OLAP Analytical Workspace Clean Up
- AMD – Oracle OLAP Catalog Clean Up
- OWB – Oracle Warehouse Builder Clean Up
- EXF/RUL – Oracle Expression Filters and Rules Manager Clean Up
- EM – Enterprise Manager Database Control Clean Up
- JAVAVM/XML – Oracle Java Virtual Machine and XDK Clean Up
- XDB – Oracle XML Database Clean Up
Finally download a slide deck about the Component Clean Up on this blog:
https://mikedietrichde.com/slides/
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
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