Differences

This shows you the differences between two versions of the page.

--- databaseauditing [2015/07/17 13:12] – z0hpvk
+++ databaseauditing [2025/03/08 22:24] (current) – external edit 127.0.0.1
@@ Line 1: / Line 1: @@
-==== Database Auditing ====
+===== Database Auditing =====
-=== Turn Off Create Session Auditing ===
+==== Disabling Create Session Auditing ====
 By default, from 11gR1, when a database is created using DBCA, the database auditing parameter will be turned on.
@@ Line 19: / Line 19: @@
 This still means that failed logins are audited.
-=== View Audit Trail ===
+==== Viewing Audit Trail ====
+SQL below will show everything that has been audited today
 <code>alter session set nls_date_format = 'DD-MON-YYYY HH24:MI';
 set linesize 160
-col os_username format a30
+col os_username format a15
-col userhost format a25
+col userhost format a45
-select os_username, username, action, userhost, timestamp
+select os_username, username, returncode, userhost, timestamp
 from dba_audit_trail
-where TIMESTAMP > trunc(sysdate)
+where timestamp > trunc(sysdate)
 order by timestamp;</code>
+==== Purging Audit Trail ====
+Copy sys.aud$ table to the SYSAUX tablespace
+<code>BEGIN
+  DBMS_AUDIT_MGMT.INIT_CLEANUP(
+    AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
+    DEFAULT_CLEANUP_INTERVAL => 24
+  );
+END;
+/</code>
+Create a job to set the Archive Timestamp. In this case it will be over 30 days
+<code>BEGIN
+  DBMS_SCHEDULER.CREATE_JOB (
+    job_name   => 'DAILY_AUDIT_ARCHIVE_TIMESTAMP',
+    job_type   => 'PLSQL_BLOCK',
+    job_action => 'BEGIN DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(AUDIT_TRAIL_TYPE =>
+                   DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,LAST_ARCHIVE_TIME => sysdate-30); END;',
+    start_date => sysdate,
+    repeat_interval => 'FREQ=HOURLY;INTERVAL=24',
+    enabled    =>  TRUE,
+    comments   => 'Create an archive timestamp'
+  );
+END;
+/</code>
+Create a purge job which will delete all audit entries older than the Archive Timestamp.
+<code>BEGIN
+  DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(
+    AUDIT_TRAIL_TYPE           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
+    AUDIT_TRAIL_PURGE_INTERVAL => 24 /* hours */,
+    AUDIT_TRAIL_PURGE_NAME     => 'Daily_Audit_Purge_Job',
+    USE_LAST_ARCH_TIMESTAMP    => TRUE
+  );
+END;
+/</code>