by

Limit Audit Trail Size

Most DBAs are familiar with auditing. They may also be familiar with the problems that arise when the SYSTEM tablespace fills up because the audit tables get large; however, this problem can be eluded.

Run the following query to discover where the current AUD$ and FGA_LOG$ tables reside:

1
2
3
4
5
6
7
8
9
10
11
12

COLUMN TABLE_NAME format a30
COLUMN tablespace_name format a30
SET tab off
SELECT  TABLE_NAME
     ,  tablespace_name
  FROM  dba_tables
 WHERE  TABLE_NAME IN ( 'AUD$', 'FGA_LOG$' );
 
TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
FGA_LOG$                       SYSTEM
AUD$                           SYSTEM

See that in this instance that both tables belong to the SYSTEM tablespace. We don’t want it there, especially if we want to be proactive in our auditing. Instead, let’s make a new tablespace just for these two tables.
Type the following (copy/paste) into a SQL*Plus client:

1
2
3
4
5
6
7
8

CREATE smallfile tablespace secad
    datafile  '/u02/oradata/T001/secad-001.dbf'
        SIZE  5g
  autoextend  ON
     maxsize  30g
logging extent management LOCAL
segment SPACE management auto
DEFAULT compress FOR oltp;

Now, let’s move the tables to our new SECAD tablespace. Make sure you use the DBMS_AUDIT_MGMT package when you do this.
Type the following (copy/paste) into a SQL*Plus client.
Re-query the dba_tables view to see where the tables are located.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

BEGIN
  -- this one for your aud$
  dbms_audit_mgmt.set_audit_trail_location
  (
    audit_trail_type            => dbms_audit_mgmt.audit_trail_aud_std
  , audit_trail_location_value  => 'SECAD'
  );
  -- this one for your fga_log$
  dbms_audit_mgmt.set_audit_trail_location
  (
    audit_trail_type            => dbms_audit_mgmt.audit_trail_fga_std
  , audit_trail_location_value  => 'SECAD'
  );
END;
/
SELECT  TABLE_NAME
     ,  tablespace_name
  FROM  dba_tables
WHERE  TABLE_NAME IN ( 'AUD$', 'FGA_LOG$' );
 
TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
FGA_LOG$                       SECAD
AUD$                           SECAD

Now that we have the audit tables in a space that will not negatively affect the database, let’s talk about the automatic limits that can be added to these tables. Again, we are using the DBMS_AUDIT_MGMT package to perform most of our work.
Type the following (copy/paste) into a SQL*Plus client.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

COLUMN parameter_name format a30
COLUMN parameter_value format a30
COLUMN audit_trail format a30
SET linesize 100
SELECT  parameter_name
     ,  parameter_value
     ,  audit_trail
  FROM  dba_audit_mgmt_config_params
WHERE  parameter_name LIKE '%AUDIT%MAX%';
 
PARAMETER_NAME                 PARAMETER_VALUE                AUDIT_TRAIL
------------------------------ ------------------------------ ------------------------------
AUDIT FILE MAX SIZE            10000                          OS AUDIT TRAIL
AUDIT FILE MAX SIZE            10000                          XML AUDIT TRAIL
AUDIT FILE MAX AGE             5                              OS AUDIT TRAIL
AUDIT FILE MAX AGE             5                              XML AUDIT TRAIL

The following clarifications should be made before we move on:
1. The sizes are in KBytes
2. The age is in days
Let’s modify these limits for the OS AUDIT trail. Type (copy/paste) the following into a SQL*Plus session

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

BEGIN
  dbms_audit_mgmt.set_audit_trail_property
    (
      audit_trail_type            => dbms_audit_mgmt.audit_trail_os
    , audit_trail_property        => dbms_audit_mgmt.os_file_max_size
    , audit_trail_property_value  => 15360 --> 15 MB
    );
 
  dbms_audit_mgmt.set_audit_trail_property
    (
      audit_trail_type            => dbms_audit_mgmt.audit_trail_xml
    , audit_trail_property        => dbms_audit_mgmt.os_file_max_age
    , audit_trail_property_value  => 30
    );
END;
/

We need to generate some audit information in the FGA_LOG$ table. To do so, let’s make a quick policy that will audit all activity to the hr.employees table. It is important to note that when you leave the audit condition out of the policy, you are basically saying where 1=1. Another important note is that your audit policy functions need to evaluate to true or false. You can do any number of evaluations, effectively filtering out unwanted noise. However, for now we will merely audit everything.

1
2
3
4
5
6
7
8
9

BEGIN
  dbms_fga.add_policy
  (
    object_schema   => 'HR'
  , object_name     => 'EMPLOYEES'
  , policy_name     => 'somePolicy'
  );
END;
/

