使用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