===== Database Auditing =====
==== Disabling Create Session Auditing ====

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

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      DB</code>
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

You can turn this off by entering the following as SYS user ... 

<code>SYS> NOAUDIT CREATE SESSION WHENEVER SUCCESSFUL;

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>