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

Posted on July 29, 2017 by Removal 7

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:

Tags:

You may also like...

7 Responses

  1. Andreas says:
    March 6, 2018 at 15:52

    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
    • Mike.Dietrich says:
      March 6, 2018 at 17:28

      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
  2. James Gardner says:
    February 26, 2019 at 16:59

    Hey Mike great site. i never understood why it needed to become so difficult uninstalling products from 12.1 onwards.
    A word of caution with the unofficial uninstall process for OLS:
    We created a new db at 12.2 and ols got installed by mistake. We uninstalled it again using the undocumented method.
    When we came to datapump from our existing 12.1 db (which didnt have ols) the import failed with the following errors:

    Master table “SYS”.”IMPDP_DBNAME” successfully loaded/unloaded
    Starting “SYS”.”IMPDP_DBNAME”: “/******** AS SYSDBA” parfile=impdp.par
    Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
    ORA-39126: Worker unexpected fatal error in KUPW$WORKER.PROCESS_MARKER_STEP [TABLE_DATA:”SYS”.”IMPDP_DBNAME”]
    BEGIN LBACSYS.OLS$DATAPUMP.system_callout_imp(:1); END;
    ORA-31625: Schema LBACSYS is needed to import this object, but is unaccessible
    ORA-01435: user does not exist
    ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 105
    ORA-06512: at “SYS.KUPW$WORKER”, line 12098
    ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 105
    ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 86
    ORA-06512: at “SYS.KUPW$WORKER”, line 9737
    ORA-06512: at “SYS.KUPW$WORKER”, line 9716
    ORA-06512: at “SYS.KUPW$WORKER”, line 23641
    —– PL/SQL Call Stack —–
    object line object
    handle number name
    0xbd4fa818 32239 package body SYS.KUPW$WORKER.WRITE_ERROR_INFORMATION
    0xbd4fa818 12119 package body SYS.KUPW$WORKER.DETERMINE_FATAL_ERROR
    0xbd4fa818 23937 package body SYS.KUPW$WORKER.PROCESS_MARKER_STEP
    0xbd4fa818 5123 package body SYS.KUPW$WORKER.LOAD_METADATA
    0xbd4fa818 13108 package body SYS.KUPW$WORKER.DISPATCH_WORK_ITEMS
    0xbd4fa818 2311 package body SYS.KUPW$WORKER.MAIN
    0xbe386e20 2 anonymous block
    Checking existence of new_seqno row
    Type completion row exist count is: 0
    Inserting new row at seqno: 100000002
    Created type completion for seqno 100000002
    Process order range is 491 to 491
    In PROCESS_MARKER_STEP with marker 1
    Looking for system callouts
    Found system callout LBACSYS.OLS$DATAPUMP with tag LABEL_SECURITY, level 1 and connect_type SOFT
    In function CONNECT_USER with connect type SOFT current connect type NONE current user SYS new user LBACSYS
    In procedure DETERMINE_FATAL_ERROR with ORA-31625: Schema LBACSYS is needed to import this object, but is unaccessible

    The workaround to get it to proceed was to create the lbacsys user. The import then gave one failure message but then continued to import everything else cleanly:
    Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
    ORA-39342: Internal error – failed to import internal objects tagged with LABEL_SECURITY due to ORA-06550: line 1, column
    7:
    PLS-00201: identifier ‘LBACSYS.OLS$DATAPUMP’ must be declared
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored
    .
    Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER

    That user can then be locked but suggests somewhere its holding a memory of label security being installed.

    Reply
    • Mike.Dietrich says:
      February 28, 2019 at 18:39

      James,

      first of all, sorry for hitting such a trap. I was not aware of it.
      And I wonder where this is coming from. Let me check this with my mates.

      Thanks for the workaround!

      Cheers,
      Mike

      Reply
    • Mike.Dietrich says:
      March 1, 2019 at 10:07

      James,

      could you please provide us the logs of the removal of OLS?
      That would be important – or, if you have opened an SR, send me the SR number?

      email: mike.dietrich –at– oracle.com

      Cheers,
      Mike

      Reply
      • Gabriel says:
        March 16, 2020 at 18:57

        Hi Mike
        we hit the same here, after removing the OLS on a 18.6 “the classic way”. was james able to solve the issue?

        Thx
        Gabriel

        Reply
        • Gabriel says:
          March 16, 2020 at 19:20

          Sorry for the double-post: to solve the issue, i reinstalled the OLS via DBCA. the error is gone.

          Greetings
          Gabriel

          Reply

Leave a Reply

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

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

Archives

Categories

Upgrade @YouTube

Subscribe via Email

Join 5,154 other subscribers

Follow me on Twitter