使用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 Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL 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 sysdba
Connected.
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 a10
SQL> col hostname for a20
SQL> alter session set nls_date_format='yyyymmdd hh34:mi:ss';

会话已更改。

SQL> set linesize 200
SQL> 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