随笔- 248 文章- 0 评论- 18 阅读- 21万

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

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

CREATE OR REPLACE TRIGGER logon_check_ip_user AFTER LOGON ON DATABASE
DECLARE
  error1 exception;
  ip       varchar2(60) := null;
  user     varchar2(60) := null;
  l_message  varchar2(2000);
BEGIN
SELECT sys_context('USERENV', 'IP_ADDRESS'),
       sys_context('USERENV' ,'AUTHENTICATED_IDENTITY')
  into ip, user
  from dual;
if ip = '192.168.1.40' AND upper(user) not in ('ZKM1','ZKM2') then
  select 'Illegal login attempt to the "' ||
         sys_context('USERENV', 'AUTHENTICATED_IDENTITY') || '" schema' ||
         ' using ' || sys_context('USERENV', 'AUTHENTICATION_TYPE') ||
         ' authentication' || ' at ' ||
         to_char(logon_time, 'dd-MON-yy hh24:mi:ss') || ' from ' || osuser || '@' ||
         machine || ' [' ||
         nvl(sys_context('USERENV', 'IP_ADDRESS'), 'Unknown IP') || ']' ||
         ' via the "' || program || '" program.'
    into l_message
    from sys.v_$session
   where sid = to_number(substr(dbms_session.unique_session_id, 1, 4), 'xxxx')
     and serial# = to_number(substr(dbms_session.unique_session_id, 5, 4), 'xxxx');
  raise error1;
end if;
exception
  WHEN error1 THEN
    sys.dbms_system .ksdwrt( 2,l_message );
    raise_application_error(num => -20001,msg =>'Illegal login,permission denied! ');
  WHEN others THEN 
     raise_application_error(num => -20001,msg =>'Illegal login,permission denied! ');
END;
/

展开复制

 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 /

如上的触发器，如果ip地址是192.168.1.40并且登录用户不是'ZKM1'和'ZKM2'的话，就会禁止登录并且将信息记录到警告日志。

警告日志：

Tue Jul 26 15:30:43 2022
Illegal login attempt to the "zkm" schema using DATABASE authentication at 26-JUL-22 15:30:43 from admin@WORKGROUP\YXB-ZHAOKM [192.168.1.40] via the "plsqldev.exe" program.