|
Audit Trail
|
In this issue, I will talk about fine-grained auditing (FGA) that has been newly introduced
in Oracle9i.
Fine-grained auditing is capable of auditing objects, privileges, objects accessed,
types of the SQL statements and so on, which enables more efficient auditing.
You can specify the following categories:
(1) Relevant column
In FGA, you can configure system to audit accesses made to specified columns.
(2) Condition
In FGA, you can specify auditing condition by using WHERE clause in SQL statement.
(3) SYS.DBA_FGA_AUDIT_TRAIL view
DBA_FGA_AUDIT_TRAIL records the fine-grained access information.
(4) Event handler
In addition to DBA_FGA_AUDIT_TRAIL, you can define the event handler to process the event.
DBMS_FGA package enables you to configure audit policy. With this policy, you can
specify data access condition that triggers audit event.
ADD_POLICY procedure: Creates an audit policy using the supplied predicate as the audit condition.
DROP_PLICY procedure: Drops an audit policy
ENABLE_POLICY procedure: Enables an audit policy
DISABLE_POLICY procedure: Disables an audit policy
Fine-grained auditing supports CBO query only. If you perform RBO query,
unnecessary audit records will be created.
Audit is enabled independently of the initialization parameter AUDIT_TRAIL.
This time, I define audit policy to audit if SELECT statement is issued to C_ID column
in CUSTOMER table owned by ORABM user. Moreover, I define a procedure to output
detailed information as event handler.
[Audit Policy]
Specify C/_ID=30 as an audit condition defined on ORABM.CUSTOMER table to generate audit record
and output detailed information to audit file.
[Event Handler]
Output following information to an audit file aud_obj.aud.
date created
DB user name to be audited
OS user name to be audited
Host name issuing SQL statement
IP address of a host issuing SQL statement
Executed SQL statement
FGA policy name
Schema name to be audited
Object name to be audited
Set utl_file_dir to appropriate value.
Now, I'll start defining audit policy.
**************************************************************************
SQL> analyze table customer compute statistics;
Table analyzed
1. Create event handler
^^^^^^^^^^^^^^^^^^^^^^^
SQL> create or replace procedure aud_obj_proc( <-(1)
2 schema_name varchar2,
3 table_name varchar2,
4 policy_name varchar2)
5 is
6 file_id utl_file.file_type;
7 sdate varchar2(19) := to_char(sysdate,'yyyy/mm/dd hh24:mi:ss');
8 aud_file_path varchar2(64) := '/home/oracle/OraHome1/rdbms/audit';
9 file_name varchar2(20) := 'audit_obj.aud';
10 begin
12 file_id := utl_file.fopen(aud_file_path,file_name,'A');
17
18 utl_file.put_line(file_id,'');
18 utl_file.put_line(file_id,sdate);
19 utl_file.put_line(file_id,'ACTION : SELECT');
20 utl_file.put_line(file_id,'DB USER: '||SYS_CONTEXT('USERENV','SESSION_USER'));
21 utl_file.put_line(file_id,'OS USER: '||SYS_CONTEXT('USERENV','OS_USER'));
22 utl_file.put_line(file_id,'HOST : '||SYS_CONTEXT('USERENV','HOST'));
23 utl_file.put_line(file_id,'IPADDR : '||SYS_CONTEXT('USERENV','IP_ADDRESS'));
24 utl_file.put_line(file_id,'SQL_TXT: '||SYS_CONTEXT('USERENV','CURRENT_SQL'));
24 utl_file.put_line(file_id,'POLICY : '||policy_name);
25 utl_file.put_line(file_id,'SCHEMA : '||schema_name);
26 utl_file.put_line(file_id,'OBJECT : '||table_name);
27
28 utl_file.fclose(file_id);
29 end;
30 /
2. Define audit policy
^^^^^^^^^^^^^^^^^^^^^^
SQL> execute dbms_fga.add_policy(- <-(2)
2 object_schema => 'orabm',- --Specify target schema
3 object_name => 'customer',- --Specify target object
4 policy_name => 'aud_obj_policy',- --Specify audit policy
5 audit_condition => 'c_id=30',- --Specify audit condition
6 audit_column => 'c_id',- --Specify an audit column
7 handler_schema => 'orabm',- --Specify a schema of handler procedure
8 handler_module => 'aud_obj_proc',- --Specify handler procedure name
9 enable => true); --Enable audit policy
**************************************************************************
|
Take a look at (1) above. The procedure defined as event handler has three arguments;
schema name of an object, object name, and audit policy name.
Take a look at (2). aud_obj_policy is defined as policy name and aud_obj_proc is set.
I execute some SQL statements.
**************************************************************************
(1).select c_id from customer where c_id = 30;
(2).select c_id from customer where c_id = 1;
(3).select c_data from customer where c_id = 30;
(4).select c_data from customer where c_id = 1;
Select SQL statement that is actually audited from DBA_FGA_AUDIT_TRAIL.
SQL> select to_char(timestamp,'yyyy/mm/dd hh24:mi:ss') timestamp
2 ,db_user
3 ,os_user
4 ,object_schema
5 ,object_name
6 ,policy_name
7 ,sql_text
8 from dba_fga_audit_trail
9 order by timestamp;
TIMESTAMP DB_US OS_US OBJECT_SCH OBJECT_NAM POLICY_NAM SQL_TEXT
-------------------- ----- ----- ---------- ---------- ---------- ----------
2003/09/01 18:09:42 ORABM kshin ORABM CUSTOMER AUD_OBJ_PO select c_i
kub LICY d from cus
tomer wher
e c_id = 3
0
2003/09/01 18:10:02 ORABM kshin ORABM CUSTOMER AUD_OBJ_PO select c_d
kub LICY ata from c
ustomer wh
ere c_id =
30
**************************************************************************
|
SQL statements matching audit_condition are only audited.
In this case, (1) and (3) are audit target.
I check a file created by event handler.
**************************************************************************
$ cat $ORACLE_HOME/rdbms/audit/aud_obj.aud
2003/09/01 18:09:42
ACTION : SELECT
DB USER: ORABM
OS USER: kshinkub
HOST : INSIGHT\KSHINKUB
IPADDR : xxx.xxx.xxx.xxx
SQL_TXT: select c_id from customer where c_id = 30
POLICY : AUD_OBJ_POLICY
SCHEMA : ORABM
OBJECT : CUSTOMER
2003/09/01 18:10:02
ACTION : SELECT
DB USER: ORABM
OS USER: kshinkub
HOST : INSIGHT\KSHINKUB
IPADDR : xxx.xxx.xxx.xxx
SQL_TXT: select c_data from customer where c_id = 30
POLICY : AUD_OBJ_POLICY
SCHEMA : ORABM
OBJECT : CUSTOMER
**************************************************************************
|
This time, I have defined a procedure as event handler that stores audit records in
external file. This procedure realizes flexible audit.
It is recommended that you should audit important data at regular interval.
That's it for today.
Koji Shinkubo
|
|
