五月 24, 2010
Quick guide on using Oracle Database Audit Trail
This post just to provide a quick guide on using Oracle 10g Database Audit Trail~
In this post:
1. Basic Configuration
2. Standard Audit Trail
3. Fine-Grained Auditing
4. Using Triggers
5. System logs
6. Oracle Audit Vault
1. To configure audit trail option, you have to connect to an instance as administrator privilege user, e.g. sysdba, sysoper
conn sys as sysdba
2. Check current audit parameters with command:
show parameter audit
3. Define the audit log file location (if you choose to keep the audit log record in physical file):
ALTER SYSTEM SET AUDIT_FILE_DEST = '/oracle/audit_log' DEFERRED;
4. Be reminded to restart database instance after applied the configuration:
5. Normally, the audit log records is growing fast that remember to archive the records, following is some methods for reference:
– perform table copy from the audit log table/view to new table and then perform deletion.
Here is the example of archive SYS.AUD$ into new table:
CREATE TABLE TMP_AUD AS SELECT * FROM SYS.AUD$;
TRUNCATE table sys.aud$;
– perform table export to file by using command
exp and then perform deletion.
– move the existing audit log into a new tablespace.
6. Three main views for auditing records are:
– DBA_AUDIT_TRAIL (AUD$): Standard auditing records.
– DBA_FGA_AUDIT_TRAIL (FGA_LOG$): Fine-grained auditing records.
– DBA_COMMON_AUDIT_TRAIL: For both standard and fine-grained auditing records.
Standard Audit Trail:
1. The options of Audit Trail record storage methods are: OS, DB, DB + Extended, XML, XML + Extended, and NONE.
2. You can apply following script to set the audit trail method, e.g. I am using XML that also capture the SQL text that user applied:
ALTER SYSTEM SET audit_trail=XML,EXTENDED SCOPE=SPFILE;
3. To define what kind of audit information required to capture for the particular user:
For DDL operations:
AUDIT ALL BY <username> BY ACCESS;
For DML operations:
AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE BY <username> BY ACCESS;
4. To disable audit trail:
NOAUDIT ALL BY ;
NOAUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE BY ;
5. If you choose to use DB to capture the audit trail record:
– Retrieve audit records by selecting the table SYS.AUD$
– Purge audit trail record by delete/truncate records in SYS.AUD$.
6. If you choose to use OS or XML to capture the audit trail record:
– Retrieve audit records by selecting the view DBA_COMMON_AUDIT_TRAIL or from the physical files
– Each audit log file will capture all concerned operation within a session of a database user.
– Purge audit trail record by physically delete/move the audit log files. FYI, as the view DBA_COMMON_AUDIT_TRAIL will read from the physical file data, after removed the audit log files, the records in the view will also disappeared.
1. FGA allows you to define your own criteria of for auditing.
2. Check the availability of FGA:
select * from v$option
where parameter like 'Fine-grained%';
3. Add FGA policy:
4. Drop FGA policy:
5. Audit trail records can be retrieved in the table SYS.FGA_LOG$, DBA_COMMON_AUDIT_TRAIL, DBA_FGA_AUDIT_TRAIL
6. To Purge audit trail record, delete records in SYS.FGA_LOG$, DBA_FGA_AUDIT_TRAIL.
1. By using triggers, you can define your own criteria and audit log logging logic, which is more flexible to capture the audit log and even the data.
2. Following is the trigger sample:
CREATE TRIGGER audit_tmp_table
AFTER INSERT OR DELETE OR UPDATE ON tmp_table
for each row
if (:new.field_name = 'A')
insert into tmp_table_audit values (
sysdate ) ;
3. Retrieve/Purge audit trail record according to the trigger logic.
1. syslog is independence with audit log, it mainly used to audit the operation of privileged users, such as SYS, in order to prevent the privileged users to modify the audit log records.
2. To enable the syslog:
– Alter the system parameter
– Set the log file location to the syslog configure file:
– Restart the database instance
Oracle Audit Vault
1. DBMS_AUDIT_MGMT PL/SQL Package is available in Oracle Audit Vault, which provides subprograms to manage audit trail records.
2. Oracle Audit Vault supported from Oracle 10g Release 2 and bundled with Oracle 11i, which means you need to install the Oracle Audit Vault package for 10g. Although it is free to download, it required license to run on production environment.
Configuring and Administering Auditing
DBMS_AUDIT_MGMT PL/SQL Package
Oracle Audit Vault Downloads
Oracle Audit Vault Server Installation Guide
* Please ensure you have applied the latest patch set for Audit Vault
http://updates.oracle.com/download/9087650.html (as of May 2010)