oracle触发器记录所有dml,使用Trigger审计一张表的DML操作

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

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

平台11.2.0.4

sys@ORCL>select*fromv$version;

BANNER

-------------------------------------------------------------------------------------

OracleDatabase11gEnterpriseEditionRelease11.2.0.4.0-64bitProduction

PL/sqlRelease11.2.0.4.0-Production

CORE11.2.0.4.0Production

TNSforLinux:Version11.2.0.4.0-Production

NLSRTLVersion11.2.0.4.0-Production

创建测试表Orders

zx@ORCL>createtableORDERS

2(

3order_idNUMBER,4order_nameVARCHAR2(10)

5);

Tablecreated.

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

zx@ORCL>createtableAUDIT_ORDERS

2(

3orderid_newNUMBER(38),4orderid_oldNUMBER(38),5usernameVARCHAR2(30),6opt_dateDATE,7opt_typeVARCHAR2(10),8terminalVARCHAR2(20),9session_idNUMBER(10),10hostnameVARCHAR2(20)

11);

Tablecreated.

创建触发器

zx@ORCL>CREATEORREPLACETRIGGERTRI_AUDIT_ORDERS

2BEFOREINSERTORUPDATEORDELETEONORDERS

3FOREACHROW

4BEGIN

5IFINSERTINGTHEN

6INSERTINTOAUDIT_ORDERS

7VALUES

8(:NEW.ORDER_ID,9:OLD.ORDER_ID,10USER,11SYSDATE,--记录操作的时间

12'INSERT',13SYS_CONTEXT('USERENV','TERMINAL'),--记录操作来源的终端信息

14USERENV('SID'),--记录操作的SID

15SYS_CONTEXT('USERENV','HOST'));--记录操作的主机名

16ELSIFUPDATINGTHEN

17INSERTINTOAUDIT_ORDERS

18VALUES

19(:NEW.ORDER_ID,20:OLD.ORDER_ID,21USER,22SYSDATE,23'UPDATE',24SYS_CONTEXT('USERENV',25USERENV('SID'),26SYS_CONTEXT('USERENV','HOST'));

27ELSIFDELETINGTHEN

28INSERTINTOAUDIT_ORDERS

29VALUES

30(:NEW.ORDER_ID,31:OLD.ORDER_ID,32USER,33SYSDATE,34'DELETE',35SYS_CONTEXT('USERENV',36USERENV('SID'),37SYS_CONTEXT('USERENV','HOST'));

38ENDIF;

39END;

40/

Triggercreated.

测试数据

--linux的sqlplus插入

zx@ORCL>insertintoordersvalues(1,'zx');

1rowcreated.

zx@ORCL>commit;

Commitcomplete.

--windows的sqlplus插入

sql>insertintoordersvalues(2,'wl');

已创建1行。

sql>commit;

提交完成。

--plsql插入

INSERTINTOordersVALUES(3,'yhz');

COMMIT;

--使用sys用户插入

zx@ORCL>conn/assysdba

Connected.

sys@ORCL>insertintozx.ordersvalues(4,'wj');

1rowcreated.

sys@ORCL>commit;

Commitcomplete.

--更新数据

zx@ORCL>updateordeRSSetorder_id=10whereorder_id=1;

1rowupdated.

zx@ORCL>commit;

Commitcomplete.

--删除数据

zx@ORCL>deletefromorderswhereorder_id<3;

1rowdeleted.

zx@ORCL>commit;

Commitcomplete.

查看记录表中的记录

--测试表记录

zx@ORCL>select*fromorders;

ORDER_IDORDER_NAME

----------------------------------------

3yhz

10zx

4wj

--审计表记录

sql>colusernamefora10

sql>colhostnamefora20

sql>altersessionsetnls_date_format='yyyymmddhh24:mi:ss';

会话已更改。

sql>setlinesize200

sql>select*fromaudit_orders;

ORDERID_NEWORDERID_OLDUSERNAMEOPT_DATEOPT_TYPETERMINALSESSION_IDHOSTNAME

-------------------------------------------------------------------------------------------------------------------------------------------

3ZX2017010421:32:46INSERTVICTOR-PC10WORKGROUP\VICTOR-PC

1ZX2017010421:30:32INSERTpts/024rhel6

2ZX2017010421:31:47INSERTVICTOR-PC146WORKGROUP\VICTOR-PC

4SYS2017010421:33:52INSERTpts/024rhel6

101ZX2017010421:37:26UPDATEpts/024rhel6

2ZX2017010421:37:50DELETEpts/024rhel6

已选择6行。

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

参考：

总结

如果觉得编程之家网站内容还不错，欢迎将编程之家网站推荐给程序员好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。