Manage Audit Data

Over the last couple of years I was asked repeatedly because the SYSTEM Tablespace is constantly growing. The reason is pretty simple: Since Oracle 11.2 DDL commands not executed with SYSDBA privilege and all logins (failed or successful) are logged in the AUD$ table. And against some documentation this table is located in the SYSTEM tablespace and not in SYSAUX.

Changing the Oracle parameter AUDIT_TRAIL=NONE will turn off this auditing but firstly this isn’t a dynamic parameter but the instances have to be bounced and secondly it might be quite useful to audit those actions.

Audit Tablespace

According to the application(s) the AUD$ table can grow rapidly so it should be moved into its own tablespace. Because you shouldn’t move data dictionary table directly (MOVE TABLESPACE) Oracle provides a package (dbms_audit_mgmt) to manage the AUD$ and FGA_LOG$ tables.

CREATE TABLESPACE AUDITTS
   DATAFILE '/u02/oradata/HANNES/auditts01.dbf' size 100M
   AUTOEXTEND ON NEXT 100M MAXSIZE 5000M; 
 -- 
 -- Initializing of the Audit Management 
 -- 
BEGIN
    dbms_audit_mgmt.init_cleanup(     
	AUDIT_TRAIL_TYPE => dbms_audit_mgmt.audit_trail_aud_std,     
	DEFAULT_CLEANUP_INTERVAL => 1 /*hours*/  
	); 
END; 
/

CAUTION! This procedure will move the AUD$ table to the SYSAUX tablespace first. So in the above case it will move 20 GB of data from SYSTEM to SYSAUX. That will make things even worse! So before executing this procedure you should delete trail records or better truncate the AUD$ table. As a best practice I would recommend that you execute this package right after the database creation for both standard auditing as well as fine grained auditing.

Now we can move the AUD$ table into its own tablespace AUDITTS. Again the package dbms_audit_mgmt will provide the right procedure:

BEGIN    
   dbms_audit_mgmt.set_audit_trail_location(     
     audit_trail_type           => dbms_audit_mgmt.audit_trail_aud_std,     
	 udit_trail_location_value  => 'AUDITTS'
   ); 
END; 
/

Now that the table has been moved our problem should be resolved. But I would recommend that you think about how to maintain the trail records. The dbms_audit_mgmt has again the right procedure (create_purge_job) but the whole process is a bit more complex.

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     => 'Audit_Purge_Job',     
	  USE_LAST_ARCH_TIMESTAMP    => TRUE   
   ); 
END; 
/

A new scheduler job named “Audit Purge Job” will be created and executed once a day. The purpose of this job is to look for trail records that have been archived (USE_LAST_ARCH_TIMESTAMP). If the parameter has been set to “FALSE” all trail records will be removed.

Unfortunately this procedure does not work as expected. With the first execution via the Oracle scheduler an error message will be returned:

ORA-12012: error on auto execute of job "SYS"."AUDIT_PURGE_JOB" ORA-46258: Cleanup not initialized for the audit trail

With the execution of this command via the scheduler the package has to be initialized again. You need to specify everything using the dbms_scheduler package or you can edit the procedure with Toad:

The next question is: how can we set the “last_arch_timestamp”? Oracle supposes that audit trail records are moved to a separate database from time to time to make them available for reporting. After the replication of those data the records will be marked “archived” and can be purged. For our “simple” auditing we need a second job running once a day and mark records “archived” which are older than 30 days.

