使用Trigger审计一张表的DML操作

最近ogg的灾备端复制进程中的一张表老是报错ORA-04031，但是又查不到原因，于是想用审计的方法来看到底这张表是被谁做了DML操作，把数据搞没了。本来想用数据库自带的审计功能参考：http://hbxztc.blog.51cto.com/1587495/1870181

但是需要重启数据库，就放弃了，上网查资料看到有人用触发器来实现这个功能，于是自己也做了尝试。

平台11.2.0.4

sys@ORCL>select * from v$version;BANNER-------------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionPL/SQL Release 11.2.0.4.0 - ProductionCORE    11.2.0.4.0      ProductionTNS for Linux: Version 11.2.0.4.0 - ProductionNLSRTL Version 11.2.0.4.0 - Production

创建测试表Orders

zx@ORCL>create table ORDERS  2  (  3    order_id   NUMBER,  4    order_name VARCHAR2(10)  5  );Table created.

创建用于记录DML操作记录的表

zx@ORCL>create table AUDIT_ORDERS  2  (  3    orderid_new NUMBER(38),    4    orderid_old NUMBER(38),  5    username    VARCHAR2(30),  6    opt_date    DATE,  7    opt_type    VARCHAR2(10),  8    terminal    VARCHAR2(50),  9    session_id  NUMBER(10), 10    hostname    VARCHAR2(50) 11  );Table created.

创建触发器

zx@ORCL>CREATE OR REPLACE TRIGGER TRI_AUDIT_ORDERS  2    BEFORE INSERT OR UPDATE OR DELETE ON ORDERS  3    FOR EACH ROW  4  BEGIN  5    IF INSERTING THEN  6      INSERT INTO AUDIT_ORDERS  7      VALUES  8        (:NEW.ORDER_ID,  9         :OLD.ORDER_ID, 10         USER, 11         SYSDATE,                           --记录操作的时间 12         'INSERT', 13         SYS_CONTEXT('USERENV', 'TERMINAL'),--记录操作来源的终端信息 14         USERENV('SID'),                    --记录操作的SID 15         SYS_CONTEXT('USERENV', 'HOST'));   --记录操作的主机名 16    ELSIF UPDATING THEN 17      INSERT INTO AUDIT_ORDERS 18      VALUES 19        (:NEW.ORDER_ID, 20         :OLD.ORDER_ID, 21         USER, 22         SYSDATE, 23         'UPDATE', 24         SYS_CONTEXT('USERENV', 'TERMINAL'), 25         USERENV('SID'), 26         SYS_CONTEXT('USERENV', 'HOST')); 27    ELSIF DELETING THEN 28      INSERT INTO AUDIT_ORDERS 29      VALUES 30        (:NEW.ORDER_ID, 31         :OLD.ORDER_ID, 32         USER, 33         SYSDATE, 34         'DELETE', 35         SYS_CONTEXT('USERENV', 'TERMINAL'), 36         USERENV('SID'), 37         SYS_CONTEXT('USERENV', 'HOST')); 38    END IF; 39  END; 40  /Trigger created.

测试数据

--linux的sqlplus插入zx@ORCL>insert into orders values(1,'zx');1 row created.zx@ORCL>commit;Commit complete.--windows的sqlplus插入SQL> insert into orders values(2,'wl');已创建 1 行。SQL> commit;提交完成。--plsql插入INSERT INTO orders VALUES(3,'yhz');COMMIT;--使用sys用户插入zx@ORCL>conn / as sysdbaConnected.sys@ORCL>insert into zx.orders values(4,'wj');1 row created.sys@ORCL>commit;Commit complete.--更新数据zx@ORCL>update orders set order_id=10 where order_id=1;1 row updated.zx@ORCL>commit;Commit complete.--删除数据zx@ORCL>delete from orders where order_id<3;1 row deleted.zx@ORCL>commit;Commit complete.

查看记录表中的记录

--测试表记录zx@ORCL>select * from orders;  ORDER_ID ORDER_NAME---------- ------------------------------         3 yhz        10 zx         4 wj--审计表记录SQL> col username for a10SQL> col hostname for a20SQL> alter session set nls_date_format='yyyymmdd hh34:mi:ss';会话已更改。SQL> set linesize 200SQL> select * from audit_orders;ORDERID_NEW ORDERID_OLD USERNAME   OPT_DATE         OPT_TYPE            TERMINAL                               SESSION_ID HOSTNAME----------- ----------- ---------- ----------------- -------------------- ---------------------------------------- ---------- --------------------          3           ZX         20170104 21:32:46 INSERT              VICTOR-PC                                      10 WORKGROUP\VICTOR-PC          1           ZX         20170104 21:30:32 INSERT              pts/0                                     24 rhel6          2           ZX         20170104 21:31:47 INSERT              VICTOR-PC                                     146 WORKGROUP\VICTOR-PC          4           SYS        20170104 21:33:52 INSERT              pts/0                                     24 rhel6         10              1 ZX      20170104 21:37:26 UPDATE              pts/0                                     24 rhel6                      2 ZX      20170104 21:37:50 DELETE              pts/0                                     24 rhel6已选择 6 行。

审计表记录了所有的DML操作，可以用于审计哪些用户对表做了哪些操作。

参考：

http://www.cnblogs.com/wanglibo/articles/2121098.html

http://www.cnblogs.com/huyong/archive/2011/04/27/2030466.html