--========================== -- 配置sqlnet.ora 限制IP访问Oracle --==========================
与防火墙类似的功能，Oracle 提供限制与允许特定的IP或主机名通过Oracle Net来访问数据库。这个功能由sqlnet.ora配置文件来实 现。该文件通常$ORACLE_HOME/network/admin/ 目录下，与tnsnames.ora以及listener.ora位于同一路径。用法也比较简单。通过监听器的 限制，实现轻量级访问限制，比在数据库内部通过触发器进行限制效率要高。
1. 实现方式 通过在sqlnet.ora文件中增加下列记录来实现
- 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. 实战演习
- -->使用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)
-->使用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段
- -->限制单用户从单IP登录，下面限制scott用户从客户端的登录
- CREATEORREPLACETRIGGER 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段登录
- CREATEORREPLACETRIGGER 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;
- /
-->限制单用户从单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; /
5.更多参考
http://psoug.org/reference/net_services.htmlhttp://forums.oracle.com/forums/thread.jspa?messageID=4566449
