五月 24, 2010

Quick guide on using Oracle Database Audit Trail

Posted in Database, Development tagged , 於 9:16 下午 由 Wing


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

Basic Configuration:

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:
shutdown IMMEDIATE;
startup;

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.

Fine-Grained Auditing:

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:
EXEC DBMS_FGA.add_policy(object_schema=>'username',object_name=>'table_name',policy_name=>'policy_name',audit_condition=>NULL,audit_column=>NULL,statement_types=>'SELECT,INSERT,UPDATE,DELETE');
4. Drop FGA policy:
EXEC DBMS_FGA.DROP_POLICY(object_schema=>'username',object_name=>'table_name',policy_name=>'policy_name');
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.

Using Triggers

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
begin
if (:new.field_name = 'A')
then
insert into tmp_table_audit values (
:tmp_field,
:old.field_name,
:new.field_name,
user,
sysdate ) ;
endif;
end;

3. Retrieve/Purge audit trail record according to the trigger logic.

System logs

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
AUDIT_TRAIL=OS
AUDIT_SYSLOG_LEVEL=facility.level
– Set the log file location to the syslog configure file:
facility.level /var/log/audit.log
– 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.

Reference:

Auditing in XML
http://www.oracle.com/technology/oramag/oracle/06-jan/o16security.html

Configuring and Administering Auditing
http://download.oracle.com/docs/cd/B19306_01/network.102/b14266/cfgaudit.htm#BABCBJHG

DBMS_AUDIT_MGMT PL/SQL Package
http://download.oracle.com/docs/cd/E11062_01/admin.1023/e11059/avadm_app_d_audit_mgmt.htm

Oracle Audit Vault Downloads
http://www.oracle.com/technology/software/products/auditvault/index.html

Oracle Audit Vault Server Installation Guide
http://download.oracle.com/docs/cd/E14472_01/doc.102/e14458/toc.htm

* Please ensure you have applied the latest patch set for Audit Vault
http://updates.oracle.com/download/9087650.html (as of May 2010)

4 則迴響 »

  1. Pretty section of content. I just stumbled upon your web site and in accession capital to assert that I get in fact enjoyed account your blog posts.
    Anyway I will be subscribing to your augment and even I achievement you access consistently quickly.

  2. It’s perfect time to make a few plans for the future and it’s time to be happy.

    I’ve read this put up and if I could I wish to counsel you some attention-grabbing things or advice. Perhaps you could write next articles referring to this article. I want to learn more things approximately it!

  3. Hi there! I simply wish to give a huge thumbs up for the great data you have here on this
    post. I will likely be coming again to your blog for
    extra soon.

  4. My brother recommended I might like this blog. He was totally right.
    This post actually made my day. You cann’t imagine just how much time I had spent for this info! Thanks!


發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

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

Twitter picture

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

Facebook照片

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

Google+ photo

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

連結到 %s

%d 位部落客按了讚: