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

Gurban Adigozalov says:

On Non-CDB 12.2 RAC database with enabled XML DB, customer complained about the error messages during the execution of some user packages during which getting below error.
ERROR- ORA-04088: error during execution of trigger’LBACSYS.LBAC$BEFORE_ALTER’

Select comp_name, status from dba_registry.

COMP_NAME STATUS
—————————— ——————————————–
Oracle Database Catalog Views VALID
Oracle Database Packages and Types INVALID
Oracle Real Application Clusters VALID
JServer JAVA Virtual Machine VALID
Oracle XDK VALID
Oracle Database Java Packages VALID
Oracle XML Database INVALID
Oracle Multimedia VALID

@?/rdbms/admin/utlrp.sql

ERROR at line 1:
ORA-04045: errors during recompilation/revalidation of SYS.XDB_PI_TRIG
ORA-06512: at “SYS.UTL_RECOMP”, line 923
ORA-04088: error during execution of trigger ‘LBACSYS.LBAC$BEFORE_ALTER’
ORA-00604: error occurred at recursive SQL level 3
ORA-04045: errors during recompilation/revalidation of LBACSYS.LBAC_EVENTS
ORA-04067: not executed, package body “LBACSYS.LBAC_EVENTS” does not exist
ORA-06508: PL/SQL: could not find program unit being called: “LBACSYS.LBAC_EVENTS”
ORA-06512: at line 2
ORA-06508: PL/SQL: could not find program unit being called: “LBACSYS.LBAC_EVENTS”
ORA-06512: at line 2
ORA-06512: at “SYS.UTL_RECOMP”, line 27
ORA-06512: at “SYS.UTL_RECOMP”, line 61
ORA-06512: at “SYS.UTL_RECOMP”, line 787
ORA-06512: at line 4

# copied catnools.sql from 12.1 home and tried to clean lbacsys schema.
SQL> @?/rdbms/admin/catnools.sql

PL/SQL procedure successfully completed.

select value$ into l_ols_config_status from lbacsys.ols$props where NAME = ‘OLS_CONFIGURED_FLAG’;
*
ERROR at line 5:
ORA-06550: line 5, column 55:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 5, column 3:
PL/SQL: SQL Statement ignored

#Tried manually execute catnools.sql lines
SQL> DROP TRIGGER LBACSYS.lbac$after_drop;
DROP TRIGGER LBACSYS.lbac$after_drop
*
ERROR at line 1:
ORA-04045: errors during recompilation/revalidation of SYS.XDB_PI_TRIG
ORA-04088: error during execution of trigger ‘LBACSYS.LBAC$BEFORE_ALTER’
ORA-00604: error occurred at recursive SQL level 2
ORA-04045: errors during recompilation/revalidation of LBACSYS.LBAC_EVENTS
ORA-04067: not executed, package body “LBACSYS.LBAC_EVENTS” does not exist
ORA-06508: PL/SQL: could not find program unit being called:
“LBACSYS.LBAC_EVENTS”
ORA-06512: at line 2
ORA-06508: PL/SQL: could not find program unit being called:
“LBACSYS.LBAC_EVENTS”
ORA-06512: at line 2
#using DBCA does not help, got same error in GUI.

#Disabled cluster mode and start one of the instance in a upgrade mode
srvctl stop database -db
startup nomount;
alter system set cluster_database=FALSE scope=spfile sid=’*’;
shutdown immediate;

startup upgrade;
:->sqlplus “/as sysdba”

SQL> @?/rdbms/admin/utlrp.sql
…
PL/SQL procedure successfully completed.
…
OBJECTS WITH ERRORS
——————-
53
….
ERRORS DURING RECOMPILATION
—————————
0
Function created.
PL/SQL procedure successfully completed.
Function dropped.
PL/SQL procedure successfully completed.
SQL> SQL> SQL>

##Still a lot of invalid objects.

SQL> SELECT owner, object_type, object_name FROM all_objects WHERE status = ‘INVALID’ and owner=’LBACSYS’ order by 1 ;

OWNER OBJECT_TYPE OBJECT_NAME
——————————————————————————————————————————– ———————– ——————————————————————————————————————————–
LBACSYS PACKAGE TO_LABEL_LIST
LBACSYS FUNCTION PRIVS_TO_CHAR
LBACSYS FUNCTION NUMERIC_LABEL_TO_LBAC
LBACSYS FUNCTION LBAC_LABEL_TO_NUMERIC
LBACSYS PACKAGE BODY TO_LABEL_LIST
LBACSYS FUNCTION PRIVS_TO_CHAR_N
LBACSYS PACKAGE BODY LBAC_SESSION
LBACSYS PACKAGE BODY LBAC_SERVICES
LBACSYS PACKAGE BODY LBAC_UTL
LBACSYS PROCEDURE SESSINFO_CLEANUP
LBACSYS PACKAGE BODY LBAC_LGSTNDBY_UTIL

OWNER OBJECT_TYPE OBJECT_NAME
——————————————————————————————————————————– ———————– ——————————————————————————————————————————–
LBACSYS PACKAGE BODY SA_AUDIT_ADMIN
LBACSYS PACKAGE BODY LBAC_SYSDBA
LBACSYS PACKAGE BODY LBAC_POLICY_ADMIN
LBACSYS PACKAGE BODY SA_COMPONENTS
LBACSYS FUNCTION TO_LBAC_LABEL_INTERNAL
LBACSYS FUNCTION TO_LBAC_LABEL
LBACSYS FUNCTION TO_LBAC_DATA_LABEL_INTERNAL
LBACSYS FUNCTION TO_LBAC_DATA_LABEL
LBACSYS FUNCTION TO_NUMERIC_DATA_LABEL
LBACSYS FUNCTION LBAC_LABEL_TO_CHAR
LBACSYS PACKAGE BODY LBAC$SA_LABELS

