审计列值变动

监控用户test下dept2.dname的改动

SQL> desc dept2;
Name   Type         Nullable Default Comments 
------ ------------ -------- ------- -------- 
DEPTNO NUMBER       Y                         
DNAME  VARCHAR2(14) Y                         
LOC    VARCHAR2(13) Y 

增加add policy

BEGIN
  dbms_fga.add_policy(object_schema   => 'TEST',
                      object_name     => 'dept2',
                      policy_name     => 'update_dept2_dname',
                      audit_column    => 'dname',
                      statement_types => 'update,insert',
                      ENABLE          => TRUE);
END;
/

查看刚增加的policy

SQL> set linesize 500
SQL> col policy_text for a30
SQL> SELECT object_schema, object_name, policy_name, enabled
  2    FROM dba_audit_policies;
OBJECT_SCHEMA                  OBJECT_NAME                    POLICY_NAME                    ENABLED
------------------------------ ------------------------------ ------------------------------ -------
TEST                           DEPT2                          UPDATE_DEPT2_DNAME             YES
1 row selected
Executed in 0.032 seconds

更改dept2

INSERT INTO dept2 SELECT * FROM dept;
UPDATE dept2 SET dname = 'aa';

查看审计结果

SQL> SELECT TIMESTAMP,
  2         sql_text,
  3         policy_name
  4    FROM dba_fga_audit_trail
  5   ORDER BY TIMESTAMP;
TIMESTAMP   SQL_TEXT                                 POLICY_NAME
----------- ---------------------------------------- ------------------------------
2015-03-25  UPDATE dept2 SET dname = 'aa'            UPDATE_DEPT2_DNAME
2015-03-25  INSERT INTO dept2 SELECT * FROM dept     UPDATE_DEPT2_DNAME
2 rows selected
Executed in 0.016 seconds

drop policy

BEGIN
  dbms_fga.drop_policy(object_schema => 'TEST',
                       object_name   => 'dept2',
                       policy_name   => 'update_dept2_dname');
END;

删除审计结果

DELETE FROM sys.fga_log$ WHERE policyname = upper('UPDATE_DEPT2_DNAME');