Oracle如何限制某个IP段只能某些用户访问 |
您所在的位置：网站首页 › oracle访问ip限制 › Oracle如何限制某个IP段只能某些用户访问 |
Oracle如何限制某个IP段只能某些用户访问
|
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.
如果是限制某个ip段，可以修改第12行的SQL，if var_ip like '192.168.1.%'。
|
【本文地址】