Differences

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

--- databaseauditing [2015/07/08 15:23] – created z0hpvk
+++ databaseauditing [2025/03/08 22:24] (current) – external edit 127.0.0.1
@@ Line 1: / Line 1: @@
-==== Database Auditing ====
+===== Database Auditing =====
+==== Disabling Create Session Auditing ====
-By default when a database is created using DBCA, the database auditing parameter will be turned on.
+By default, from 11gR1, when a database is created using DBCA, the database auditing parameter will be turned on.
 <code>SQL> show parameter audit_trail
@@ Line 8: / Line 9: @@
 ------------------------------------ ----------- ------------------------------
 audit_trail                          string      DB</code>
-This means whenever a connection is made to the database, an entry is logged in the Audit Trail.
+This means whenever a connection is made to the database, an entry is logged in the Audit Trail.\\
 Therefore the table sys.aud$ can grow very quickly and on web applications will affect performance
@@ Line 16: / Line 17: @@
 Noaudit succeeded.</code>
 This still means that failed logins are audited.
+==== 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 a15
+col userhost format a45
+select os_username, username, returncode, userhost, timestamp
+from dba_audit_trail
+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>