{"id":202,"date":"2015-02-13T13:42:55","date_gmt":"2015-02-13T12:42:55","guid":{"rendered":"https:\/\/www.carajandb.com\/2015\/02\/13\/unified-auditing-en-1\/"},"modified":"2018-12-21T12:52:33","modified_gmt":"2018-12-21T11:52:33","slug":"unified-auditing-en-1","status":"publish","type":"post","link":"https:\/\/carajandb.com\/en\/2015\/02\/13\/unified-auditing-en-1\/","title":{"rendered":"Oracle 12c Unified Auditing – Part 1"},"content":{"rendered":"

With Oracle 12c it is all getting better! And that includes the new auditing possibilities as well, which can be grouped as \u201cUnified Auditing<\/em>\u201d. First of all unified auditing basically does not mean anything more but the previous functions, standard auditing and fine-grained auditing, being combined. This means that we are dealing with three (!) different methods now, because the \u201cformer\u201d ones still exist, even if they are disabled by default – despite what is said in several documents. I can also just partly agree on the claim, that the same commands as in Oracle 11g are being recorded. Only a failed logon is being monitored for example, but not a successful logon \u2013 read more lately.
\n
\nThere are several significant improvements compared to the previous auditing functions. These include among others audit records not being written in tables right away, but first existing in the SGA only. Those who find this too insecure can, of course, overwrite this procedure so the audit data is written in the table like in every regular table right away. And this is the second change already: in Version 12c a partitioned table in the AUDSYS scheme is being used for the audit data. The table, with the cryptic name \u201cCLI_SWP\u2026\u201d, is located in the SYSAUX by default and should better be relocated to a separate table space e.g. AUDITTS.<\/p>\n

Activating Unified Auditing<\/h2>\n

IIn a few blogs and also in the Oracle documentation it is said that you can switch the database to Unified Auditing i.e. to go without the \u201cformer\u201d functions. Therefore the Oracle kernel has to be relinked. I would postpone this, as the Unified Auditing does not yet master the full extent of the previous fine-grained auditing (see Oracle 12c Database Security Guide: \u201cIf you want to audit specific columns or use event handlers, use fine-grained auditing\u201d).<\/p>\n

The following Query could give the impression that unified auditing is not active; this impression is wrong. The \u201cFALSE\u201d just means that unified auditing is not being used exclusively but the \u201cformer\u201d auditing can be used additionally.<\/p>\n

SELECT parameter, value\r\n  FROM v$option\r\n WHERE parameter = 'Unified Auditing';\r\n\r\nPARAMETER            VALUE\r\n-------------------- --------------------\r\nUnified Auditing     FALSE\r\n<\/pre>\n
The next improvement affects the administration of unified auditing in general. Until now it was necessary to do a initialization, if you for example wanted to relocate the audit table or wanted to delete data sets by \u201cPURGE\u201d (see also the blog: \u00a0<\/a>Manage Audit Data<\/a>). With unified auditing this is not necessary or possible anymore. You can just ignore the following command and error message.<\/p>\n
BEGIN\r\n   dbms_audit_mgmt.init_cleanup(\r\n      audit_trail_type => dbms_audit_mgmt.audit_trail_unified,\r\n      default_cleanup_interval => 24);\r\nEND;\r\n\/ \r\nBEGIN\r\n   ERROR at line 1:\r\n   ORA-46250: Invalid value for argument 'AUDIT_TRAIL_TYPE'\r\n   ORA-06512: at \"SYS.DBMS_AUDIT_MGMT\", line 177\r\n   ORA-06512: at \"SYS.DBMS_AUDIT_MGMT\", line 605\r\n   ORA-06512: at line 2\r\n<\/pre>\n
Note:<\/h3>\n
There are a few documents, which say that the parameter AUDIT_TRAIL has got no notability in unified auditing. That is wrong! If the Parameter is not set to \u201cDB\u201d, no unified auditing data is being written and some commands in this context do not work:<\/p>\n
execute DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;\r\n   ERROR at line 1:\r\n   ORA-46276: DBMS_AUDIT_MGMT operation on unified audit trail failed\r\n   ORA-55906: Secure file log [id: 0 name: ORA$AUDIT_NEXTGEN_LOG] does not exist\r\n   ORA-06512: at \"SYS.DBMS_AUDIT_MGMT\", line 1746\r\n   ORA-06512: at line 1\r\n<\/pre>\n
Therefore you should definitely set the parameter to \u201cDB\u201d to use unified auditing.<\/p>\n
Relocating the Table<\/h2>\n
The relocation of the tables is quite simple:<\/p>\n
CREATE TABLESPACE auditts\r\n   DATAFILE SIZE 1000M AUTOEXTEND ON NEXT 100M MAXSIZE 2000M;\r\n   \r\nBEGIN\r\n   dbms_audit_mgmt.set_audit_trail_location(\r\n   audit_trail_type => dbms_audit_mgmt.audit_trail_unified,\r\n   audit_trail_location_value => 'AUDITTS');\r\nEND;\r\n\/\r\n<\/pre>\n
Deleting Audit Data<\/h2>\n
The procedure for deleting audit data is the same as the one already known from former versions (see blog:\u00a0Manage Audit Data<\/a>). That means the audit records, which can be deleted, are marked first (SET_LAST_ARCHIVE_TIMESTAMP) and either a purge job is performed afterwards or the data sets are deleted directly by a clean procedure.<\/p>\n
Unfortunately there is a messy bug, but a patch is already existing for it: Patch 18743542; 12C UNIFIED AUDIT TRAIL, CANNOT DELETE LAST_ARCHIVE_TIME.<\/em>.<\/p>\n
Here again how to delete audit records (the initialization is inapplicable, as I already mentioned before):<\/p>\n
BEGIN\r\n   dbms_audit_mgmt.set_last_archive_timestamp(\r\n      audit_trail_type => dbms_audit_mgmt.audit_trail_unified,\r\n\t  last_archive_time => to_date('13.01.2015 13:22:00','DD.MM.YYYY HH24:MI:SS'));\r\nEND;\r\n\/\r\n\r\nSELECT last_archive_ts \r\n  FROM dba_audit_mgmt_last_arch_ts;\r\n\r\nLAST_ARCHIVE_TS                     \r\n-----------------------------------\r\n13-JAN-15 01.22.00.000000 PM +00:00\r\n\r\nBEGIN\r\n   dbms_audit_mgmt.clean_audit_trail(\r\n      audit_trail_type => dbms_audit_mgmt.audit_trail_unified,\r\n\t  use_last_arch_timestamp => TRUE);\r\nEND;\r\n<\/pre>\n
In this example the purge job is not called up, but the deletion performed directly. But it seems as if nothing happens to start with\u2026 Wait for it!<\/p>\n
In the second part we are working on the actual auditing.<\/p>\n","protected":false},"excerpt":{"rendered":"
With Oracle 12c it is all getting better! And that includes the new auditing possibilities as well, which can be grouped as \u201cUnified Auditing\u201d. First of all unified auditing basically does not mean anything more but the previous functions, standard auditing and fine-grained auditing, being combined. This means that we are dealing with three (!) different methods now, because the \u201cformer\u201d ones still exist, even if they are disabled by default – despite what is said in several documents. I can also just partly agree on the claim, that the same commands as in Oracle 11g are being recorded. Only […]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_crdt_document":"","_uag_custom_page_level_css":"","site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[406],"tags":[317],"class_list":["post-202","post","type-post","status-publish","format-standard","hentry","category-oracle-en","tag-unified-auditing-3"],"acf":{"occupation":"Write the Occupation of the Person","person_can-be-speaker":true,"person_is-team":true,"person_related-user-account":null,"person_ordering-number":50,"publication_speakers":null,"publication_content-language":["de"],"publication_files":null,"publication_event":"","publication_date":null},"spectra_custom_meta":{"_fgj2wp_old_id":["202"],"_yoast_wpseo_metadesc":["Oracle 12c Unified Auditing"],"_yoast_wpseo_metakeywords":["Oracle 12c, Unified Auditing, Auditing, AUD$, FGA_LOG$"],"layout_show-author-box":["1"],"_layout_show-author-box":["field_5a64ee9cc6490"],"_edit_lock":["1545393017:3"],"_edit_last":["3"],"_vc_post_settings":["a:1:{s:10:\"vc_grid_id\";a:0:{}}"],"_yoast_wpseo_content_score":["60"],"layout_header":["light"],"_layout_header":["field_5a48111185e5f"],"layout_background":[""],"_layout_background":["field_5a481193c0995"],"layout_hide_services":["0"],"_layout_hide_services":["field_5a4811c5c0996"],"layout_show-categories-tags":["1"],"_layout_show-categories-tags":["field_5a64eee4c6491"],"blogpost_related-publications":[""],"_blogpost_related-publications":["field_5a480debdac62"],"blogpost_related-files":[""],"_blogpost_related-files":["field_5a480f377db29"],"_yoast_wpseo_primary_category":[""],"rank_math_primary_category":[""],"rank_math_description":["Oracle 12c Unified Auditing"],"rank_math_news_sitemap_robots":["index"],"rank_math_robots":["a:1:{i:0;s:5:\"index\";}"],"astra_style_timestamp_css":["1773686891"],"rank_math_internal_links_processed":["1"],"wpil_sync_report3":["1"],"wpil_links_inbound_internal_count":["0"],"wpil_links_inbound_internal_count_data":["eJxLtDKwqq4FAAZPAf4="],"wpil_links_outbound_internal_count":["3"],"wpil_links_outbound_internal_count_data":["eJztU01PAyEQ\/SuGg7e2y9oPpWmMiRcTq0ePZLpMurQsEJa1Nab\/xd\/iL3PYVm1jvJj0YPQGj\/eGN\/MAxJl41iIb3ws+EOzBayOnTqGRt9oumeB0XIuRYIa2Uis2TuSaVKwJhtGKDzLByhi96PUCGmhsUeZZ3u8WEGABVs26hat62ipcd33pL52P2tkJgbJwNqKNp1D58aPG1QRC1IXBFtBqwi+GAhqlo7bzDtoWLiDSST4SC1eCtVh3oAxoVf1OuIlYJe0oS\/b6ZM7VMS3zoWDfO0yMc8E0OQoWqLWZ4Fu9b\/V3tKECQGIX2nKCvb5sJ0CwUqjk7El608y1TepsW9C45NjZxKQ57lpuC2S7sa5KXXsMsggIET9mzDnZqSU00SXaPhwDFEsayl4iCa6cagzKA3a+u6S9eB23+EZTcz+PvD86YuSDz8gV7kfOv0R+kPdgeNy8OTU9BQtzPLlKBk+uIcLvzT\/\/\/\/J\/+wls3gBfAPwh"],"wpil_links_outbound_external_count":["0"],"wpil_links_outbound_external_count_data":["eJxLtDKwqq4FAAZPAf4="],"wpil_sync_report2_time":["2024-08-27T11:47:29+00:00"],"copied_media_ids":["a:0:{}"],"referenced_media_ids":["a:0:{}"],"_uag_css_file_name":["uag-css-202.css"]},"uagb_featured_image_src":{"full":false,"thumbnail":false,"medium":false,"medium_large":false,"large":false,"1536x1536":false,"2048x2048":false},"uagb_author_info":{"display_name":"Johannes Ahrends","author_link":"https:\/\/carajandb.com\/en\/author\/9aa6cdb2095bd409\/"},"uagb_comment_info":13,"uagb_excerpt":"With Oracle 12c it is all getting better! And that includes the new auditing possibilities as well, which can be grouped as \u201cUnified Auditing\u201d. First of all unified auditing basically does not mean anything more but the previous functions, standard auditing and fine-grained auditing, being combined. This means that we are dealing with three (!)…","_links":{"self":[{"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/posts\/202","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/comments?post=202"}],"version-history":[{"count":1,"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/posts\/202\/revisions"}],"predecessor-version":[{"id":7108,"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/posts\/202\/revisions\/7108"}],"wp:attachment":[{"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/media?parent=202"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/categories?post=202"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/tags?post=202"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}