Oracle … as usual

Oracle by Laurent Leturgez

Managing database audit Trail in Oracle 11gR2

Due to American Security laws, Oracle has changed its audit management rules in Oracle 11g.

Now, AUDIT_TRAIL is configured bydefault to ‘DB’. Many operations are now audited by default and stored in the AUD$ table (which is stored by default in the SYSTEM tablespace).

Audited operations are available in the DBA_PRIV_AUDIT_OPTS view, and if you have a look in it, you will see that “CREATE SESSION” operations are audited.

So, if you create your database with ‘dbca’, if you update your database with ‘dbua’, you leave the default configuration, and your SYSTEM Tablespace datafile is in autoextend mode … you will probably have a huge system datafile soon.

To manage the audit trail, you have different solutions:

– The first one is to disable audit trail: set AUDIT_TRAIL to NONE into your spfile file, shutdown and restart your database.

– The second one is to move your AUD$ to another tablespace :

SQL> select table_name,tablespace_name from dba_tables where table_name='AUD$';

TABLE_NAME      TABLESPACE_NAME
--------------- ---------------
AUD$            SYSTEM
SQL> exec DBMS_AUDIT_MGMT.set_audit_trail_location(audit_trail_type=>DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, audit_trail_location_value => 'AUD_TS');

PL/SQL procedure successfully completed.
SQL> select table_name,tablespace_name from dba_tables where table_name='AUD$';

TABLE_NAME      TABLESPACE_NAME
--------------- ---------------
AUD$            AUD_TS

– The third one is to manage the properties of the audit trail with the DBMS_AUDIT_MGMT PL/SQL package

If you want to change it, you have to use DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY procedure to configure properties of the audit trail:

SQL> begin
  2  DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(audit_trail_type=>DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
  3                                           audit_trail_property=>DBMS_AUDIT_MGMT.DB_DELETE_BATCH_SIZE,
  4                                           audit_trail_property_value=>1000);
  5  end;
  6  /

Then, you have to initialize the cleanup, and to define the oldest record to leave in the table (Here 30 days):

SQL> begin
  2  DBMS_AUDIT_MGMT.INIT_CLEANUP(audit_trail_type=> DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
  3                               default_cleanup_interval=>720);
  4
  5  DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
  6                                             last_archive_time => SYSTIMESTAMP-30);
  7  end;
  8  /

Next, we create a job. This job will be executed every 168 hours (7 days).

SQL> BEGIN
  2  DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(audit_trail_type=> DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
  3                                   audit_trail_purge_interval => 168,
  4                                   audit_trail_purge_name=> 'PURGE_AUD_JOB',
  5                                   use_last_arch_timestamp => TRUE);
  6  END;
  7  /
Note : if USE_LAST_ARCH_TIMESTAMP is set to TRUE, the job will purge only the oldest entries that match to the corresponding retention parameter. You can have a look to the DBA_SCHEDULER_JOBS to have more details about this new job.

Here I gave you an example to purge the DB audit structure (AUD$ and FGA_AUD$ tables), if you have a look to the Oracle documentation, you can use the same procedure to purge OS Audit File, XML File.

Advertisements

One response to “Managing database audit Trail in Oracle 11gR2

  1. Pingback: Top ORCLearnings of the week « Oracle DB 10g & 11g Tops

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: