链接:http://www.eygle.com/archives/2008/01/sqlnetora_ip_limit.html
在Oracle数据库中,我们可以通过SQLNET.ora文件实现地址访问限制。 在SQLNET.ora文件中设置以下参数可以实现IP访问限制:
tcp.validnode_checking=yes tcp.invited_nodes=(ip1,ip2......) tcp.excluded_nodes=(ip1,ip2......)
在未设置这些参数前,测试数据库可以正常访问:
D:/>tnsping eygle TNS Ping Utility for 32-bit Windows: Version 10.2.0.3.0 - Production on 28-1月 -2008 14:52:52 Copyright (c) 1997, 2006, Oracle. All rights reserved. 已使用的参数文件,: C:/oracle/10.2.0/network/admin/sqlnet.ora 已使用 TNSNAMES 适配器来解析别名 Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.33.11)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = eygle))) OK (30 毫秒)
当设置参数之后:
[oracle@jumper admin]$ cat sqlnet.ora # SQLNET.ORA Network Configuration File: /opt/oracle/product/9.2.0/network/admin/sqlnet.ora # Generated by Oracle configuration tools. NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME) tcp.validnode_checking=yes tcp.invited_nodes=(172.16.33.11,172.16.34.89)
重新启动监听器使设置生效:
[oracle@jumper admin]$ lsnrctl start LSNRCTL for Linux: Version 9.2.0.4.0 - Production on 28-JAN-2008 14:42:01 Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved. Starting /opt/oracle/product/9.2.0/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 9.2.0.4.0 - Production System parameter file is /opt/oracle/product/9.2.0/network/admin/listener.ora Log messages written to /opt/oracle/product/9.2.0/network/log/listener.log Trace information written to /opt/oracle/product/9.2.0/network/trace/listener.trc Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.33.11)(PORT=1521))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.33.11)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 9.2.0.4.0 - Production Start Date 28-JAN-2008 14:42:01 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level support Security ON SNMP OFF Listener Parameter File /opt/oracle/product/9.2.0/network/admin/listener.ora Listener Log File /opt/oracle/product/9.2.0/network/log/listener.log Listener Trace File /opt/oracle/product/9.2.0/network/trace/listener.trc Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.33.11)(PORT=1521))) Services Summary... Service "eygle" has 1 instance(s). Instance "eygle", status UNKNOWN, has 1 handler(s) for this service... Service "julia" has 1 instance(s). Instance "eygle", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully
我们再来看客户端的访问:
D:/>tnsping eygle TNS Ping Utility for 32-bit Windows: Version 10.2.0.3.0 - Production on 28-1月 -2008 14:53:19 Copyright (c) 1997, 2006, Oracle. All rights reserved. 已使用的参数文件: C:/oracle/10.2.0/network/admin/sqlnet.ora 已使用 TNSNAMES 适配器来解析别名 Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.33.11)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = eygle))) TNS-12547: TNS: 丢失连接
需要注意的是一定要将本地地址,或者Cluster群集其他节点的地址都加入到允许列表,否则监听器可能无法启动。 修改参数之后,重启监听器设置即可生效。 通过监听器的限制,通常属于轻量级,比在数据库内部通过触发器进行限制效率要高。