BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
1.限制程序用户指定服务器登录
create or replace trigger logon_ip_control
after logon on database
declare
ip STRING(30);
user STRING(30);
begin
SELECT SYS_CONTEXT('USERENV','SESSION_USER') into user from dual;
SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') into ip from dual;
if user='TEST'
THEN
IF ip not in ('192.168.88.61',''192.168.88.62')
THEN raise_application_error(-20018,'User '||user||' is not allowed to connect from '||ip);
END IF;
if ip is null
THEN raise_application_error(-20018,'User '||user||' is not allowed to connect from '||' Non AMC employees');
END IF;
END IF;
end;
/
--'192.168.88.61',''192.168.88.62' 这些IP地址允许登录TEST程序用户,访问数据库;如果使用其他地址想登录TEST程序用户会报错。
2.限制业务用户只有公司内部指定域用户登录
create or replace trigger logon_user_control
after logon on database
declare
host STRING(30);
user STRING(30);
begin
SELECT SYS_CONTEXT('USERENV','SESSION_USER') into user from dual;
select (select substr(t.ip,-6,6) from (SELECT SYS_CONTEXT('USERENV','host') as ip from dual) t) into host from dual;
if user='DOUDOU'
THEN
IF host not in ('181396','181456')
THEN raise_application_error(-20017,'User '||user||' is not allowed to connect from '||host);
END IF;
if host is null
THEN raise_application_error(-20018,'User '||user||' is not allowed to connect from '||' Non AMC employees');
END IF;
END IF;
end;
/
--'181396','181456' 这些员工ID允许登录DOUDOU业务用户,访问数据库;如果使用其他员工的ID登录DOUDOU业务用户会报错。