现。该文件通常$ORACLE_HOME/network/admin/目录下,与tnsnames.ora以及listener.ora位于同一路径。用法也比较简单。通过监听器的
限制,实现轻量级访问限制,比在数据库内部通过触发器进行限制效率要高。
1. 实现方式
通过在sqlnet.ora文件中增加下列记录来实现
点击(此处)折叠或打开
- [sql] view plaincopyprint?
- 01.tcp.validnode_checking = yes
- 02.tcp.invited_nodes = (hostname1, hostname2,ip1,ip2)
- 03.tcp.excluded_nodes = (10.103.11.17,hostname1,hostname2)
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 plaincopyprint?
- 01.-->使用tnsping demo92,连接正常
- 02. C:\>tnsping demo92
- 03.
- 04. TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 25-JUN-2011 18:55:39
- 05. Copyright (c) 1997, 2010, Oracle. All rights reserved.
- 06. Used parameter files:
- 07. d:\app\Robinson\Oracle_client\product\11.2.0\client_1\network\admin\sqlnet.ora
- 08.
- 09. 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段
点击(此处)折叠或打开
- [sql] view plaincopyprint?
- 01.-->限制单用户从单IP登录,下面限制scott用户从客户端的登录
- 02. CREATE OR REPLACE TRIGGER disablelogin
- 03. AFTER logon ON scott.schema -->注意使用方式为username.schema
- 04. DECLARE
- 05. ipaddr VARCHAR2(30);
- 06. BEGIN
- 07. SELECT sys_context('userenv', 'ip_address')
- 08. INTO ipaddr
- 09. 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. /