通过trigger限制客户端来源

生产用户为XXX,为了减少对其他用户影响,将triger放置在该用户下面.如下:

    CREATE OR REPLACE TRIGGER XXX.LOGON_AUDIT
    AFTER LOGON ON DATABASE
    declare
      lv_user   varchar2(100);
      lv_host   varchar2(100);
      lv_schema varchar2(100);
      lv_suser  varchar2(100);
      lv_ip     varchar2(100);
    BEGIN
      select sys_context('USERENV', 'HOST'),
             sys_context('USERENV', 'CURRENT_USER'),
             sys_context('USERENV', 'CURRENT_SCHEMA'),
             sys_context('USERENV', 'SESSION_USER'),
             sys_context('USERENV', 'IP_ADDRESS')
        into lv_host, lv_user, lv_schema, lv_suser, lv_ip
        from dual;
   
      if lv_suser = 'XXX' then
        --记录正常XXX用户登陆记录
        if lv_host = 'alanlee' or substr(lv_ip, 1, 9) = '192.168.1' then
          insert into alanlee.logon_record
            (logon_user, logon_schema, logon_host, ip_address, status)
          values
            (lv_user, lv_schema, lv_host, lv_ip, 'passed');
        else
          RAISE_APPLICATION_ERROR(-20001, 'CONNECTIION REFUSED');
        end if;
      end if;
    END;