Oracle如何限制某个IP段只能某些用户访问

本文主要是介绍Oracle如何限制某个IP段只能某些用户访问，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

Oracle如何限制某个IP段只能某些用户访问

如下触发器可以实现并且若有不符合的用户登录会将信息记录到警告日志：

 1 CREATE OR REPLACE TRIGGER logon_check_ip_user AFTER LOGON ON DATABASE
 2 DECLARE
 3   error1 exception;
 4   ip       varchar2(60) := null;
 5   user     varchar2(60) := null;
 6   l_message  varchar2(2000);
 7 BEGIN
 8 SELECT sys_context('USERENV', 'IP_ADDRESS'),
 9        sys_context('USERENV' ,'AUTHENTICATED_IDENTITY')
10   into ip, user
11   from dual;
12 if ip = '192.168.1.40' AND upper(user) not in ('ZKM1','ZKM2') then
13   select 'Illegal login attempt to the "' ||
14          sys_context('USERENV', 'AUTHENTICATED_IDENTITY') || '" schema' ||
15          ' using ' || sys_context('USERENV', 'AUTHENTICATION_TYPE') ||
16          ' authentication' || ' at ' ||
17          to_char(logon_time, 'dd-MON-yy hh24:mi:ss') || ' from ' || osuser || '@' ||
18          machine || ' [' ||
19          nvl(sys_context('USERENV', 'IP_ADDRESS'), 'Unknown IP') || ']' ||
20          ' via the "' || program || '" program.'
21     into l_message
22     from sys.v_$session
23    where sid = to_number(substr(dbms_session.unique_session_id, 1, 4), 'xxxx')
24      and serial# = to_number(substr(dbms_session.unique_session_id, 5, 4), 'xxxx');
25   raise error1;
26 end if;
27 exception
28   WHEN error1 THEN
29     sys.dbms_system .ksdwrt( 2,l_message );
30     raise_application_error(num => -20001,msg =>'Illegal login,permission denied! ');
31   WHEN others THEN 
32      raise_application_error(num => -20001,msg =>'Illegal login,permission denied! ');
33 END;
34 /

这篇关于Oracle如何限制某个IP段只能某些用户访问的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！