标题: 使用自治事务加触发器实现阻止非法主机dml操作 [打印本页] 作者: 郑全 时间: 2019-3-13 17:15 标题: 使用自治事务加触发器实现阻止非法主机dml操作 --创建测试表
create table hr.emp as select * from hr.employees;
--创建日志表
create table HR.TR_EMP_LOG ( os_user varchar2(30),
host varchar2(50),
program varchar2(50),
oper_date date,
client_ip varchar2(30),
db_user varchar2(30),
oper_type varchar2(20))
tablespace users;
--授予权限
grant select on v_$session to HR;
--创建触发器
create or replace trigger HR.TR_EMP
before update or insert or delete on HR.EMP
for each row
DECLARE
exe_program varchar2(100);
begin
select program
into exe_program
from v$session
where sid = sys_context('userenv', 'SID');
if (sys_context('userenv', 'ip_address') = '192.168.133.1' and exe_program = 'plsqldev.exe' and sys_context('userenv', 'SESSION_USER') = 'HR' and inserting)
or
(sys_context('userenv', 'ip_address') = '192.168.133.117' and exe_program = 'plsqldev.exe' and sys_context('userenv', 'SESSION_USER') = 'HR' and updating) then
dbms_output.put_line('success!');
else
if inserting then
p_save_err_result(1,exe_program);
elsif deleting then
p_save_err_result(2,exe_program);
elsif updating then
p_save_err_result(3,exe_program);
end if;
RAISE_APPLICATION_ERROR(-20001, 'you cannot execute DML in EMP table!');
end if;
end;
/
create or replace procedure p_save_err_result(
in_type in number,
in_exe_program in varchar2) is
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
if in_type = 1 then
insert into HR.TR_EMP_LOG
values (sys_context('userenv', 'os_user'),SYS_CONTEXT('USERENV', 'HOST'),in_exe_program,sysdate,sys_context('userenv', 'ip_address'),sys_context('userenv', 'SESSION_USER'),'insert');
elsif in_type = 2 then
insert into HR.TR_EMP_LOG
values
(sys_context('userenv', 'os_user'),SYS_CONTEXT('USERENV', 'HOST'),in_exe_program,sysdate,sys_context('userenv', 'ip_address'),sys_context('userenv', 'SESSION_USER'),'delete');
else
insert into HR.TR_EMP_LOG
values
(sys_context('userenv', 'os_user'),SYS_CONTEXT('USERENV', 'HOST'),in_exe_program,sysdate,sys_context('userenv', 'ip_address'),sys_context('userenv', 'SESSION_USER'),'update');
end if;
commit;
END p_save_err_result;