If in your production database configured audit, DBA should maintain audit tables.
Because audit records may grows up to undesired size.
Because audit records may grows up to undesired size.
One of the most significant aspects of database security involves setting up auditing to record user activities.
When auditing is enabled, the audit output is recorded in an audit trail, which is usually stored in the database in a table under the SYS schema called AUD$. It can also reside as files in the file system, and the files can optionally be stored in XML format. For more-precise control, the Fine Grained Auditing feature of Oracle Database 11g provides granular control of what to audit, based on a more detailed set of policies. Fine Grained Auditing audits are usually stored in another table, FGA_LOG$, under the SYS schema.
These various audit trails can quickly grow out of control when database activity increases. As audit trails grow, two main challenges must be addressed:
- Trails need to be kept to a manageable size (and old records purged) if they are to be used effectively in forensic analysis.
- Because database-resident trails are typically stored in the SYSTEM tablespace, they can potentially fill it up—bringing the database to a halt.
Fortunately, the new auditing features in Oracle Database 11g Release 2 can help address these challenges. These capabilities, implemented in a package called DBMS_AUDIT_MGMT, enable you to move audit trails from the SYSTEM tablespace to one of your choice.
The new auditing features also let you set up one-time and automated purge processes for each of your audit trail types. Historically, to purge an audit trail, you were generally forced to stop auditing (which may have required bouncing the database), truncate, and then restart auditing (and bouncing the database again).
In this article, you will learn how to use the new features in Oracle Database 11g Release 2 to manage your audit trails.
Oracle 11g Release 1 turned on auditng by default for the first time. Oracle 11g Release 2 now allows better management of the audit trail using theDBMS_AUDIT_MGMT package.
The
AUDIT_TRAIL_TYPE parameter is specified using one of three constants.DBMS_AUDIT_MGMT.AUDIT_TRAIL_: Standard audit trail (AUD$).AUD_STD DBMS_AUDIT_MGMT.AUDIT_TRAIL_: Fine-grained audit trail (FGA_LOG$).FGA_STD DBMS_AUDIT_MGMT.AUDIT_TRAIL_: Both standard and fine-grained audit trails.DB_STD
If you want the purge job to maintain an audit trail of a specific number of days, the easiest way to accomplish this is to define a job to set the last archive time automatically. The following job resets the last archive time on a daily basis, keeping the last archive time 90 days in the past.
Automated Purge with retention of 90 days.
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'audit_last_archive_time',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN
DBMS_AUDIT_MGMT.SET_LAST_
Manual Purging only aud$ table with retention of 120 days.
col owner for a20 select LAST_ANALYZED,owner, table_name,num_rows from dba_tables where table_name='AUD$'; LAST_ANALYZED OWNER TABLE_NAME NUM_ROWS --------------- -------------------- ------------------------- --------------- 14-OCT-07 SYS AUD$ 0 20:03:01 [SYS]>> exec dbms_stats.gather_table_stats (ownname=>'SYS', tabname=>'AUD$' , estimate_percent=>10, cascade=>TRUE, degree=>5); PL/SQL procedure successfully completed. Elapsed: 00:00:19.65 20:03:31 [SYS]>> select LAST_ANALYZED,owner, table_name,num_rows from dba_tables where table_name='AUD$'; LAST_ANALYZED OWNER TABLE_NAME NUM_ROWS --------------- ---------- ------------------------- --------------- 24-MAY-13 SYS AUD$ 1982500 00:36:00 [SYS]>> select max(ntimestamp#) from sys.aud$ where trunc(NTIMESTAMP# ) < trunc(sysdate -1000); MAX(NTIMESTAMP#) --------------------------------------------------------------------------- 28-AUG-10 11.58.44.054379 PM 02:45:16 [SYS]>> select max(ntimestamp#) from sys.aud$ where trunc(NTIMESTAMP# ) < trunc(sysdate -500); MAX(NTIMESTAMP#) --------------------------------------------------------------------------- 10-JAN-12 11.56.07.081514 PM Elapsed: 00:00:01.51 02:46:13 [SYS]>> select max(ntimestamp#) from sys.aud$ where trunc(NTIMESTAMP# ) < trunc(sysdate -250); MAX(NTIMESTAMP#) --------------------------------------------------------------------------- 16-SEP-12 11.58.46.296859 PM Elapsed: 00:00:01.55 02:59:00 [SYS]>> select max(ntimestamp#) from sys.aud$ where trunc(NTIMESTAMP# ) < trunc(sysdate -120); MAX(NTIMESTAMP#) --------------------------------------------------------------------------- 24-JAN-13 11.59.17.319628 PM 03:04:53 [SYS]>> delete from sys.aud$ where trunc(NTIMESTAMP# ) < trunc(sysdate -1000); 795713 rows deleted. Elapsed: 00:01:02.47 03:06:08 [SYS]>> commit; Commit complete. Elapsed: 00:00:00.02 03:06:38 [SYS]>> delete from sys.aud$ where trunc(NTIMESTAMP# ) < trunc(sysdate -500); 660309 rows deleted. Elapsed: 00:00:56.64 03:07:58 [SYS]>> commit; Commit complete. Elapsed: 00:00:00.01 03:08:08 [SYS]>> delete from sys.aud$ where trunc(NTIMESTAMP# ) < trunc(sysdate -120); 396701 rows deleted. Elapsed: 00:00:46.57 03:09:07 [SYS]>> commit; Commit complete. Rebuilding Indexes ---------------------- 03:25:23 [SYS]>> select owner,index_name,index_type,LAST_ANALYZED from dba_indexes where table_name='AUD$' and owner='SYS'; OWNER INDEX_NAME INDEX_TYPE LAST_ANALYZED ------------ ---------------------------------------- ------------ --------------- SYS SYS_IL0000000375C00040$$ LOB SYS SYS_IL0000000375C00041$$ LOB SYS I_AUD1 NORMAL 25-MAY-13 -->If any Indexes listed above needs to rebuild indexes.
03:27:50 [SYS]>> alter index SYS.I_AUD1 rebuild online; Index altered. Elapsed: 00:00:05.39 03:35:59 [SYS]>> exec dbms_stats.gather_table_stats (ownname=>'SYS', tabname=>'AUD$' , estimate_percent=>10, cascade=>TRUE, degree=>5); PL/SQL procedure successfully completed. 03:37:31 [SYS]>> select LAST_ANALYZED,owner, table_name,num_rows from dba_tables where table_name='AUD$'; LAST_ANALYZED OWNER TABLE_NAME NUM_ROWS --------------- ------------ -------------------- --------------- 25-MAY-13 SYS AUD$ 123510
