oracle巧用触发器提高数据库安全级别
导读:数据库的安全越来越被企业所重视。用户直接登录数据库又是风险级别最高的,所以对程序用户、业务用户限制性登录又显得格外重要。下面直接干货,告诉你怎么用触发器限制用户登录。
SYS@doudou2> select * from v$version;
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业务用户会报错。
以上结果均经过本人测试,生产慎用!
导读:数据库的安全越来越被企业所重视。用户直接登录数据库又是风险级别最高的,所以对程序用户、业务用户限制性登录又显得格外重要。下面直接干货,告诉你怎么用触发器限制用户登录。
SYS@doudou2> select * from v$version;
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业务用户会报错。
以上结果均经过本人测试,生产慎用!
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26442936/viewspace-1318991/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26442936/viewspace-1318991/