精品伊人久久大香线蕉,开心久久婷婷综合中文字幕,杏田冲梨,人妻无码aⅴ不卡中文字幕

 暈，這年頭數(shù)據(jù)老是被篡改，開發(fā)環(huán)境都這樣。。。。 應(yīng)開發(fā)人員要求，簡單記錄一下操作

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)
);

------------------------
觸發(fā)器

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;

##################################
增加一個 如果需要讓特定的應(yīng)用不能去更改某張表的數(shù)據(jù),那么可以用如此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,
'您沒有權(quán)限修改該表，請聯(lián)系***');
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;


--eof---