配置sqlnet.ora限制IP访问Oracle

与防火墙类似的功能,Oracle 提供限制与允许特定的IP或主机名通过Oracle Net来访问数据库。这个功能由sqlnet.ora配置文件来实
现。该文件通常$ORACLE_HOME/network/admin/ 目录下,与tnsnames.ora以及listener.ora位于同一路径。用法也比较简单。通过监听器的
限制,实现轻量级访问限制,比在数据库内部通过触发器进行限制效率要高。

1. 实现方式
    通过在sqlnet.ora文件中增加下列记录来实现      
  1. tcp.validnode_checking = yes                              
  2. tcp.invited_nodes = (hostname1, hostname2,ip1,ip2)       
  3. tcp.excluded_nodes = (10.103.11.17,hostname1,hostname2)   
    当使用invited_nodes时,则所有没有包含在invited_nodes值中的IP或主机将无法通过Oracel Net连接到数据库。而如果使用
    excluded_nodes时,除了excluded_nodes值中列出的IP和主机不可访问之外,其余的节点都可以访问数据库。通常情况下,更倾向于使
    用excluded_nodes参数。

2. 注意事项
    使用excluded_nodes与invited_nodes的一些特性
        不支持通配符的使用(如hostname不能写为svhs0*,IP地址不能写为10.103.11.*)
        excluded_nodes与invited_nodes为互斥方式,要么使用前者,要么使用后者
        如果tcp.invited_nodes与tcp.excluded_nodes都存在,则tcp.invited_nodes优先
        要将本地地址,或者Cluster群集其他节点的地址都加入到允许列表,否则监听器可能无法启动
        修改之后,一定要重起监听或reload才能生效,而不需要重新启动数据库 
        仅提供对TCP/IP协议的支持

3. 实战演习    
  1. -->使用tnsping demo92,连接正常                                                                            
  2.     C:\>tnsping demo92                                                                                       
  3.                                                                                                              
  4.     TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 25-JUN-2011 18:55:39             
  5.     Copyright (c) 1997, 2010, Oracle.  All rights reserved.                                                  
  6.     Used parameter files:                                                                                    
  7.     d:\app\Robinson\Oracle_client\product\11.2.0\client_1\network\admin\sqlnet.ora                           
  8.                                                                                                              
  9.     Used TNSNAMES adapter to resolve the alias                                                               
  10.     Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.103.11.20     
  11.     9)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = demo92)))                         
  12.     OK (0 msec)                                                                                                
  13.                                                                                                            
  14. -->查看配置文件                                                                                            
  15.     [oracle@test admin]$ more sqlnet.ora                                                                     
  16.     # SQLNET.ORA Network Configuration File: /oracle/92/network/admin/sqlnet.ora                             
  17.     # Generated by Oracle configuration tools.                                                               
  18.                                                                                                              
  19.     NAMES.DIRECTORY_PATH= (ONAMES, TNSNAMES, HOSTNAME)                                                       
  20.                                                                                                              
  21.     #Added by Robinson                                                                                       
  22.     tcp.validnode_checking = yes                                                                             
  23.     tcp.excluded_nodes = (10.103.11.17)                                                                      
  24.                                                                                                            
  25. -->重新reload                                                                                              
  26.     [oracle@test admin]$ lsnrctl reload listener_demo92                                                      
  27.                                                                                                              
  28.     LSNRCTL for Linux: Version 9.2.0.8.0 - Production on 26-JUN-2011 10:03:11                                
  29.                                                                                                              
  30.     Copyright (c) 1991, 2006, Oracle Corporation.  All rights reserved.                                      
  31.                                                                                                              
  32.     Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test)(PORT=1521)))                               
  33.     The command completed successfully                                                                       
  34.                                                                                                            
  35. -->再次tnsping时,收到TNS-12547错误                                                                        
  36.     C:\>tnsping demo92                                                                                       
  37.                                                                                                              
  38.     TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 25-JUN-2011 19:01:21             
  39.     Copyright (c) 1997, 2010, Oracle.  All rights reserved.                                                  
  40.     Used parameter files:                                                                                    
  41.     d:\app\Robinson\Oracle_client\product\11.2.0\client_1\network\admin\sqlnet.ora                           
  42.                                                                                                              
  43.     Used TNSNAMES adapter to resolve the alias                                                               
  44.     Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.103.11.20     
  45.     9)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = demo92)))                         
  46.     TNS-12547: TNS:lost contact                                                                              
  47.                                                                                                            
  48. -->下面的演示中如果excluded_nodes与invited_nodes都存在,则invited_nodes优先,不再演示                      
  49.     [oracle@test admin]$ more sqlnet.ora                                                                     
  50.     # SQLNET.ORA Network Configuration File: /oracle/92/network/admin/sqlnet.ora                             
  51.     # Generated by Oracle configuration tools.                                                               
  52.                                                                                                              
  53.     NAMES.DIRECTORY_PATH= (ONAMES, TNSNAMES, HOSTNAME)                                                       
  54.                                                                                                              
  55.     #Added by Robinson                                                                                       
  56.     tcp.validnode_checking = yes                                                                             
  57.     tcp.excluded_nodes = (10.103.11.17)                                                                      
  58.     tcp.invited_nodes = (10.103.11.17)                                                                       
4.使用触发器限制单用户或IP段    
  1. -->限制单用户从单IP登录,下面限制scott用户从客户端的登录                      
  2.     CREATE OR REPLACE TRIGGER disablelogin                                      
  3.         AFTER logon ON scott.schema    -->注意使用方式为username.schema           
  4.     DECLARE                                                                     
  5.         ipaddr VARCHAR2(30);                                                      
  6.     BEGIN                                                                       
  7.         SELECT sys_context('userenv''ip_address')                               
  8.         INTO   ipaddr                                                             
  9.         FROM   dual;                                                              
  10.         IF ipaddr = '10.103.11.17' THEN                                           
  11.             raise_application_error('-20001',                                       
  12.                         'You can not login,Please contact administrator');                
  13.         END IF;                                                                   
  14.     END disablelogin;                                                           
  15.     /                                                                           
  16.                                                                               
  17. -->限制IP段登录                                                               
  18.     CREATE OR REPLACE TRIGGER chk_ip_range                                      
  19.         AFTER logon ON scott.schema                                               
  20.     DECLARE                                                                     
  21.         ipaddr VARCHAR2(30);                                                      
  22.     BEGIN                                                                       
  23.         SELECT sys_context('userenv''ip_address')                               
  24.         INTO   ipaddr                                                             
  25.         FROM   dual;                                                              
  26.         IF ipaddr LIKE ('10.103.11.%'THEN                                       
  27.             raise_application_error('-20001',                                       
  28.                         'You can not login,Please contact administrator');                
  29.         END IF;                                                                   
  30.     END chk_ip_range;                                                           
  31.     /                  
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值