通过IP限定用户登录trigger

CREATE OR REPLACE TRIGGER log_control
  AFTER logon ON DATABASE
declare
  cursor c1 is
    select *
      from log_control_ip
     where UPPER(TRIM(USERNAME)) = SYS_CONTEXT('USERENV', 'SESSION_USER');
  --根据用户IP查看表中是否有匹配的记录
  ipaddress VARCHAR2(30 BYTE);
  logdbname VARCHAR2(30 BYTE);
  logsessid VARCHAR2(30 BYTE);
  num       number(2);
  num1      number(2);
begin
  ipaddress := SYS_CONTEXT('USERENV', 'IP_ADDRESS');
  logdbname := SYS_CONTEXT('USERENV', 'DB_NAME');
  logsessid := USERENV('SESSIONID');
  num       := 0;
  num1      := 0;

  for owner1 in c1 LOOP
    --循环查询登录参数表中等于登录IP地址的记录
    num1 := 1;
    if ipaddress is NULL THEN
      RAISE_APPLICATION_ERROR(-20999,
                              'This user as a particular user, please go to the listening conn ！');
      --通过操作系统登录的用户,请走监听,否则禁止登录
    end if;
    IF ipaddress = trim(owner1.ipdet) THEN
      --判断用户使用的IP是否是允许的，如果是允许的那么记录用户登录信息
      num := num + 1;
      BEGIN
        insert into log_user_monitor
          (session_id,
           login_on_time,
           login_off_time,
           user_in_db,
           db_name,
           machine,
           ip_address,
           run_program,
           run_action,
           flag)
          select AUDSID,
                 sysdate,
                 null,
                 sys.login_user,
                 logdbname,
                 machine,
                 ipaddress,
                 program,
                 action,
                 0
            from v$session
           where AUDSID = logsessid;
        COMMIT;
      EXCEPTION
        when others then
          null;
      END;
    end if;
  end loop;
  if num = 0 and num1 <> 0 then
    --num = 0 没有匹配的IP
    --num1 = 0 用户不匹配，无法进入循环
    RAISE_APPLICATION_ERROR(-20999,
                            'IP access limited.There is a need to make timely application to the Ministry of information ！');
  end if;
END;

 

--------------------------------------------------------------------------------------------------------------------

create table LOG_CONTROL_IP
(
  IPDET    VARCHAR2(15) not null,
  USERNAME VARCHAR2(15) default 'all' not null,
  ONTIME   DATE default sysdate not null,
  OFFTIME  DATE default to_date('20501201','yyyymmdd') not null,
  FLAG     INTEGER default 1 not null,
  NOTE     VARCHAR2(50)
);

 

create table LOG_USER_MONITOR
(
  SESSION_ID     VARCHAR2(15),
  LOGIN_ON_TIME  DATE,
  LOGIN_OFF_TIME DATE,
  USER_IN_DB     VARCHAR2(15),
  DB_NAME        VARCHAR2(10),
  MACHINE        VARCHAR2(40),
  IP_ADDRESS     VARCHAR2(20),
  RUN_PROGRAM    VARCHAR2(30),
  RUN_ACTION     VARCHAR2(10),
  FLAG           INTEGER
);

--IP access limited.There is a need to make timely application to the Ministry of information ！
--IP访问限定，有需要请及时向信息化部申请!!!
--This user as a particular user, please go to the listening conn ！
--此用户为特殊用户,请走监听 conn