审计列值变动

最新推荐文章于 2024-11-14 16:32:39 发布

jgmydsai

最新推荐文章于 2024-11-14 16:32:39 发布

阅读量565

点赞数

CC 4.0 BY-SA版权

分类专栏： pl/sql

本文链接：https://blog.youkuaiyun.com/jgmydsai/article/details/44626005

pl/sql 专栏收录该内容

15 篇文章

订阅专栏

本文介绍如何使用Oracle的Fine-Grained Auditing (FGA)功能来监控特定表的更改情况，通过添加策略来跟踪对特定列的更新和插入操作，并展示了如何查看审计结果。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

监控用户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');