Audit

How to Manage Audit Files and Auditing on 11gr2
Oracle Database » How To Guides » How to Manage Audit Files and Auditing on 11gr2
Fold
Table of Contents
Introduction
Switching on the Audit Management Packages.
Step-by-Step
Script
Audit Switch On
Issues with the Audit Management Package.
Introduction

The following document explains how to switch on database auditing and the audit management packages for Oracle 11.2.

Database auditing is used to audit various DDL and DML statements.

Audit management was a new feature introduced into Oracle 11gR1. The initial release was not great and contained a number of bugs. However, in 11gR2 many of the bugs have been fixed and a number of enhancements made.

The purpose of the audit management package is to allow easier management and purging of the aud$ table records and the files located in the audit directory as defined by audit_file_dest initialization parameter.

Management of the aud$ table is required if database auditing has been switched on by setting the initialization parameter audit_trail to a value of db or db, extended.
Management of the audit_file_dest location is required if database auditing has been switched on by setting the initialization parameter audit_trail to a value of os, xml or xml, extended.
Important

Management of the audit_file_dest location is also required even if the initialization parameter audit_trail is set to none. The reason for this is that regardless of the initialization parameter audit_trail value, sys as sysdba connections are recorded in this location.

As such, even if database auditing is not switched on it is worth switching on the audit management packages so that the sysdba audit files are managed appropriately and do not fill up the filesystem they are stored on.

Switching on the Audit Management Packages.

Step-by-Step

1. Initialise audit management.
2. Check the default clean-up interval has been set
3. Set the Last Archive Timestamp value for file based audit files
4. Check the Last Archive Timestamp values are set
5. Set-up the automated purge job.
6. Set-up a job to move the Last Archive Timestamp values forward
7. Check to see if the jobs are scheduled.

Script

--
-- Set-up Audit Management
--

BEGIN
  DBMS_AUDIT_MGMT.init_cleanup(
    audit_trail_type         => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
    default_cleanup_interval => 12 /* hours */);
END;
/

--
-- Check Default Clean Up Interval has been Set and Cleanup is Initialized
--

COLUMN parameter_name FORMAT A30
COLUMN parameter_value FORMAT A20
COLUMN audit_trail FORMAT A20

SELECT * FROM dba_audit_mgmt_config_params WHERE PARAMETER_NAME = 'DEFAULT CLEAN UP INTERVAL'
/

SET SERVEROUTPUT ON
BEGIN
  IF DBMS_AUDIT_MGMT.is_cleanup_initialized(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN
    DBMS_OUTPUT.put_line('YES');
  ELSE
    DBMS_OUTPUT.put_line('NO');
  END IF;
END;
/

pause Check the default clean up interval been set and the cleanup has been initialized.

--
-- Set Last Archive Timestamp Values for OS and XML Files.
--

BEGIN
DBMS_AUDIT_MGMT.set_last_archive_timestamp(
audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
last_archive_time => SYSTIMESTAMP-31);
END;
/

BEGIN
DBMS_AUDIT_MGMT.set_last_archive_timestamp(
audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
last_archive_time => SYSTIMESTAMP-31);
END;
/

BEGIN
DBMS_AUDIT_MGMT.set_last_archive_timestamp(
audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML,
last_archive_time => SYSTIMESTAMP-31);
END;
/

--
-- Check Last Archive Timestamp Values are Set
--

SELECT * FROM dba_audit_mgmt_last_arch_ts
/

pause Check the last archive timestamp values have been set.

--
-- Set-up Automated Purge Job
--

BEGIN
DBMS_AUDIT_MGMT.create_purge_job(
audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
audit_trail_purge_interval => 24 /* hours */,
audit_trail_purge_name     => 'PURGE_ALL_AUDIT_TRAILS',
use_last_arch_timestamp    => TRUE);
END;
/

--
-- Set-up a Job to Move the Last Archive Timestamp Forward Each Day
--

-- Unhash if rerunning code.
--
-- BEGIN
--   SYS.DBMS_SCHEDULER.DROP_JOB
--     (job_name  => 'SYS.MOVE_LAST_TIMESTAMP_FORWARD');
-- END;
-- /

BEGIN
  SYS.DBMS_SCHEDULER.CREATE_JOB
    (
       job_name        => 'SYS.MOVE_LAST_TIMESTAMP_FORWARD'
      ,start_date      => TO_TIMESTAMP_TZ('2012/04/13 08:00:00.000000 +01:00','yyyy/mm/dd hh24:mi:ss.ff tzr')
      ,repeat_interval => 'FREQ=DAILY;INTERVAL=1'
      ,end_date        => NULL
      ,job_class       => 'DEFAULT_JOB_CLASS'
      ,job_type        => 'PLSQL_BLOCK'
      ,job_action      => 'BEGIN
  DBMS_AUDIT_MGMT.set_last_archive_timestamp(
    audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
    last_archive_time => SYSTIMESTAMP-31);
END;
BEGIN
  DBMS_AUDIT_MGMT.set_last_archive_timestamp(
   audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
   last_archive_time => SYSTIMESTAMP-31);
END;
BEGIN
  DBMS_AUDIT_MGMT.set_last_archive_timestamp(
    audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML,
    last_archive_time => SYSTIMESTAMP-31);
END;'
      ,comments        => NULL
    );
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'SYS.MOVE_LAST_TIMESTAMP_FORWARD'
     ,attribute => 'RESTARTABLE'
     ,value     => FALSE);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'SYS.MOVE_LAST_TIMESTAMP_FORWARD'
     ,attribute => 'LOGGING_LEVEL'
     ,value     => SYS.DBMS_SCHEDULER.LOGGING_OFF);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    ( name      => 'SYS.MOVE_LAST_TIMESTAMP_FORWARD'
     ,attribute => 'MAX_FAILURES');
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    ( name      => 'SYS.MOVE_LAST_TIMESTAMP_FORWARD'
     ,attribute => 'MAX_RUNS');
  BEGIN
    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
      ( name      => 'SYS.MOVE_LAST_TIMESTAMP_FORWARD'
       ,attribute => 'STOP_ON_WINDOW_CLOSE'
       ,value     => FALSE);
  EXCEPTION
    -- could fail if program is of type EXECUTABLE...
    WHEN OTHERS THEN
      NULL;
  END;
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'SYS.MOVE_LAST_TIMESTAMP_FORWARD'
     ,attribute => 'JOB_PRIORITY'
     ,value     => 3);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    ( name      => 'SYS.MOVE_LAST_TIMESTAMP_FORWARD'
     ,attribute => 'SCHEDULE_LIMIT');
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'SYS.MOVE_LAST_TIMESTAMP_FORWARD'
     ,attribute => 'AUTO_DROP'
     ,value     => TRUE);

  SYS.DBMS_SCHEDULER.ENABLE
    (name                  => 'SYS.MOVE_LAST_TIMESTAMP_FORWARD');
