有时候数据库总是会遇到一些灵异事件,比如什么数据莫名其妙被人篡改了,但也不想去查日志分析。一张不太应该改动的表,竟然有人会改动。这个时候需要一个捉鬼触发器。
以下是我利用sys_context写的一个较为完善的、用于记录操作行为的触发器。会把这些数据写进一个叫做guardian的表里。
- create or replace trigger trg_input_1_g
- before insert or update on input_1
- for each row
- declare
- terminal varchar2(30);
- host varchar2(30);
- IP varchar2(30);
- osuser varchar2(30);
- dbUser varchar2(30);
- catch_time date;
- begin
- select sysdate into catch_time from dual;
- select sys_context('userenv','TERMINAL'),SYS_CONTEXT('USERENV','HOST') host,sys_context('USERENV','IP_ADDRESS'),SYS_CONTEXT('USERENV','OS_USER') os_user,sys_context('userenv','SESSION_USER')
- into terminal,host,ip,osuser,dbUser from dual;
- insert into GUARDIAN values(terminal,host,ip,osuser,dbUser,catch_time,'DBMTEAM.INPUT_1');
- end ;
guardian表结构如下:
SQL> desc guardian
Name Type Nullable Default Comments
---------- ------------ -------- ------- --------
TERMINAL VARCHAR2(30) Y
HOST VARCHAR2(30) Y
IP VARCHAR2(30) Y
OSUSER VARCHAR2(30) Y
DBUSER VARCHAR2(30) Y
CATCH_TIME DATE Y
TABLE_NAME VARCHAR2(50) Y
示例结果如下:
SQL> select * from guardian;
TERMINAL HOST IP OSUSER DBUSER CATCH_TIME TABLE_NAME
--------- ------------- --------- ------ ------- ----------- -----------
AA-PC WORKGROUP\AA-PC 172.16.31.196 AA DBMTEAM 2012/9/4 11 DBMTEAM.INPUT_1
转载于:https://blog.51cto.com/gundam/981617