晕,这年头数据老是被篡改,开发环境都这样。。。。 应开发人员要求,简单记录一下操作 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--- |
|