快速實作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$表中一樣存在記錄.

參考:

http://www.oracle.com/technology/pub/articles/10gdba/week10_10gdba.html

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;

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.