为了严格控制生产库的用户登陆，包括登陆的程序和ip地址等，前不久在线上库创建了一个trigger以实现这个功能：
create or replace trigger check_ip after logon on database
declare
ip varchar2(100);
user_pr varchar2(2000);
user_xz varchar2(2000);
begin
select nvl(program, 'kasaur')
into user_pr
from gv$session
where AUDSID = USERENV('SESSIONID')
and AUDSID <> 0
and rownum = 1;
SELECT nvl(machine, 'kasaur')
into user_xz
from gv$session
where AUDSID = USERENV('SESSIONID')
and AUDSID <> 0
and rownum = 1;
select distinct nvl(sys_context('USERENV', 'IP_ADDRESS'), '0')
into ip
from dual;
if user_xz like '%FILESERVER%' or user_xz like '%FS0%' then
raise_application_error(-20001, 'you are not permitted!');
elsif ip not in ('xx.xx.xx.xx', 'xx.xx.xx.xx', 'xx.xx.xx.xx', '0',
'xx.xx.xx.xx') and
lower(user_pr) in ('plsqldev.exe', 'toad.exe', 'prodbtools.exe',
'sql developer', 'exp.exe', 'expdp.exe') then
raise_application_error(-20001, 'you are not permitted!');
end if;
EXCEPTION
WHEN NO_DATA_FOUND THEN
null;
end;
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/25618347/viewspace-714088/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
oracle备份脚本
请登录后发表评论
登录
全部评论
