使用tcp.validnode_checking允许、限制机器访问数据库,在$OREACLE_HOME/network/admin下直接修改sqlnet.ora文件,增加如下内容:
tcp.validnode_checking=yes
#允许访问的ip
tcp.invited_nodes=(ip1,ip2,……)
#不允许访问的ip
tcp.excluded_nodes=(ip1,ip2,……)
修改sqlnet.ora后,重新启动listener服务。如果不允许的IP客户端连接过来,会出现以下错误:
[oracle@Database1 admin]$ tnsping oradb
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 06-JUN-2018 06:47:32
Copyright (c) 1997, 2013, 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.57.129)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = oradb)))
TNS-12547: TNS:lost contact
ORACLE的Valid node checking(TCP.VALIDNODE_CHECKING)是监听的一个功能,可以用于允许或者拒绝指定的IP地址连接数据库,可以把成防火墙在1521端口上面的一个规则。
Valid node checking可以定义2个列表,一个是INVITEDNODES列表,指定监听允许连接数据库的IP地址或者是主机名。一个是EXCLUDED_NODES列表,指定监听不允许连接数据库的IP地址或者是主机名。
下面是测试案例:
(1)在主机Database1的Tnsname配置要远程登入数据库的网络名
[oracle@Database1 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORADB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.57.129)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = oradb)
)
)
(2)在要远程登入的数据库的$ORACLE_HOME/network/admin下面使用vim编辑一个sqlnet.ora文件,在里面加上参数制定不予许远程登入数据库的ip,即Database1的ip
[oracle@Database2 ~]$ cd /u01/app/oracle/product/11.2.0/db_1/network/admin/
[oracle@Database2 admin]$ ls
listener.ora samples shrept.lst sqlnet.ora tnsnames.ora
[oracle@Database2 admin]$ cat sqlnet.ora (加上下面标红的两行,即开启VALIDNODE_CHECKING功能,同时限制连接数据库的Ip)
TCP.VALIDNODE_CHECKING=yes
TCP.EXCLUDED_NODES=(192.168.57.128)
(3)在远程登入数据库配置好sqlnet.ora文件之后,将监听关闭然后重启监听
[oracle@Database2 admin]$ lsnrctl stop
[oracle@Database2 admin]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 16-JUL-2018 20:33:58
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/Database2/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Database2)(PORT=1521)))
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 16-JUL-2018 20:33:58
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/Database2/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Database2)(PORT=1521)))
Services Summary...
Service "oradba" has 1 instance(s).
Instance "oradb", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
(4)避免动态注册监听需要一段时间,手工动态将服务注册到监听
[oracle@Database2 admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Jul 16 20:34:14 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter system register;
System altered.
SQL> exit;
(5)在主机Database1上面测试远程登入Database2,同时观察Database2上面监听日志
[oracle@Database1 admin]$ sqlplus system/system@oradb
SQL*Plus: Release 11.2.0.4.0 Production on Wed Jun 6 06:11:11 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
ERROR:
ORA-12547: TNS:lost contact
在远程要登入的数据库上面查看监听日志信息
[oracle@Database2 trace]$ tail -f listener.log
16-JUL-2018 20:34:23 * service_update * oradb * 0
Incoming connection from 192.168.57.128 rejected
16-JUL-2018 20:34:28 * 12546
TNS-12546: TNS:permission denied
TNS-12560: TNS:protocol adapter error
TNS-00516: Permission denied
可以看到拒绝了远程登入
总结:可以在sqlnet.ora来限制登入数据库主机的IP地址