通过sqlnet.ora限制访问数据库的地址_sqlnet.ora 限制地址-CSDN博客

本文链接：https://blog.csdn.net/shaochenshuo/article/details/42238603

本文详细介绍了如何通过SQLNET.ORA文件实现对数据库访问地址的限制，包括参数设置、注意事项以及测试过程。重点展示了如何配置TCP参数以限制或允许特定IP地址访问数据库，并通过实例演示了限制生效的过程。

一： sqlnet.ora文件可以通过不同的参数实现很多功能，本篇博客主要讲一下通过sqlnet.ora对访问数据库的地址做限制。要实现限制ip地址功能，我们可以使用如下参数：

tcp.validnode_checking=yes ##是否启用ip地址限制功能
tcp.invited_nodes=(ip1,ip2......) ##指定可以访问数据库的ip地址，多个ip地址间以英文逗号分隔
tcp.excluded_nodes=(ip1,ip2......) ##指定不可以访问数据库的ip地址

二：通过sqlnet.ora对访问数据库的ip地址作限制有如下注意事项

1) 注意一定要允许数据库服务器本机地址访问，如果是RAC那么RAC所有节点的ip地址都需要允许访问(且RAC每一个节点的sqlnet.ora文件都需要配置)

2)一般情况下tcp.invited_nodes，tcp.excluded_nodes只同时使用其中一个，如果两个参数被同时使用，tcp.invited_nodes参数具有较高优先级。

3) 配置过sqlnet.ora 文件需要重启监听才能生效

4) tcp.invited_nodes,tcp.excluded_nodes参数指定ip地址时支持例如“192.168.88.* ”使用通配符的写法

三：下面是我的测试情况

在某台TEST远程主机的$ORACLE_HOME/network/admin/tnsnames.ora文件写入如下两个连接串

test2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.48.224)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = egapdr)
    )
  )
  
test5 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.48.222)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = egapdr)
    )
  )

##其中192.168.48.224为RAC数据库的scan_ip，192.168.48.222为RAC数据库的vip

在未限制之前两个连接串都能正常连上数据库

sqlplus sys/******test2 as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 29 14:59:53 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL>

sqlplus sys/******@test5 as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 29 14:56:49 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL>

在RAC数据库的两个节点上修改sqlnet.ora文件对远程主机TEST的地址作出限制，如果不重启监听所做的限制不会生效(这个我们就不试了，有兴趣的可以自己试一下)。此处我们来重启一下监听然后再试试

su - grid

lsnrctl stop

lsnrctl start

lsnrctl status

重启后再次尝试使用test2及test5两个连接串连接数据库

test5(vip)

sqlplus sys/*****@test5 as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 29 15:00:37 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

ERROR:
ORA-12537: TNS:connection closed

test2(scan_ip)

sqlplus sys/******@test2 as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 29 14:59:53 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL>

##从上面的测试结果我们可以发现，使用vip的连接串test5限制已经生效，而使用SCAN_IP的连接串test2限制未生效。其实原因很简单上面我们只是lsnrctl stop,lsnrctl start 重启了本地监听，而有重启SCAN LISTENER

重启 SCAN LISTENER 后再次测试

lsnrctl stop LISTENER_SCAN1

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production on 29-DEC-2014 15:00:43

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))
The command completed successfully

lsnrctl start LISTENER_SCAN1

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production on 29-DEC-2014 15:00:52

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Starting /apps/oracle/11.2.0.3/grid/bin/tnslsnr: please wait...

TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
System parameter file is /apps/oracle/11.2.0.3/grid/network/admin/listener.ora
Log messages written to /apps/oracle/11.2.0.3/grid/log/diag/tnslsnr/hostdb1/listener_scan1/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_SCAN1
Version                   TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
Start Date                29-DEC-2014 15:00:52
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      ON
Listener Parameter File   /apps/oracle/11.2.0.3/grid/network/admin/listener.ora
Listener Log File         /apps/oracle/11.2.0.3/grid/log/diag/tnslsnr/hostdb1/listener_scan1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))
The listener supports no services
The command completed successfully

使用test5连接串显示ip地址限制

tnsping test5

TNS Ping Utility for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production on 29-DEC-2014 15:06:41

Copyright (c) 1997, 2011, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.48.222)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = egapdr)))
TNS-12537: TNS:connection closed


sqlplus sys/******@test2 as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 29 15:00:37 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

ERROR:
ORA-12537: TNS:connection closed

使用test2连接显示ip地址限制

tnsping test2

TNS Ping Utility for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production on 29-DEC-2014 15:06:41

Copyright (c) 1997, 2011, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.48.224)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = egapdr)))
TNS-12537: TNS:connection closed


sqlplus sys/******@test2 as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 29 15:00:37 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

ERROR:
ORA-12537: TNS:connection closed

##如果你的应该连接串使用的SCAN_IP或者SCAN_IP的域名，那么使用sqlnet.ora作地址限制的时候一定要记得重启几个SCAN LISTENER(使用DNS 会有三个SCAN_IP，几个SCAN LISTENER都需要重启)。

##有一种情况，如果我们在已经作了ip地址限定，现在想放开某个ip地址，正确修改sqlnet.ora文件后，如果我们只重启了SCAN LISTENER没有重启本地监听那么该ip还是不能访问数据库(因为SCAN LISTENER是把连接请求发给本地监听的)，所以如果是RAC 本地监听和SCAN LISTENER 都需要重启

##通过监听器的限制，通常属于轻量级，比在数据库内部通过触发器进行限制效率要高。