{"id":13118,"date":"2023-04-04T22:52:50","date_gmt":"2023-04-04T20:52:50","guid":{"rendered":"https:\/\/www.oradba.ch\/wordpress\/?p=13118"},"modified":"2023-06-13T11:39:09","modified_gmt":"2023-06-13T09:39:09","slug":"great-i-found-my-audit-policies-again","status":"publish","type":"post","link":"https:\/\/www.oradba.ch\/wordpress\/2023\/04\/great-i-found-my-audit-policies-again\/","title":{"rendered":"Great, I found my Audit Policies again"},"content":{"rendered":"\n

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?<\/a> 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<\/a>. See Oracle Support Document 30769454.8<\/a> Bug 30769454 – Policy Created For Some Actions Are Not Showing In Audit_Unified_Policies<\/em>.<\/p>\n\n\n\n

Install and Test<\/h2>\n\n\n\n

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.<\/p>\n\n\n\n

oracle@db19:~\/ [TSEC02] $cdh\/OPatch\/opatch lspatches\n34777391;JDK BUNDLE PATCH 19.0.0.0.230117\n34786990;OJVM RELEASE UPDATE: 19.18.0.0.230117 (34786990)\n34765931;Database Release Update : 19.18.0.0.230117 (34765931)\n29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)<\/pre>\n\n\n\n
I\u2019ll create an other audit policy to verify that we still have the issue.<\/p>\n\n\n\n
CREATE AUDIT POLICY tvdlm_dir_access\n  ACTIONS\n    READ DIRECTORY,\n    WRITE DIRECTORY,\n    EXECUTE DIRECTORY\n  ONLY TOPLEVEL;<\/pre>\n\n\n\n
And as expected, we do not see these in AUDIT_UNIFIED_POLICIES.<\/p>\n\n\n\n
SET pagesize 200\nSET linesize 160\nCOL policy_name FOR A25\nSELECT policy_name FROM audit_unified_policies\n  WHERE policy_name LIKE 'TVD%' \n  GROUP BY policy_name ORDER BY policy_name;<\/pre>\n\n\n\n
no rows selected<\/pre>\n\n\n\n
But in the view DBA_OBJECTS.<\/p>\n\n\n\n
SET pagesize 200\nSET linesize 160\nCOL object_name FOR A25\nCOL object_type FOR A25\nSELECT object_name, object_type FROM dba_objects\n  WHERE object_name LIKE 'TVD%' ORDER BY object_name;<\/pre>\n\n\n\n
OBJECT_NAME\t\t  OBJECT_TYPE\n------------------------- -------------------------\nTVDLM_DIR_ACCESS\t  UNIFIED AUDIT POLICY<\/pre>\n\n\n\n
Run opatch apply<\/em> to install the one-off patch<\/p>\n\n\n\n
oracle@db19:\/tmp\/30769454\/ [TSEC02] $cdh\/OPatch\/opatch apply\nOracle Interim Patch Installer version 12.2.0.1.36\nCopyright (c) 2023, Oracle Corporation.  All rights reserved.\n\n\nOracle Home       : \/u01\/app\/oracle\/product\/19.0.0.0\nCentral Inventory : \/u01\/app\/oraInventory\n   from           : \/u01\/app\/oracle\/product\/19.0.0.0\/oraInst.loc\nOPatch version    : 12.2.0.1.36\nOUI version       : 12.2.0.7.0\nLog file location : \/u01\/app\/oracle\/product\/19.0.0.0\/cfgtoollogs\/opatch\/opatch2023-04-04_22-41-48PM_1.log\n\nVerifying environment and performing prerequisite checks...\nOPatch continues with these patches:   30769454  \n\nDo you want to proceed? [y|n]\ny\nUser Responded with: Y\nAll checks passed.\nBacking up files...\nApplying interim patch '30769454' to OH '\/u01\/app\/oracle\/product\/19.0.0.0'\n\nPatching component oracle.rdbms.dbscripts, 19.0.0.0.0...\nPatch 30769454 successfully applied.\nLog file location: \/u01\/app\/oracle\/product\/19.0.0.0\/cfgtoollogs\/opatch\/opatch2023-04-04_22-41-48PM_1.log\n\nOPatch succeeded.<\/pre>\n\n\n\n
And finally datapatch<\/em><\/p>\n\n\n\n
oracle@db19:\/tmp\/30769454\/ [TSEC02] $cdh\/OPatch\/datapatch\nSQL Patching tool version 19.18.0.0.0 Production on Tue Apr  4 22:43:53 2023\nCopyright (c) 2012, 2023, Oracle.  All rights reserved.\n\nLog file for this invocation: \/u01\/app\/oracle\/cfgtoollogs\/sqlpatch\/sqlpatch_8785_2023_04_04_22_43_53\/sqlpatch_invocation.log\n\nConnecting to database...OK\nGathering database info...done\nBootstrapping registry and package to current versions...done\nDetermining current state...done\n\nCurrent state of interim SQL patches:\nInterim patch 30769454 (POLICY CREATED FOR SOME ACTIONS ARE NOT SHOWING IN AUDIT_UNIFIED_POLICIES):\n  Binary registry: Installed\n  SQL registry: Not installed\nInterim patch 31668882 (OJVM RELEASE UPDATE: 19.9.0.0.201020 (31668882)):\n  Binary registry: Not installed\n  SQL registry: Rolled back successfully on 30-MAR-23 04.22.06.093772 PM\nInterim patch 34786990 (OJVM RELEASE UPDATE: 19.18.0.0.230117 (34786990)):\n  Binary registry: Installed\n  SQL registry: Applied with errors on 30-MAR-23 04.25.21.102732 PM\n\nCurrent state of release update SQL patches:\n  Binary registry:\n    19.18.0.0.0 Release_Update 230111171738: Installed\n  SQL registry:\n    Applied 19.18.0.0.0 Release_Update 230111171738 with errors on 30-MAR-23 04.25.21.097389 PM\n\nAdding patches to installation queue and performing prereq checks...done\nInstallation queue:\n  No interim patches need to be rolled back\n  Patch 34765931 (Database Release Update : 19.18.0.0.230117 (34765931)):\n    Apply from 19.9.0.0.0 Release_Update 200930183249 to 19.18.0.0.0 Release_Update 230111171738\n  The following interim patches will be applied:\n    34786990 (OJVM RELEASE UPDATE: 19.18.0.0.230117 (34786990))\n    30769454 (POLICY CREATED FOR SOME ACTIONS ARE NOT SHOWING IN AUDIT_UNIFIED_POLICIES)\n\nInstalling patches...\nPatch installation complete.  Total patches installed: 3\n\nValidating logfiles...done\nPatch 34765931 apply: SUCCESS\n  logfile: \/u01\/app\/oracle\/cfgtoollogs\/sqlpatch\/34765931\/25078403\/34765931_apply_TSEC02_2023Apr04_22_44_17.log (no errors)\nPatch 34786990 apply: SUCCESS\n  logfile: \/u01\/app\/oracle\/cfgtoollogs\/sqlpatch\/34786990\/25032666\/34786990_apply_TSEC02_2023Apr04_22_44_16.log (no errors)\nPatch 30769454 apply: SUCCESS\n  logfile: \/u01\/app\/oracle\/cfgtoollogs\/sqlpatch\/30769454\/25157729\/30769454_apply_TSEC02_2023Apr04_22_47_53.log (no errors)\nSQL Patching tool complete on Tue Apr  4 22:48:16 2023<\/pre>\n\n\n\n
opatch now lists the oneonf patch<\/p>\n\n\n\n
oracle@db19:\/tmp\/30769454\/ [TSEC02] $cdh\/OPatch\/opatch lspatches\n30769454;POLICY CREATED FOR SOME ACTIONS ARE NOT SHOWING IN AUDIT_UNIFIED_POLICIES\n34777391;JDK BUNDLE PATCH 19.0.0.0.230117\n34786990;OJVM RELEASE UPDATE: 19.18.0.0.230117 (34786990)\n34765931;Database Release Update : 19.18.0.0.230117 (34765931)\n29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)\n\nOPatch succeeded.<\/pre>\n\n\n\n
Without further adjustments the created policy is now visible in AUDIT_UNIFIED_POLICIES.<\/p>\n\n\n\n
SET pagesize 200\nSET linesize 160\nCOL policy_name FOR A25\nSELECT policy_name FROM audit_unified_policies\n  WHERE policy_name LIKE 'TVD%' \n  GROUP BY policy_name ORDER BY policy_name;<\/pre>\n\n\n\n
POLICY_NAME\n-------------------------\nTVDLM_DIR_ACCESS<\/pre>\n\n\n\n
Conclusion<\/h2>\n\n\n\n
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…<\/p>\n","protected":false},"excerpt":{"rendered":"
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 \u2013 Policy Created For Some Actions Are Not Showing In Audit_Unified_Policies.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[13,101,11,32,198],"tags":[],"class_list":["post-13118","post","type-post","status-publish","format-standard","hentry","category-audit","category-bug","category-security","category-security-audit","category-unified-audit"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p1aErb-3pA","jetpack_sharing_enabled":true,"jetpack-related-posts":[{"id":13000,"url":"https:\/\/www.oradba.ch\/wordpress\/2023\/03\/help-i-lost-my-brand-new-unified-audit-policy\/","url_meta":{"origin":13118,"position":0},"title":"Help I lost my brand new Unified Audit Policy?","author":"Stefan","date":"16. March 2023","format":false,"excerpt":"Just realized that under some conditions audit policies are not shown in AUDIT_UNIFIED_POLICIES. This blog post does provide a few information and a test case.","rel":"","context":"In "Bug"","block_context":{"text":"Bug","link":"https:\/\/www.oradba.ch\/wordpress\/category\/bug\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":9867,"url":"https:\/\/www.oradba.ch\/wordpress\/2022\/03\/how-to-write-unified-audit-trail-records-to-syslog\/","url_meta":{"origin":13118,"position":1},"title":"How to write Unified Audit Trail Records to SYSLOG\u00a0","author":"Stefan","date":"23. March 2022","format":false,"excerpt":"With the introduction of Oracle Unified Audit, Oracle has completely redesigned the process of logging audit events. With the new unified audit trail, there is only one place where audit records are stored. Ok, the audit trail exists per PDB and for a read only database additionally somehow as a\u2026","rel":"","context":"In "18c"","block_context":{"text":"18c","link":"https:\/\/www.oradba.ch\/wordpress\/category\/oracle-database\/18c\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.oradba.ch\/wordpress\/wp-content\/uploads\/Oracle-SYSLOG-Audit-PDB.png?fit=1200%2C630&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.oradba.ch\/wordpress\/wp-content\/uploads\/Oracle-SYSLOG-Audit-PDB.png?fit=1200%2C630&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/www.oradba.ch\/wordpress\/wp-content\/uploads\/Oracle-SYSLOG-Audit-PDB.png?fit=1200%2C630&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/www.oradba.ch\/wordpress\/wp-content\/uploads\/Oracle-SYSLOG-Audit-PDB.png?fit=1200%2C630&ssl=1&resize=700%2C400 2x, https:\/\/i0.wp.com\/www.oradba.ch\/wordpress\/wp-content\/uploads\/Oracle-SYSLOG-Audit-PDB.png?fit=1200%2C630&ssl=1&resize=1050%2C600 3x"},"classes":[]},{"id":13852,"url":"https:\/\/www.oradba.ch\/wordpress\/2023\/08\/sql-toolbox-for-simplified-oracle-unified-audit-data-analysis\/","url_meta":{"origin":13118,"position":2},"title":"SQL Toolbox for simplified Oracle Unified Audit Data Analysis","author":"Stefan","date":"28. August 2023","format":false,"excerpt":"On my journey through the area of database security, Oracle Unified Audit has been a constant companion. I not only created audit concepts, but also often had the opportunity to implement them. Besides the configuration, the administration and evaluation of the audit data was always part of it. Occasionally I\u2026","rel":"","context":"In "19c"","block_context":{"text":"19c","link":"https:\/\/www.oradba.ch\/wordpress\/category\/oracle-database\/19c\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.oradba.ch\/wordpress\/wp-content\/uploads\/saua_teact.sql.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.oradba.ch\/wordpress\/wp-content\/uploads\/saua_teact.sql.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/www.oradba.ch\/wordpress\/wp-content\/uploads\/saua_teact.sql.png?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/www.oradba.ch\/wordpress\/wp-content\/uploads\/saua_teact.sql.png?resize=700%2C400&ssl=1 2x"},"classes":[]},{"id":13876,"url":"https:\/\/www.oradba.ch\/wordpress\/2023\/12\/easy-audit-data-analysis-with-sql-developer-reports\/","url_meta":{"origin":13118,"position":3},"title":"Easy Audit Data Analysis with SQL Developer Reports","author":"Stefan","date":"21. December 2023","format":false,"excerpt":"In one of my last blog post SQL Toolbox for simplified Oracle Unified Audit Data Analysis, I introduced a set of scripts designed to streamline Oracle Unified Audit data analysis. These scripts, now available on my GitHub repository oehrlis\/oradba, have received positive feedback. Building on that, I'd like to explore\u2026","rel":"","context":"In "19c"","block_context":{"text":"19c","link":"https:\/\/www.oradba.ch\/wordpress\/category\/oracle-database\/19c\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.oradba.ch\/wordpress\/wp-content\/uploads\/ChartAuditEventsDay-1.png?fit=1200%2C916&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.oradba.ch\/wordpress\/wp-content\/uploads\/ChartAuditEventsDay-1.png?fit=1200%2C916&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/www.oradba.ch\/wordpress\/wp-content\/uploads\/ChartAuditEventsDay-1.png?fit=1200%2C916&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/www.oradba.ch\/wordpress\/wp-content\/uploads\/ChartAuditEventsDay-1.png?fit=1200%2C916&ssl=1&resize=700%2C400 2x, https:\/\/i0.wp.com\/www.oradba.ch\/wordpress\/wp-content\/uploads\/ChartAuditEventsDay-1.png?fit=1200%2C916&ssl=1&resize=1050%2C600 3x"},"classes":[]},{"id":3240,"url":"https:\/\/www.oradba.ch\/wordpress\/2019\/09\/audit-trail-cleanup-in-oracle-multitenant-environments\/","url_meta":{"origin":13118,"position":4},"title":"Audit Trail cleanup in Oracle Multitenant environments","author":"Stefan","date":"20. September 2019","format":false,"excerpt":"A crucial aspect of any database audit concept is the management and maintenance of audit trails. Depending on the defined audit policies and the database activity, an audit trail can grow relatively quickly. Oracle Multitenant environments increase the operational effort because the root container and each PDB uses their own\u2026","rel":"","context":"In "12R2"","block_context":{"text":"12R2","link":"https:\/\/www.oradba.ch\/wordpress\/category\/oracle-database\/12r2\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":14847,"url":"https:\/\/www.oradba.ch\/wordpress\/2024\/01\/its-a-wrap-insights-from-the-soug-espresso-on-oracle-audit\/","url_meta":{"origin":13118,"position":5},"title":"It\u2019s a Wrap: Insights from the SOUG Espresso on Oracle Audit","author":"Stefan","date":"26. January 2024","format":false,"excerpt":"In my latest SOUG Espresso session, Easy Audit Data Analysis with SQL Developer Reports, I focused on Oracle Unified Audit Analysis. This was an opportunity to share my experience and insights into database security and audit data workflow enhancement. Event Highlights Based on my considerable experience with Oracle databases, I\u2026","rel":"","context":"In "19c"","block_context":{"text":"19c","link":"https:\/\/www.oradba.ch\/wordpress\/category\/oracle-database\/19c\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.oradba.ch\/wordpress\/wp-content\/uploads\/SOUG2024_expresso_audit_oehrli.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.oradba.ch\/wordpress\/wp-content\/uploads\/SOUG2024_expresso_audit_oehrli.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/www.oradba.ch\/wordpress\/wp-content\/uploads\/SOUG2024_expresso_audit_oehrli.png?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/www.oradba.ch\/wordpress\/wp-content\/uploads\/SOUG2024_expresso_audit_oehrli.png?resize=700%2C400&ssl=1 2x"},"classes":[]}],"_links":{"self":[{"href":"https:\/\/www.oradba.ch\/wordpress\/wp-json\/wp\/v2\/posts\/13118","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.oradba.ch\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.oradba.ch\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.oradba.ch\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.oradba.ch\/wordpress\/wp-json\/wp\/v2\/comments?post=13118"}],"version-history":[{"count":4,"href":"https:\/\/www.oradba.ch\/wordpress\/wp-json\/wp\/v2\/posts\/13118\/revisions"}],"predecessor-version":[{"id":13430,"href":"https:\/\/www.oradba.ch\/wordpress\/wp-json\/wp\/v2\/posts\/13118\/revisions\/13430"}],"wp:attachment":[{"href":"https:\/\/www.oradba.ch\/wordpress\/wp-json\/wp\/v2\/media?parent=13118"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.oradba.ch\/wordpress\/wp-json\/wp\/v2\/categories?post=13118"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.oradba.ch\/wordpress\/wp-json\/wp\/v2\/tags?post=13118"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}