监控表的DML操作

 晕，这年头数据老是被篡改，开发环境都这样。。。。 应开发人员要求，简单记录一下操作

create table TMP_MONITOR
(
USERNAME   VARCHAR2(100),
OSUSER     VARCHAR2(100),
MACHINE    VARCHAR2(100),
TERMINAL   VARCHAR2(100),
PROGRAM    VARCHAR2(100),
SQL_ID     VARCHAR2(100),
MODULE     VARCHAR2(100),
GMT_CREATE DATE,
SQLTEXT    VARCHAR2(2000)
);

------------------------
触发器

create or replace trigger tr_test_xf
before insert or update or delete on tmp_xf_test   
for each row
declare
-- local variables here
begin

insert into tmp_monitor
(username,
osuser,
machine,
terminal,
program,
sql_id,
module,
gmt_create,
sqltext)
select t1.username,
t1.osuser,
t1.machine,
t1.terminal,
t1.program,
t1.sql_id,
t1.module,
sysdate,
t2.SQL_TEXT
from v$session t1, v$sql t2
where t1.sid in (select sid from v$mystat where rownum = 1)
and t1.SQL_ID = t2.SQL_ID
and rownum = 1;

end tr_test_xf;

--------

如果需要IP可以加上
select sys_context('userenv', 'ip_address' ) INTO v_ipaddr from dual;

##################################
增加一个 如果需要让特定的应用不能去更改某张表的数据,那么可以用如此trigger

CREATE OR REPLACE TRIGGER TR_TMP_XF_TEST_NOA
BEFORE INSERT OR UPDATE OR DELETE ON TMP_XF_1
FOR EACH ROW
DECLARE
V_PROM VARCHAR(200);

BEGIN

SELECT T1.PROGRAM
INTO V_PROM
FROM V$SESSION T1, V$SQL T2
WHERE T1.SID IN (SELECT SID FROM V$MYSTAT WHERE ROWNUM < 2)
AND T1.SQL_ID = T2.SQL_ID;

IF UPPER(V_PROM) = 'PLSQLDEV.EXE' THEN
RAISE_APPLICATION_ERROR(-20002,
'You can do the dml on the table tmp_xf_1 by plsqldev');
END IF;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;

--上面脚本的增强版,加个配置表

CREATE OR REPLACE TRIGGER tr_tmp_xf_config
BEFORE INSERT OR UPDATE OR DELETE ON TMP_XF_TRI_CONF
FOR EACH ROW
DECLARE
V_PROM     VARCHAR(200);
V_TERMINAL VARCHAR2(200);
V_COUNT    NUMBER;
BEGIN

SELECT T1.PROGRAM, T1.TERMINAL
INTO V_PROM, V_TERMINAL
FROM V$SESSION T1, V$SQL T2
WHERE T1.SID IN (SELECT SID FROM V$MYSTAT WHERE ROWNUM < 2)
AND T1.SQL_ID = T2.SQL_ID and rownum < 1;

IF UPPER(V_TERMINAL) = 'T-XUANFENG 'THEN
IF UPPER(V_PROM) = 'PLSQLDEV.EXE'  THEN
RAISE_APPLICATION_ERROR(-20002,
'您没有权限修改该表，请联系***');
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;


--eof---