晕,这年头数据老是被篡改,开发环境都这样。。。。 应开发人员要求,简单记录一下操作
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---
本站仅提供存储服务,所有内容均由用户发布,如发现有害或侵权内容,请
点击举报。