BEGIN   
   dbms_scheduler.create_job (     
      job_name   => 'Audit_Archive_Timestamp',     
	  job_type   => 'PLSQL_BLOCK',     
	  job_action => 'BEGIN                          
	     dbms_audit_mgmt.init_cleanup(                             
		    AUDIT_TRAIL_TYPE         => dbms_audit_mgmt.audit_trail_aud_std,                             
			DEFAULT_CLEANUP_INTERVAL => 1);                          
	     dbms_audit_mgmt.set_last_archive_timestamp(                             
			AUDIT_TRAIL_TYPE         => dbms_audit_mgmt.audit_trail_aud_std,                             
			LAST_ARCHIVE_TIME        => sysdate-31);                           
		 dbms_audit_mgmt.deinit_cleanup(                            
			AUDIT_TRAIL_TYPE => dbms_audit_mgmt.audit_trail_aud_std);                      
      END;',      
	  start_date      => to_date('2015-01-01 00:30:00','YYYY-MM-DD HH24:MI:SS'),      
	  repeat_interval => 'FREQ=HOURLY;INTERVAL=24',      
	  enabled         =>  TRUE,     
	  comments        => 'Create Archive timestamp'   
   ); 
END; 
/

This job calls the procedure set_last_archive_timestamp and marks trail records as archived. Like with the purge job it is again necessary to initialize the process first.

Now the management of audit trail has been finished and we can deinitialize the management:

BEGIN    
   dbms_audit_mgmt.deinit_cleanup(       
      AUDIT_TRAIL_TYPE => dbms_audit_mgmt.audit_trail_aud_std    
   ); 
END; 
/

Crosscheck

The following views might help to check if auditing is enabled and if the procedures or jobs are executed correctly.

SELECT audit_option, success, failure 
  FROM dba_stmt_audit_opts; 

AUDIT_OPTION                             SUCCESS    FAILURE 
---------------------------------------- ---------- --------- 
CREATE EXTERNAL JOB                      BY ACCESS  BY ACCESS 
CREATE ANY JOB                           BY ACCESS  BY ACCESS 
GRANT ANY OBJECT PRIVILEGE               BY ACCESS  BY ACCESS 
EXEMPT ACCESS POLICY                     BY ACCESS  BY ACCESS 
CREATE ANY LIBRARY                       BY ACCESS  BY ACCESS 
GRANT ANY PRIVILEGE                      BY ACCESS  BY ACCESS 
DROP PROFILE                             BY ACCESS  BY ACCESS 
ALTER PROFILE                            BY ACCESS  BY ACCESS 
DROP ANY PROCEDURE                       BY ACCESS  BY ACCESS 
ALTER ANY PROCEDURE                      BY ACCESS  BY ACCESS 
CREATE ANY PROCEDURE                     BY ACCESS  BY ACCESS 
ALTER DATABASE                           BY ACCESS  BY ACCESS 
SYSTEM GRANT                             BY ACCESS  BY ACCESS 
GRANT ANY ROLE                           BY ACCESS  BY ACCESS 
CREATE PUBLIC DATABASE LINK              BY ACCESS  BY ACCESS 
DROP ANY TABLE                           BY ACCESS  BY ACCESS 
ALTER ANY TABLE                          BY ACCESS  BY ACCESS 
CREATE ANY TABLE                         BY ACCESS  BY ACCESS 
PROFILE                                  BY ACCESS  BY ACCESS 
ROLE                                     BY ACCESS  BY ACCESS 
DATABASE LINK                            BY ACCESS  BY ACCESS 
PUBLIC SYNONYM                           BY ACCESS  BY ACCESS 
DROP USER                                BY ACCESS  BY ACCESS 
ALTER USER                               BY ACCESS  BY ACCESS 
CREATE USER                              BY ACCESS  BY ACCESS 
CREATE SESSION                           BY ACCESS  BY ACCESS 
SYSTEM AUDIT                             BY ACCESS  BY ACCESS 
ALTER SYSTEM                             BY ACCESS  BY ACCESS 

col JOB_NAME format a30 
col JOB_FREQUENCY format a40 
SELECT job_name,job_status,audit_trail,job_frequency 
  FROM dba_audit_mgmt_cleanup_jobs; 
  
 JOB_NAME                       JOB_STAT AUDIT_TRAIL                  JOB_FREQUENCY 
 ------------------------------ -------- ---------------------------- ----------------------- 
 DAILY_AUDIT_PURGE_JOB          ENABLED  STANDARD AUDIT TRAIL         FREQ=HOURLY;INTERVAL=24 
 
 SELECT job_name, next_run_date, state, enabled 
   FROM dba_scheduler_jobs 
  WHERE job_name LIKE '%AUDIT%'; 
  
