Great, I found my Audit Policies again

Leave a reply

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…

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.