Oracle通过触发器审计一张表的DML

1.场景描述：

工作中，OGG数据同步的时候发现目标端B库有部分update操作没有同步过去，通过logminer挖掘源端A库发现没同步的操作是ogg配置用户执行操作的，OGG抽取进程中又设置了TRANLOGOPTIONS EXCLUDEUSER参数（防止出现级联同步）排除了OGG配置用户的操作，通过查看发现该同步表未从其他库同步过来（即A库是这张同步表最原始的数据来源）。然而logminer挖掘出来的os_username，machine_name和session_info都是unkown。通过表审计可以查看，不过需要相关审计参数打开，调整审计参数需要重启db，只能转换思路通过在表上创建触发器来审计该表的DML操作。

2.实验

1. 创建实验测试表，并初始化

SQL>create table crmsb(id number, create_time date,update_time date,content varchar2(50));
SQL>insert into crmsb values(1,to_date('20200820 10:00:00','YYYY/MM/DD HH24:MI:SS'),to_date('20200820 10:00:00','YYYY/MM/DD HH24:MI:SS'),'first');
SQL>commit;
SQL>select * from crmsb;

        ID CREATE_TIME                    UPDATE_TIME                    CONTENT
---------- ------------------------------ ------------------------------ ------------------------------
         1 2020-08-20 10:00:00            2020-08-20 10:00:00            first

2. 创建审计表

SQL>create table audit_crmsb
SQL>(
SQL>  id_new NUMBER,  
SQL>  id_old NUMBER,
SQL>  create_time_new date,
SQL>  create_time_old date,
SQL>  update_time_new date,
SQL>  update_time_old date,
SQL>  content_new varchar2(50),
SQL>  content_old varchar2(50),    
SQL>  username    VARCHAR2(30),
SQL>  opt_date    DATE,
SQL>  opt_type    VARCHAR2(10),
SQL>  terminal    VARCHAR2(50),
SQL>  session_id  NUMBER(10),
SQL>  hostname    VARCHAR2(50)
SQL>);

3. 创建审计触发器

SQL> CREATE OR REPLACE TRIGGER TRI_AUDIT_CRMSBA
SQL>   BEFORE INSERT OR UPDATE OR DELETE ON CRMSB
SQL>   FOR EACH ROW
SQL> BEGIN
SQL>   IF INSERTING THEN
SQL>     INSERT INTO AUDIT_CRMSB
SQL>     VALUES
SQL>       (:NEW.ID,
SQL>        :OLD.ID,
SQL>        :NEW.CREATE_TIME,
SQL>        :OLD.CREATE_TIME,
SQL>        :NEW.UPDATE_TIME,
SQL>        :OLD.UPDATE_TIME,
SQL>        :NEW.CONTENT,
SQL>        :OLD.CONTENT,
SQL>        USER,
SQL>        SYSDATE,
SQL>        'INSERT',
SQL>        SYS_CONTEXT('USERENV', 'TERMINAL'),
SQL>        USERENV('SID'),
SQL>        SYS_CONTEXT('USERENV', 'HOST'));
SQL>   ELSIF UPDATING THEN
SQL>     INSERT INTO AUDIT_CRMSB
SQL>     VALUES
SQL>       (:NEW.ID,
SQL>        :OLD.ID,
SQL>        :NEW.CREATE_TIME,
SQL>        :OLD.CREATE_TIME,
SQL>        :NEW.UPDATE_TIME,
SQL>        :OLD.UPDATE_TIME,
SQL>        :NEW.CONTENT,
SQL>        :OLD.CONTENT,
SQL>        USER,
SQL>        SYSDATE,
SQL>        'UPDATE',
SQL>        SYS_CONTEXT('USERENV', 'TERMINAL'),
SQL>        USERENV('SID'),
SQL>        SYS_CONTEXT('USERENV', 'HOST'));
SQL>   ELSIF DELETING THEN
SQL>     INSERT INTO AUDIT_CRMSB
SQL>     VALUES
SQL>       (:NEW.ID,
SQL>        :OLD.ID,
SQL>        :NEW.CREATE_TIME,
SQL>        :OLD.CREATE_TIME,
SQL>        :NEW.UPDATE_TIME,
SQL>        :OLD.UPDATE_TIME,
SQL>        :NEW.CONTENT,
SQL>        :OLD.CONTENT,
SQL>        USER,
SQL>        SYSDATE,
SQL>        'DELETE',
SQL>        SYS_CONTEXT('USERENV', 'TERMINAL'),
SQL>        USERENV('SID'),
SQL>        SYS_CONTEXT('USERENV', 'HOST'));
SQL>   END IF;
SQL> END;
SQL> /

4. 数据测试

SQL> insert into crmsb values(2,sysdate,sysdate,'second');

1 row created.

SQL> commit;

Commit complete.

SQL> update crmsb set update_time = sysdate where id = 1;

1 row updated.

SQL> commit;

Commit complete.

SQL> delete from crmsb where id = 2;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from audit_crmsb;
ID_NEW	ID_OLD	CREATE_TIME_NEW			CREATE_TIME_OLD		UPDATE_TIME_NEW		UDATE_TIME_OLD		CONTENT_NEW		CONTENT_OLD		USERNAME	OPT_DATE			OPT_TYPE	TERMINAL	SESSION_ID	HOSTNAME
	2			2020/8/26 15:11:27							2020/8/26 15:11:27						second			    			DBMGR		2020/8/26 15:11:27	INSERT		pts/0		419			cnsz92vl00060
	1	1		2020/8/20 10:00:00		2020/8/20 10:00:00	2020/8/26 15:11:57	2020/8/20 10:00:00	first			first			DBMGR		2020/8/26 15:11:57	UPDATE		pts/0		419			cnsz92vl00060
		2		2020/8/26 15:11:27		2020/8/26 15:11:27															second			DBMGR		2020/8/26 15:12:11	DELETE		pts/0		419			cnsz92vl00060

3.总结

定位一条数据SQL操作源头可以通过表审计，logminer日志挖掘和表上创建trigger记录数据操作情况（通常在业务中也会采取这种方法定位数据是否更新写入成功）