细粒度审计可以实现对于行的审计,在实际系统中,对于要求精细审计的场合,十分有用。比如要查询谁修改了员工5450的工资等等。
SQL> select * from scott.ttt; --首先创建一个scott.ttt表,有如下数据:
ID NAME
---- ----------
20 'hehe'
30 'xixi'
40 'lala'
--加入细粒度审计策略,对于该表的update插入,如果更新了id>20的行,需要审计
SQL> begin
2 dbms_fga.add_policy('SCOTT','TTT','ttt_aud','id > 20',statement_types => 'UPDATE',audit_trail => DBMS_FGA.DB_EXTENDED);
3 END;
4 /
--查看审计策略状况
SQL> SELECT OBJECT_NAME,POLICY_NAME,POLICY_TEXT FROM DBA_AUDIT_POLICIES;
OBJECT_NAME POLICY_NAME POLICY_TEXT
------------------------------ ------------------------------ --------------------------------------------------------------------------------
TTT TTT_AUD id > 20
--以scott用户登录,修改ttt表【注意,sys用户修改该表,不会记录审计信息】
scott@TESTASM> update ttt set name='jfod' where id = 40;
1 row updated.
scott@TESTASM> commit;
Commit complete.
scott@TESTASM> update ttt set name='jfod' where id = 20;
1 row updated.
--查看审计信息,只发现了修改id=40行的update语句,所以成功实现了细粒度审计
SQL> SELECT SQL_TEXT FROM DBA_FGA_AUDIT_TRAIL;
SQL_TEXT
--------------------------------------------------------------------------------
update ttt set name='jfod' where id = 40
scott@TESTASM> commit;
Commit complete.
--再次修改
scott@TESTASM> update ttt set name='jaaafod' where id = 40;
1 row updated.
--再回滚
scott@TESTASM> rollback;
Rollback complete.
--发现审计记录中,不管是回滚的操作还是提交的操作,都会记录下来
SQL> SELECT SQL_TEXT FROM DBA_FGA_AUDIT_TRAIL;
SQL_TEXT
--------------------------------------------------------------------------------
update ttt set name='jfod' where id = 40
update ttt set name='jaaafod' where id = 40
--修改整个表
scott@TESTASM> update ttt set name='ooooo';
3 rows updated.
--查询审计记录
SQL> SELECT SQL_TEXT FROM DBA_FGA_AUDIT_TRAIL;
SQL_TEXT
--------------------------------------------------------------------------------
update ttt set name='jfod' where id = 40
update ttt set name='jaaafod' where id = 40
update ttt set name='ooooo'