限制使用者在某個網段Logon實現trigger

gdutllf2006發表於2010-05-29

create table system.t_validip( IP_ADDRESS VARCHAR2(20),USERNAME   VARCHAR2(20)) tablespace users;

insert into system.t_validip values('10.230.12.2','*');
insert into system.t_validip values('10.230.12.3','*');
insert into system.t_validip values('10.230.12.4','*');
insert into system.t_validip values('10.230.11.2','*');
commit;

create trigger sys.login_on_info
after logon on database
declare
  s_username varchar2(20);

  s_machine varchar2(100);
  s_ip  varchar2(100);

  i  number;

Begin
    s_username := upper(user);
    s_machine := substrb(sys_context('USERENV','HOST'),1,100);
    s_ip := SYS_CONTEXT('USERENV','IP_ADDRESS');
    if s_username in ('MTAMANAGER', 'MAILCONTENT') then
      if ( s_ip is null) then
        null;
      else
       select count(*) into i from system.t_validip where ip_address=s_ip and (username='*' or username=s_username);
        if i=0 then
                  raise_application_error(-20010,'INVALID USER!('||s_username||','||s_ip||')');
    end if;
        end if;
      end if;
end;
     

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/10248702/viewspace-663987/,如需轉載,請註明出處,否則將追究法律責任。

相關文章