使用触发器来审计表的DML、DDL操作

最近帮客户排查某问题时，因为怀疑应用对某张配置表有变更，所以需要对这张表的所有操作进行审计。

原本Oracle对某张表的审计是非常方便的，一条命令就可以实现，也不需要费心自定义审计表。

-- 启用对表DEPT的插入、更新和删除操作的审计
AUDIT INSERT, UPDATE, DELETE ON DEPT BY ACCESS;

-- 查看对DEPT表的所有审计记录
SELECT * FROM DBA_AUDIT_TRAIL WHERE OBJ_NAME = 'DEPT';

-- 停用对表DEPT的审计
NOAUDIT INSERT, UPDATE, DELETE ON DEPT;

但不幸的是，因为客户当前环境已经把默认开启的audit_trail给关闭了。（默认开启，默认值是DB，关闭就是NONE）
笔者已经在实验环境验证过：
– 1.audit_trail必须开的情况下，对表进行审计才会有记录；
– 2.audit_trail是静态参数，如果修改，需要重启数据库才可以生效。

尤其注意要检查这个参数的设置情况，因为这里比较坑的一点是，上述审计命令，即便你关了这个参数，开启表审计的命令执行也并不会报错，只是实际上无法记录。

相信大家都知道，生产环境的重启申请流程非常麻烦，因此只能用手工的方式，比如自定义触发器的方式来监控这张表的DML操作。

还好因为这个配置表正常情况下，并不会被频繁修改，所以使用触发器也不会带来什么性能问题。

下面就进入到hands-on环节，我们模拟下这个客户的需求，假设dept这张表:

-- 创建示例表DEPT
CREATE TABLE dept (
    deptno NUMBER(2) PRIMARY KEY,
    dname VARCHAR2(14),
    loc VARCHAR2(13)
);

-- 插入示例数据到DEPT表
INSERT INTO dept VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO dept VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO dept VALUES (40, 'OPERATIONS', 'BOSTON');

commit;

针对dept表建立触发器如下：

-- CREATE TABLE
CREATE TABLE dml_audit(
modiy_time DATE, 
table_name VARCHAR2(30), 
deptno NUMBER(2), 
modiy_type VARCHAR2(14), 
dname VARCHAR2(14), 
loc VARCHAR2(13)
);

CREATE OR REPLACE TRIGGER dept_trg
BEFORE INSERT OR DELETE OR UPDATE ON dept
FOR EACH ROW
BEGIN
  IF inserting THEN 
    INSERT INTO dml_audit (modiy_time, table_name, deptno, modiy_type, dname, loc) 
    VALUES (sysdate, 'dept', :NEW.deptno, 'insert', :NEW.dname, :NEW.loc);
  END IF;

  IF deleting THEN 
    INSERT INTO dml_audit (modiy_time, table_name, deptno, modiy_type, dname, loc) 
    VALUES (sysdate, 'dept', :OLD.deptno, 'delete', :OLD.dname, :OLD.loc);
  END IF;

  IF updating THEN 
    INSERT INTO dml_audit (modiy_time, table_name, deptno, modiy_type, dname, loc) 
    VALUES (sysdate, 'dept', :OLD.deptno, 'update_before', :OLD.dname, :OLD.loc);

    INSERT INTO dml_audit (modiy_time, table_name, deptno, modiy_type, dname, loc) 
    VALUES (sysdate, 'dept', :NEW.deptno, 'update_after', :NEW.dname, :NEW.loc);
  END IF;
END;
/

测试下，确认delete、insert、update都可以监测到：

08:17:58 PRIMARY @DB0913_9DF_IAD -> JINGYU @DEMO2> select * from dml_audit;

no rows selected

Elapsed: 00:00:00.02
08:18:05 PRIMARY @DB0913_9DF_IAD -> JINGYU @DEMO2> delete from dept where deptno=10;

1 row deleted.

Elapsed: 00:00:00.22
08:18:41 PRIMARY @DB0913_9DF_IAD -> JINGYU @DEMO2> INSERT INTO dept VALUES (10, 'ACCOUNTING', 'NEW YORK');