OWNER OBJECT_TYPE OBJECT_NAME
——————————————————————————————————————————– ———————– ——————————————————————————————————————————–
LBACSYS PACKAGE LBAC$SA
LBACSYS PACKAGE BODY LBAC$SA
LBACSYS PACKAGE BODY SA_SESSION
LBACSYS PACKAGE SA_UTL
LBACSYS PACKAGE BODY SA_UTL
LBACSYS FUNCTION NUMERIC_MERGE_LABEL
LBACSYS FUNCTION NUMERIC_GREATEST_LBOUND
LBACSYS FUNCTION NUMERIC_LEAST_UBOUND
LBACSYS FUNCTION NUMERIC_DOMINATES
LBACSYS FUNCTION OLS_LABEL_DOMINATES
LBACSYS FUNCTION NUMERIC_STRICTLY_DOMINATES

OWNER OBJECT_TYPE OBJECT_NAME
——————————————————————————————————————————– ———————– ——————————————————————————————————————————–
LBACSYS FUNCTION NUMERIC_DOMINATED_BY
LBACSYS FUNCTION NUMERIC_STRICTLY_DOMINATED_BY
LBACSYS PACKAGE SA_USER_ADMIN
LBACSYS PACKAGE BODY SA_USER_ADMIN
LBACSYS PACKAGE BODY OLS$DATAPUMP
LBACSYS PACKAGE BODY LBAC_EVENTS

39 rows selected.

SQL> alter package lbacsys.lbac_events compile body;

Warning: Package Body altered with compilation errors.

# rexecute of catnool does not help however at this time manual execution of lines works.
SQL> @$ORACLE_HOME/rdbms/admin/catnools.sql
PL/SQL procedure successfully completed.

select value$ into l_ols_config_status from lbacsys.ols$props where NAME = ‘OLS_CONFIGURED_FLAG’;
*
ERROR at line 5:
ORA-06550: line 5, column 55:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 5, column 3:
PL/SQL: SQL Statement ignored

SQL> DROP TRIGGER LBACSYS.lbac$after_drop;
Trigger dropped.
SQL> DROP TRIGGER LBACSYS.lbac$after_create;
Trigger dropped.
SQL> DROP TRIGGER LBACSYS.lbac$before_alter;
Trigger dropped.
SQL> DECLARE
CURSOR lbacroles IS
SELECT granted_role
FROM dba_role_privs
WHERE grantee = ‘LBACSYS’
AND granted_role like ‘%_DBA’
AND admin_option = ‘YES’;
CURSOR lbaccontexts IS
SELECT namespace
FROM dba_context
WHERE schema = ‘LBACSYS’;
CURSOR lbacsynonyms IS
SELECT synonym_name
FROM dba_synonyms
WHERE table_owner = ‘LBACSYS’;
rolename VARCHAR2(30);

BEGIN

— drop roles
FOR r IN lbacroles LOOP
dbms_output.put_line(‘Dropping role ‘ || r.granted_role );

BEGIN
EXECUTE IMMEDIATE ‘DROP ROLE ‘ ||
dbms_assert.enquote_name(r.granted_role,FALSE);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(‘Unable to drop role ‘ || r.granted_role);
END;

END LOOP;

— drop contexts
FOR c IN lbaccontexts LOOP
dbms_output.put_line(‘Dropping context ‘ || c.namespace);

BEGIN
EXECUTE IMMEDIATE ‘DROP CONTEXT ‘ ||
dbms_assert.enquote_name(c.namespace,FALSE);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(‘Unable to drop context ‘ || c.namespace);
END;

END LOOP;

— drop synonyms
FOR s IN lbacsynonyms LOOP
dbms_output.put_line(‘Dropping public synonym ‘ || s.synonym_name);

BEGIN
EXECUTE IMMEDIATE ‘DROP PUBLIC SYNONYM ‘ ||
dbms_assert.enquote_name(s.synonym_name,FALSE);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(‘Unable to drop synonym ‘ || s.synonym_name);
END;

END LOOP;

END;
/

PL/SQL procedure successfully completed.

SQL> EXECUTE LBACSYS.LBAC_CACHE.UPDATE_PROPS_TABLE(0, TRUE);

PL/SQL procedure successfully completed.

SQL> DROP USER LBACSYS CASCADE;

User dropped.

SQL> DELETE FROM exppkgact$ WHERE PACKAGE = ‘LBAC_UTL’;

0 rows deleted.

SQL> COMMIT;

Commit complete.

SQL> SELECT owner, object_type, object_name FROM all_objects WHERE status = ‘INVALID’ and owner=’LBACSYS’ order by 1 ;

no rows selected

#enable cluster mode and start Database normally.
SQL> alter system set cluster_database=TRUE scope=spfile sid=’*’;

System altered.

SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
:->srvctl start database -db
:->sqlplus “/as sysdba”

SQL> set lin 300
SQL> select comp_name, status from dba_registry ;

COMP_NAME STATUS
————————————-
Oracle Database Catalog Views VALID
Oracle Database Packages and Types VALID
Oracle Real Application Clusters VALID
JServer JAVA Virtual Machine VALID
Oracle XDK VALID
Oracle Database Java Packages VALID
Oracle XML Database VALID
Oracle Multimedia VALID

8 rows selected.