使用自治事务加触发器实现阻止非法主机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;