配置sqlnet.ora限制IP访问Oracle

最新推荐文章于 2024-04-29 10:29:26 发布

jiejie5945

最新推荐文章于 2024-04-29 10:29:26 发布

阅读量556

点赞数

分类专栏： lsnrctl

lsnrctl 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

--==========================
-- 配置sqlnet.ora 限制IP访问Oracle
--==========================

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

1. 实现方式
通过在sqlnet.ora文件中增加下列记录来实现

[sql] view plain copy print ?

tcp.validnode_checking = yes
tcp.invited_nodes = (hostname1, hostname2，ip1,ip2)
tcp.excluded_nodes = (10.103.11.17,hostname1,hostname2)
tcp.validnode_checking = yes                            
tcp.invited_nodes = (hostname1, hostname2，ip1,ip2)     
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. 实战演习

[sql] view plain copy print ?

-->使用tnsping demo92，连接正常
C:\>tnsping demo92
TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 25-JUN-2011 18:55:39
Used parameter files:
d:\app\Robinson\Oracle_client\product\11.2.0\client_1\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.103.11.20
9)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = demo92)))
OK (0 msec)
-->查看配置文件
[oracle@test admin]$ more sqlnet.ora
# SQLNET.ORA Network Configuration File: /oracle/92/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
NAMES.DIRECTORY_PATH= (ONAMES, TNSNAMES, HOSTNAME)
#Added by Robinson
tcp.validnode_checking = yes
tcp.excluded_nodes = (10.103.11.17)
-->重新reload
[oracle@test admin]$ lsnrctl reload listener_demo92
LSNRCTL for Linux: Version 9.2.0.8.0 - Production on 26-JUN-2011 10:03:11
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test)(PORT=1521)))
The command completed successfully
-->再次tnsping时，收到TNS-12547错误
C:\>tnsping demo92
TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 25-JUN-2011 19:01:21
Used parameter files:
d:\app\Robinson\Oracle_client\product\11.2.0\client_1\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.103.11.20
9)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = demo92)))
TNS-12547: TNS:lost contact
-->下面的演示中如果excluded_nodes与invited_nodes都存在，则invited_nodes优先，不再演示
[oracle@test admin]$ more sqlnet.ora
# SQLNET.ORA Network Configuration File: /oracle/92/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
NAMES.DIRECTORY_PATH= (ONAMES, TNSNAMES, HOSTNAME)
#Added by Robinson
tcp.validnode_checking = yes
tcp.excluded_nodes = (10.103.11.17)
tcp.invited_nodes = (10.103.11.17)

-->使用tnsping demo92，连接正常                                                                          
	C:\>tnsping demo92                                                                                     
	                                                                                                       
	TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 25-JUN-2011 18:55:39           
	Copyright (c) 1997, 2010, Oracle.  All rights reserved.                                                
	Used parameter files:                                                                                  
	d:\app\Robinson\Oracle_client\product\11.2.0\client_1\network\admin\sqlnet.ora                         
	                                                                                                       
	Used TNSNAMES adapter to resolve the alias                                                             
	Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.103.11.20   
	9)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = demo92)))                       
	OK (0 msec)		                                                                                         
                                                                                                         
-->查看配置文件                                                                                          
	[oracle@test admin]$ more sqlnet.ora                                                                   
	# SQLNET.ORA Network Configuration File: /oracle/92/network/admin/sqlnet.ora                           
	# Generated by Oracle configuration tools.                                                             
	                                                                                                       
	NAMES.DIRECTORY_PATH= (ONAMES, TNSNAMES, HOSTNAME)                                                     
	                                                                                                       
	#Added by Robinson                                                                                     
	tcp.validnode_checking = yes                                                                           
	tcp.excluded_nodes = (10.103.11.17)                                                                    
                                                                                                         
-->重新reload                                                                                            
	[oracle@test admin]$ lsnrctl reload listener_demo92                                                    
	                                                                                                       
	LSNRCTL for Linux: Version 9.2.0.8.0 - Production on 26-JUN-2011 10:03:11                              
	                                                                                                       
	Copyright (c) 1991, 2006, Oracle Corporation.  All rights reserved.                                    
	                                                                                                       
	Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test)(PORT=1521)))                             
	The command completed successfully                                                                     
                                                                                                         
-->再次tnsping时，收到TNS-12547错误                                                                      
	C:\>tnsping demo92                                                                                     
	                                                                                                       
	TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 25-JUN-2011 19:01:21           
	Copyright (c) 1997, 2010, Oracle.  All rights reserved.                                                
	Used parameter files:                                                                                  
	d:\app\Robinson\Oracle_client\product\11.2.0\client_1\network\admin\sqlnet.ora                         
	                                                                                                       
	Used TNSNAMES adapter to resolve the alias                                                             
	Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.103.11.20   
	9)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = demo92)))                       
	TNS-12547: TNS:lost contact	                                                                           
                                                                                                         
-->下面的演示中如果excluded_nodes与invited_nodes都存在，则invited_nodes优先，不再演示                    
	[oracle@test admin]$ more sqlnet.ora                                                                   
	# SQLNET.ORA Network Configuration File: /oracle/92/network/admin/sqlnet.ora                           
	# Generated by Oracle configuration tools.                                                             
	                                                                                                       
	NAMES.DIRECTORY_PATH= (ONAMES, TNSNAMES, HOSTNAME)                                                     
	                                                                                                       
	#Added by Robinson                                                                                     
	tcp.validnode_checking = yes                                                                           
	tcp.excluded_nodes = (10.103.11.17)                                                                    
	tcp.invited_nodes = (10.103.11.17)

4.使用触发器限制单用户或IP段

[sql] view plain copy print ?

-->限制单用户从单IP登录，下面限制scott用户从客户端的登录
    CREATE OR REPLACE TRIGGER disablelogin
        AFTER logon ON scott.schema    -->注意使用方式为username.schema
    DECLARE
        ipaddr VARCHAR2(30);
    BEGIN
        SELECT sys_context('userenv', 'ip_address')
        INTO   ipaddr
        FROM   dual;
        IF ipaddr = '10.103.11.17' THEN
            raise_application_error('-20001',
                        'You can not login,Please contact administrator');
        END IF;
    END disablelogin;
    /

-->限制IP段登录
    CREATE OR REPLACE TRIGGER chk_ip_range
        AFTER logon ON scott.schema
    DECLARE
        ipaddr VARCHAR2(30);
    BEGIN
        SELECT sys_context('userenv', 'ip_address')
        INTO   ipaddr
        FROM   dual;
        IF ipaddr LIKE ('10.103.11.%') THEN
            raise_application_error('-20001',
                        'You can not login,Please contact administrator');
        END IF;
    END chk_ip_range;
    /