oracle巧用触发器提高数据库安全级别

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业务用户会报错。


以上结果均经过本人测试，生产慎用！

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26442936/viewspace-1318991/，如需转载，请注明出处，否则将追究法律责任。