快速實現oracle10g的審計功能

快速實現oracle10g的審計功能

工作需要,要在一個表上建立審計功能,看了一些文件,摘要如下:

1.首先修改引數audit_trail:
alter system set audit_trail=DB_EXTENDED scope=spfile ;

說明:如果修改成DB引數,無法在sys.aud$表記錄執行的sql文字,以及bind數值.
使用 audit_trail=DB_EXTENDED可以實現.


2.移動sys使用者下的aud$,audit$表到另外的表空間,主要問題是避免佔用系統systemt太多.
  另外需要rebuild相應的索引並且移出system表空間.

ALTER TABLE SYS.aud$   MOVE TABLESPACE tools LOB(sqltext,sqlbind) STORE AS (TABLESPACE tools);
ALTER TABLE SYS.audit$ MOVE TABLESPACE tools;

ALTER INDEX SYS.I_AUD1 REBUILD TABLESPACE TOOLS STORAGE  ( INITIAL 64K ) ;
ALTER INDEX SYS.I_AUDIT REBUILD TABLESPACE TOOLS STORAGE ( INITIAL 64K ) ;


3.建立審計,例子:

AUDIT Delete ON SCOTT.DEPT BY ACCESS WHENEVER SUCCESSFUL ;
AUDIT Update ON SCOTT.DEPT BY ACCESS WHENEVER SUCCESSFUL ;

重啟oracle資料庫.

說明:審計的一些其他選項
by access / by session：
by access 每一個被審計的操作都會生成一條audit trail。
by session 一個會話裡面同型別的操作只會生成一條audit trail，預設為by session。

4.測試資料:
insert into dept values(50,'111',111);
commit ;

update dept  set loc='222' where deptno=50 ;
update dept  set loc='333' where deptno=50 ;
rollback



4.檢視審計結果,執行:
SELECT *  FROM dba_audit_trail;

dba_audit_trail實際上是SYS.aud$ , system_privilege_map spm, system_privilege_map spx,  
stmt_audit_option_map aom, audit_actions act組成的檢視.

不過有一個問題,就是如果執行rollback,在aud$表中一樣存在記錄.
參考:


When Triggers Are Necessary
Avoiding False Positives. Audit trails are generated through autonomous transactions
from the original transactions. Hence they are committed even if the original transactions
are rolled back.

Here is a simple example to illustrate the point. Assume that we have set up auditing for
UPDATEs on table CLASS. A user issues a statement to update a data value from 20 to 10
and then rolls it back as shown below.

update class set size = 10 where class_id = 123;
rollback

Now the value of the column SIZE will be 20, not 10, as if the user never did anything.
However, the audit trail will capture the change, even if it's rolled back. This may be
undesirable in some cases, especially if there are lots of rollbacks by users.
In such a case, you may have to use the trigger to capture only committed changes.
If there were a trigger on the table CLASS to insert records into the user defined
audit trail, upon rollback the audit trails would have been rolled back too.