为了数据库安全,有时候需要限制某些数据库用户只有特定的IP才能登陆,使用触发器,获取登录用户的IP就可以实现上述功能。
SQL语句如下:
CREATE OR REPLACE TRIGGER system.check_ip_addresses
AFTER logon ON DATABASE
BEGIN
IF USER IN ('USER1',
'USER2') THEN
IF sys_context('USERENV',
'IP_ADDRESS') NOT IN ('192.168.0.101',
'192.168.0.102') THEN
raise_application_error(-20000,
'Can not log in from this IP address (' ||
sys_context('USERENV',
'IP_ADDRESS') || ')');
END IF;
END IF;
END;
使用USER1用户登录,会报如下错误,禁止登录:
代码优化,只允许特定数据库用户远程登录
SQL代码如下:
CREATE OR REPLACE TRIGGER system.check_ip_addresses
AFTER logon ON DATABASE
BEGIN
DECLARE
l_count NUMBER;
BEGIN
SELECT COUNT(1)
INTO l_count
FROM cux_local_ip lip
WHERE lip.ip = sys_context('USERENV',
'IP_ADDRESS');
IF l_count = 0 THEN
SELECT COUNT(1)
INTO l_count
FROM cux_acess_remoteip_user ru
WHERE ru.user_name = USER
AND (ru.access_ip = 'ALL' OR sys_context('USERENV',
'IP_ADDRESS') = ru.access_ip);
IF l_count = 0 AND USER !='SYSTEM' THEN
raise_application_error(-20000,
'Login error,Permission denied!');
END IF;
END IF;
END;
END;
其中,
表cux_local_ip,代表本地IP,允许登录所有用户
cux_acess_remoteip_user,字段user_name代表哪些可以在对应的IP登录,access_ip为登录IP(ALL代表所有IP都能登录)