END;
/

--
-- Check the DBMS Scheduler Jobs are Configured.
--

SELECT owner,job_name FROM DBA_SCHEDULER_JOBS WHERE job_name IN ('PURGE_ALL_AUDIT_TRAILS','MOVE_LAST_TIMESTAMP_FORWARD')
/

pause Check the jobs are scheduled.
Audit Switch On

Having set up the audit maintenance packages, auditing needs to be switched on. This can be achieved by setting the audit_trail initialization parameter to a relevant value as discussed in the Introduction. For example,

ALTER SYSTEM SET audit_trail=db
This will ensure all audit records apart from connections as sysdba get logged to aud$. Sys as sysdba operations will still write a file to the audit destination location. More importantly, all sysdba connections will now start writing to the audit destination. At the time of writing it is not clear if this is by design or a bug as it would seem logical that if sys as sysdba connections were going to be written to the audit destination before switching on auditing then ANY sysdba connection should be also. It seems odd to that only once auditing is switched on that ALL sysdba connections are logged.

Once auditing has been switched on, a number of statements will automatically be audited. A list of these statements can be found by running the following SQL statement.

SELECT * FROM dba_stmt_audit_opts ORDER BY user_name,audit_option;
Create session is one of these operations and therefore when auditing is switched on all logons and logoffs are audited which may not be appropriate for many sites. This can be turned off using the following command.

NOAUDIT create session
It is strongly recommended checking other audited statements to see if they are appropriate for your site.

Issues with the Audit Management Package.

The audit management package is still maturing. As such there are a number of issues and oddities that seem to be in conflict with the documentation, represent bugs or require more development on the part of Oracle. The list below represents some of the issues that are apparent with Oracle 11gR2.

It is not clear what the default_cleanup_interval is used for. The documentation states The default time interval, in hours, after which the cleanup procedure should be called. The minimum value is 1 and the maximum is 999. However, when trying out these packages, it did not seem to purge any logs or records, nor did it seem to trigger any event. Furthermore, the fact that the documentation details how to schedule a purge job to perform log and record purging via the jobs scheduler it seems to suggest that this parameter is redundant or currently not implemented.
Although, there is a procedure to set the last timestamp, there does not seem to be any job to automatically move the timestamp forward. This job has to be coded separately. This must be an omission by Oracle given there is a package to schedule the purge.. Perhaps this functionality will appear in later versions.
Despite what is detailed in the documentation, the parameter AUDIT_TRAIL_TYPE in the procedure SET_LAST_ARCHIVE_TIMESTAMP cannot be set to AUDIT_TRAIL_FILES or AUDIT_TRAIL_ALL. Instead, each AUDIT_TRAIL_TYPE has to be set individually. Its not clear if this is a problem with the documentation or a bug.