Great, I found my Audit Policies again

A while ago I wrote a blog post about issues with some Oracle Unified Audit Policies see Help I lost my brand new Unified Audit Policy? In the meantime, the whole thing no longer looks so tragic. The problem is an official bug for which Oracle has already released a one-off patch. See Oracle Support Document 30769454.8 Bug 30769454 – Policy Created For Some Actions Are Not Showing In Audit_Unified_Policies.

Install and Test

Let’s see how our system looks before we install the patch. The output of OPatch shows that nothing special has been installed except RU 19.18.

oracle@db19:~/ [TSEC02] $cdh/OPatch/opatch lspatches
34777391;JDK BUNDLE PATCH 19.0.0.0.230117
34786990;OJVM RELEASE UPDATE: 19.18.0.0.230117 (34786990)
34765931;Database Release Update : 19.18.0.0.230117 (34765931)
29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)

I’ll create an other audit policy to verify that we still have the issue.

CREATE AUDIT POLICY tvdlm_dir_access
  ACTIONS
    READ DIRECTORY,
    WRITE DIRECTORY,
    EXECUTE DIRECTORY
  ONLY TOPLEVEL;

And as expected, we do not see these in AUDIT_UNIFIED_POLICIES.

SET pagesize 200
SET linesize 160
COL policy_name FOR A25
SELECT policy_name FROM audit_unified_policies
  WHERE policy_name LIKE 'TVD%' 
  GROUP BY policy_name ORDER BY policy_name;

no rows selected

But in the view DBA_OBJECTS.

SET pagesize 200
SET linesize 160
COL object_name FOR A25
COL object_type FOR A25
SELECT object_name, object_type FROM dba_objects
  WHERE object_name LIKE 'TVD%' ORDER BY object_name;

OBJECT_NAME		  OBJECT_TYPE
------------------------- -------------------------
TVDLM_DIR_ACCESS	  UNIFIED AUDIT POLICY

Run opatch apply to install the one-off patch

oracle@db19:/tmp/30769454/ [TSEC02] $cdh/OPatch/opatch apply
Oracle Interim Patch Installer version 12.2.0.1.36
Copyright (c) 2023, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/19.0.0.0
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/19.0.0.0/oraInst.loc
OPatch version    : 12.2.0.1.36
OUI version       : 12.2.0.7.0
Log file location : /u01/app/oracle/product/19.0.0.0/cfgtoollogs/opatch/opatch2023-04-04_22-41-48PM_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   30769454  

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
Backing up files...
Applying interim patch '30769454' to OH '/u01/app/oracle/product/19.0.0.0'

Patching component oracle.rdbms.dbscripts, 19.0.0.0.0...
Patch 30769454 successfully applied.
Log file location: /u01/app/oracle/product/19.0.0.0/cfgtoollogs/opatch/opatch2023-04-04_22-41-48PM_1.log

OPatch succeeded.

And finally datapatch

oracle@db19:/tmp/30769454/ [TSEC02] $cdh/OPatch/datapatch
SQL Patching tool version 19.18.0.0.0 Production on Tue Apr  4 22:43:53 2023
Copyright (c) 2012, 2023, Oracle.  All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_8785_2023_04_04_22_43_53/sqlpatch_invocation.log

Connecting to database...OK
Gathering database info...done
Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of interim SQL patches:
Interim patch 30769454 (POLICY CREATED FOR SOME ACTIONS ARE NOT SHOWING IN AUDIT_UNIFIED_POLICIES):
  Binary registry: Installed
  SQL registry: Not installed
Interim patch 31668882 (OJVM RELEASE UPDATE: 19.9.0.0.201020 (31668882)):
  Binary registry: Not installed
  SQL registry: Rolled back successfully on 30-MAR-23 04.22.06.093772 PM
Interim patch 34786990 (OJVM RELEASE UPDATE: 19.18.0.0.230117 (34786990)):
  Binary registry: Installed
  SQL registry: Applied with errors on 30-MAR-23 04.25.21.102732 PM

Current state of release update SQL patches:
  Binary registry:
    19.18.0.0.0 Release_Update 230111171738: Installed
  SQL registry:
    Applied 19.18.0.0.0 Release_Update 230111171738 with errors on 30-MAR-23 04.25.21.097389 PM

Adding patches to installation queue and performing prereq checks...done
Installation queue:
  No interim patches need to be rolled back
  Patch 34765931 (Database Release Update : 19.18.0.0.230117 (34765931)):
    Apply from 19.9.0.0.0 Release_Update 200930183249 to 19.18.0.0.0 Release_Update 230111171738
  The following interim patches will be applied:
    34786990 (OJVM RELEASE UPDATE: 19.18.0.0.230117 (34786990))
    30769454 (POLICY CREATED FOR SOME ACTIONS ARE NOT SHOWING IN AUDIT_UNIFIED_POLICIES)

Installing patches...
Patch installation complete.  Total patches installed: 3

Validating logfiles...done
Patch 34765931 apply: SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/34765931/25078403/34765931_apply_TSEC02_2023Apr04_22_44_17.log (no errors)
Patch 34786990 apply: SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/34786990/25032666/34786990_apply_TSEC02_2023Apr04_22_44_16.log (no errors)
Patch 30769454 apply: SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/30769454/25157729/30769454_apply_TSEC02_2023Apr04_22_47_53.log (no errors)
SQL Patching tool complete on Tue Apr  4 22:48:16 2023

opatch now lists the oneonf patch

oracle@db19:/tmp/30769454/ [TSEC02] $cdh/OPatch/opatch lspatches
30769454;POLICY CREATED FOR SOME ACTIONS ARE NOT SHOWING IN AUDIT_UNIFIED_POLICIES
34777391;JDK BUNDLE PATCH 19.0.0.0.230117
34786990;OJVM RELEASE UPDATE: 19.18.0.0.230117 (34786990)
34765931;Database Release Update : 19.18.0.0.230117 (34765931)
29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)

OPatch succeeded.

Without further adjustments the created policy is now visible in AUDIT_UNIFIED_POLICIES.

SET pagesize 200
SET linesize 160
COL policy_name FOR A25
SELECT policy_name FROM audit_unified_policies
  WHERE policy_name LIKE 'TVD%' 
  GROUP BY policy_name ORDER BY policy_name;

POLICY_NAME
-------------------------
TVDLM_DIR_ACCESS

Conclusion

As written in the original blog post, the issue is not critical, but can cause problems during automated deployment. Especially if you check in AUDIT_UNIFIED_POLICIES if a policy already exists. The workaround is to query DBA_OBJECTS directly instead of AUDIT_UNIFIED_POLICIES. Or install the One-Off patch. It is a pleasure when issues are fixed quickly by a bugfix. The probability is by the way relatively high, that this bugfix will be fixed with the next release update in April. We’ll see…

OraDBA

Just another Site about Oracle, Database Security, Linux, Mac OS X and more

Great, I found my Audit Policies again

Install and Test

Conclusion