快速实现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;
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.