使用触发器来审计表的DML、DDL操作
2024-08-07 23:11 AlfredZhao 阅读(258) 评论(0) 编辑 收藏 举报最近帮客户排查某问题时,因为怀疑应用对某张配置表有变更,所以需要对这张表的所有操作进行审计。
原本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的跟踪记录,有了这些操作痕迹,自然就方便客户去进一步排查问题了。