触发器限制指定IP访问oracle数据库

1. 触发器限制指定IP访问oracle数据库
   
2. ---------------------来自德哥的BLOG，觉着很有用，记录一下-------------------
   
3. 
   
4. 最近有个项目需要限制某些数据库用户的访问来源IP，在PG中比较好实现，但是ORACLE没有比较简便的操作。
   
5. 如果不管用户的话，仅仅限制来源IP对监听的访问是比较容易实现的，通过配置数据库服务器的sqlnet.ora文件或者修改数据库服务器的IPTABLES等手段实现。
   
6. sqlnet.ora范例:
   
7. tcp.validnode_checking=yes
   
8. tcp.invited_nodes=(172.16.33.11,172.16.34.89)
   
9. 
   
10. iptables范例:
    
11. [root@kefu ~]# cat /etc/sysconfig/iptables
    
12. # Firewall configuration written by system-config-securitylevel
    
13. # Manual customization of this file is not recommended.
    
14. *filter
    
15. :INPUT ACCEPT [0:0]
    
16. :FORWARD ACCEPT [0:0]
    
17. :OUTPUT ACCEPT [0:0]
    
18. :RH-Firewall-1-INPUT - [0:0]
    
19. -A INPUT -j RH-Firewall-1-INPUT
    
20. -A FORWARD -j RH-Firewall-1-INPUT
    
21. # 允许访问1521的服务器
    
22. -A RH-Firewall-1-INPUT -s 172.16.3.68/32 -m state --state NEW -m tcp -p tcp --dport 1521 -j ACCEPT
    
23. -A RH-Firewall-1-INPUT -i lo -j ACCEPT
    
24. -A RH-Firewall-1-INPUT -p icmp --icmp-type any -j ACCEPT
    
25. -A RH-Firewall-1-INPUT -p 50 -j ACCEPT
    
26. -A RH-Firewall-1-INPUT -p 51 -j ACCEPT
    
27. -A RH-Firewall-1-INPUT -p udp --dport 5353 -d 224.0.0.251 -j ACCEPT
    
28. -A RH-Firewall-1-INPUT -p udp -m udp --dport 631 -j ACCEPT
    
29. -A RH-Firewall-1-INPUT -p tcp -m tcp --dport 631 -j ACCEPT
    
30. -A RH-Firewall-1-INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT
    
31. -A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 22 -j ACCEPT
    
32. -A RH-Firewall-1-INPUT -j REJECT --reject-with icmp-host-prohibited
    
33. COMMIT
    
34. 
    
35. 下面来看看如何限制特定用户和特定IP：
    
36. 
    
37. 1. 创建ACL表 (本例将ACL表建立在dsm用户下,随便建哪里都可以)
    
38. create table dsm.tbl_iplimit (logonuser varchar2(32),ip_address varchar2(15),remark varchar2(64),create_time date default sysdate);
    
39. insert into dsm.tbl_iplimit values ('DSM','172.16.18.81','digoal''s host.',sysdate);
    
40. insert into dsm.tbl_iplimit values ('DSM','local','本地',sysdate);
    
41. commit;
    
42. 这里限制了DSM用户只能从172.16.18.81和ORACLE所在服务器登录.其他用户不受限制.
    
43. 
    
44. 2. 创建触发器
    
45. conn / as sysdba
    
46. create or replace trigger "logon_audit" after
    
47. logon on database
    
48. declare
    
49. record_num number;
    
50. userip varchar2(15);
    
51. isforbidden boolean:=true;
    
52. begin
    
53.   userip:=nvl(sys_context ('userenv','ip_address'),'local');
    
54.   select count(*) into record_num from dsm.tbl_iplimit where logonuser=user;
    
55.   if (record_num>0) then
    
56.       select count(*) into record_num from dsm.tbl_iplimit where logonuser=user and ip_address=userip;
    
57.       if (record_num=0) then
    
58.       raise_application_error(-20003,'ip :'||userip||' is forbided');
    
59.       end if;
    
60.   end if;
    
61. exception
    
62.  when value_error then
    
63.   sys.dbms_output.put_line('exception handed');
    
64.  when others then
    
65.   raise;
    
66. end logon_audit;
    
67. /

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

转载于:http://blog.itpub.net/28878983/viewspace-2133909/