Now we’ll issue a query against HR.EMPLOYEES to ensure that information is in the FGA_LOG$ table.

1

SELECT * FROM hr.employees WHERE salary > :1;

Perform several DML queries agains the HR.EMPLOYEES table.

  1. Do a select with and without bind variables.
  2. Do an update of the salary column to 20% more than the current amount for employee 105.
  3. Delete a single row.
  4. Query the SYS.FGA_LOG$ table to see what the results of your activities does.

Audit Trail Monitoring
Backing up the audit trail is an important task. You could use that information to perform data mining, build security dashboards, or even create a paging system depending on the severity of the policy being triggered.
The following code will create a backup table of everything in the audit tables. This won’t help you if you don’t perform regular purges. We will show you how to purge the audit trail shortly. For now, lets implement the following block:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58

CREATE OR REPLACE PROCEDURE audit_backup ( pi_which    IN varchar2 )
AS
  l_sql varchar2(4000);
  l_now varchar2(30);
BEGIN
  l_now := to_char ( systimestamp, 'DDMONYYYY_HHMISSFF4' );
  IF  dbms_assert.simple_sql_name ( pi_which  ) = pi_which
  THEN
    IF pi_which = 'FGA_LOG$' THEN
      l_sql :=
        ' create table sys.$1_$2                            '||
        ' tablespace secad                                  '||
        ' as                                                '||
        ' select  SESSIONID                                 '||
        '      ,  TIMESTAMP#                                '||
        '      ,  DBUID                                     '||
        '      ,  OSUID                                     '||
        '      ,  OSHST                                     '||
        '      ,  CLIENTID                                  '||
        '      ,  EXTID                                     '||
        '      ,  OBJ$SCHEMA                                '||
        '      ,  OBJ$NAME                                  '||
        '      ,  POLICYNAME                                '||
        '      ,  SCN                                       '||
        '      ,  SQLTEXT                                   '||
        '      ,  LSQLTEXT                                  '||
        '      ,  SQLBIND                                   '||
        '      ,  COMMENT$TEXT                              '||
        '      ,   to_lob ( PLHOL ) as PLHOL                '||
        '      ,  STMT_TYPE                                 '||
        '      ,  NTIMESTAMP#                               '||
        '      ,  PROXY$SID                                 '||
        '      ,  USER$GUID                                 '||
        '      ,  INSTANCE#                                 '||
        '      ,  PROCESS#                                  '||
        '      ,  XID                                       '||
        '      ,  AUDITID                                   '||
        '      ,  STATEMENT                                 '||
        '      ,  ENTRYID                                   '||
        '      ,  DBID                                      '||
        '      ,  LSQLBIND                                  '||
        '      ,  OBJ$EDITION                               '||
        '   from  $1';
    elsif pi_which = 'AUD$' THEN
      l_sql :=
        ' create table sys.$1_$2                            '||
        ' tablespace secad                                  '||
        ' as                                                '||
        ' select  *                                         '||
        '   from  sys.$1';
    END IF;
    l_sql := regexp_replace ( l_sql, '( ){2,}', ' '  );
    l_sql := regexp_replace ( l_sql, '\$1', pi_which );
    l_sql := regexp_replace ( l_sql, '\$2', l_now    );
    EXECUTE immediate l_sql;
  END IF;
END;
/

To purge the audit trail you must do the following. Make sure you initialize the cleanup jobs with the first block and then schedule the second block with dbms_job.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

-- run once
BEGIN
  dbms_audit_mgmt.init_cleanup
  (
    audit_trail_type          => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD
  , default_cleanup_interval  => 999
  );
 
  dbms_audit_mgmt.init_cleanup
  (
    audit_trail_type          => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD
  , default_cleanup_interval  => 999
  );
END;
/

The following block should be pushed into a job. Notice that we backup the AUD$ and FGA_LOG$ tables before purging.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

-- run the following on a schedule
BEGIN
  audit_backup ( 'AUD$' );
  audit_backup ( 'FGA_LOG$' );
 
  dbms_audit_mgmt.set_last_archive_timestamp
  (
    audit_trail_type        => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD
  , last_archive_time       => systimestamp
  );
  dbms_audit_mgmt.set_last_archive_timestamp
  (
     audit_trail_type        => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD
  , last_archive_time       => systimestamp
  );
  dbms_audit_mgmt.clean_audit_trail
  (
    audit_trail_type        => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD
  , use_last_arch_timestamp => TRUE
  );
  dbms_audit_mgmt.clean_audit_trail
  (
    audit_trail_type        => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD
  , use_last_arch_timestamp => TRUE
  );
END;
/

Now you have a way to limit the audit trail size and backup audit items. Hopefully, this will help your database performance and size.