JOB_NAME                       NEXT_RUN_DATE                       STATE           ENABL 
------------------------------ ----------------------------------- --------------- ----- 
AUDIT_ARCHIVE_TIMESTAMP        08.01.15 00:30:00,000000 +01:00     SCHEDULED       TRUE 
DAILY_AUDIT_PURGE_JOB          08.01.15 09:16:30,800000 +01:00     SCHEDULED       TRUE

But you might want to look for the audit trail information. The following example lists all logons to the database. The return code shows that some of the logins were not successful.

 
SELECT os_username, username, userhost, extended_timestamp, action, action_name, returncode as return 
  FROM dba_audit_trail 
 WHERE action_name = 'LOGON' 
 ORDER BY TIMESTAMP desc;  
 
OS_USERNAME       USERNAME USERHOST               EXTENDED_TIMESTAMP               ACTION ACTION_NAM  RETURN 
----------------- -------- ---------------------- -------------------------------- ------ ---------- ------- 
Johannes Ahrends  SYSTEM   CARAJANDB\CDB-JA       08.01.15 12:11:58,150458 +01:00     100 LOGON            0 
oracle            SYSTEM   mozart.carajandb.intra 08.01.15 12:11:37,148850 +01:00     100 LOGON            0 
oracle            SYSTEM   mozart.carajandb.intra 08.01.15 12:11:32,535973 +01:00     100 LOGON         1017 
oracle            SYSTEM   mozart.carajandb.intra 08.01.15 12:10:16,180199 +01:00     100 LOGON         1017 
oracle            SYSMAN   mozart.carajandb.intra 08.01.15 11:18:33,723828 +01:00     100 LOGON            0 
oracle            SYSMAN   mozart.carajandb.intra 08.01.15 11:18:31,224429 +01:00     100 LOGON        28000 
oracle            SYSMAN   mozart.carajandb.intra 08.01.15 11:18:31,736211 +01:00     100 LOGON            0 
oracle            SYSMAN   mozart.carajandb.intra 08.01.15 11:18:31,430337 +01:00     100 LOGON            0 
oracle            DBSNMP   mozart.carajandb.intra 08.01.15 11:18:31,497521 +01:00     100 LOGON            0 
oracle            SYSMAN   mozart.carajandb.intra 08.01.15 11:18:31,588672 +01:00     100 LOGON            0 
oracle            SYSMAN   mozart.carajandb.intra 08.01.15 11:18:31,712832 +01:00     100 LOGON            0 
oracle            DBSNMP   mozart.carajandb.intra 08.01.15 11:18:10,574229 +01:00     100 LOGON            0 
oracle            DBSNMP   mozart.carajandb.intra 08.01.15 11:18:05,167148 +01:00     100 LOGON        28000 
oracle            SYSTEM   mozart.carajandb.intra 08.01.15 11:03:49,565045 +01:00     100 LOGON            0 
oracle            SYSTEM   mozart.carajandb.intra 08.01.15 11:03:49,491996 +01:00     100 LOGON            0

Conclusion

Latest your SYSTEM tablespace grew more than 2 GB I would suggest to have a look at the space usage of the AUD$ or FGA_LOG$ table and to move those tables into a separate tablespace (with FGA_LOG$ the AUDIT_TRAIL_TYPE = dbms_audit_mgmt.audit_trail_fga_std). The problem is that you are probably not able to shrink the SYSTEM (or SYSAUX) tablespace because other objects might have created extents at the end of the datafile.

Currently I’m working on the new unified auditing in Oracle 12c. So I guess one of my next blogs will be about those new features.