1 row created.

Elapsed: 00:00:00.00
08:18:56 PRIMARY @DB0913_9DF_IAD -> JINGYU @DEMO2> update dept set LOC='China' where deptno=40;

1 row updated.

Elapsed: 00:00:00.04
08:19:25 PRIMARY @DB0913_9DF_IAD -> JINGYU @DEMO2> select * from dml_audit;

MODIY_TIME     TABLE_NAME                 DEPTNO MODIY_TYPE     DNAME      LOC
------------------ ------------------------------ ---------- -------------- -------------- -------------
07-AUG-24      dept                   10 delete     ACCOUNTING     NEW YORK
07-AUG-24      dept                   10 insert     ACCOUNTING     NEW YORK
07-AUG-24      dept                   40 update_before  OPERATIONS     BOSTON
07-AUG-24      dept                   40 update_after   OPERATIONS     China

Elapsed: 00:00:00.01

是否万事大吉了呢？

其实不是，因为客户问题是怀疑这个表有被改动，而改动其实不仅仅局限于DML操作。

我们假设一种极端场景，如果一个操作是将这张表drop后重建，然后插入一样的数据。

这样的场景，仅靠上面的触发器就无法记录到。因为表被drop时，对应的触发器也会被删除。
因为触发器是依赖于表存在的对象，当表被删除时，触发器也会失去作用的对象。

比如有这样一个init的脚本，功能是直接对配置表进行了drop删除然后重新创建初始化数据：

@init

那要如何做才能监控到这种极端情况呢？
也不难，再建立一个针对DDL的触发器，如果对dept表有ddl操作也记录到指定日志表。

-- 创建审计表
CREATE TABLE ddl_audit (
    event_time  TIMESTAMP,
    username    VARCHAR2(30),
    userhost    VARCHAR2(30),
    object_type VARCHAR2(30),
    object_name VARCHAR2(30),
    action      VARCHAR2(30)
);

-- 创建 DDL 触发器
CREATE OR REPLACE TRIGGER ddl_trigger
AFTER CREATE OR DROP ON SCHEMA
DECLARE
    v_username    VARCHAR2(30);
    v_userhost    VARCHAR2(30);
BEGIN
    SELECT USER, SYS_CONTEXT('USERENV', 'HOST') INTO v_username, v_userhost FROM DUAL;

    IF ORA_DICT_OBJ_NAME = 'DEPT' THEN
        INSERT INTO ddl_audit (
            event_time, username, userhost, object_type, object_name, action
        ) VALUES (
            SYSTIMESTAMP, v_username, v_userhost, ORA_DICT_OBJ_TYPE, ORA_DICT_OBJ_NAME, ORA_SYSEVENT
        );
    END IF;
END;
/

这样，如果真有这种DDL操作，也可以审计到，但需要注意，这个DDL的触发器的影响相对要大些，非必要情况先不要建。定位完问题后，及时删掉。

08:26:49 PRIMARY @DB0913_9DF_IAD -> JINGYU @DEMO2> select * from ddl_audit;

EVENT_TIME             USERNAME        USERHOST        OBJECT_TYPE            OBJECT_NAME     ACTION
------------------------------ --------------- --------------- ------------------------------ --------------- ------------------------------
07-AUG-24 08.25.11.885875 AM   JINGYU          demo        TABLE                  DEPT        DROP
07-AUG-24 08.25.12.167304 AM   JINGYU          demo        TABLE                  DEPT        CREATE
07-AUG-24 08.25.59.989356 AM   JINGYU          demo        TABLE                  DEPT        DROP
07-AUG-24 08.26.00.061629 AM   JINGYU          demo        TABLE                  DEPT        CREATE

Elapsed: 00:00:00.01

可以看到，这样触发器的方式，虽然比审计笨重，但也可以实现对表无论是DDL还是DML的跟踪记录，有了这些操作痕迹，自然就方便客户去进一步排查问题了。