如何禁止特定IP访问Oracle数据库 通过使用数据库服务器端的sqlnet.ora文件可以实现禁止指定IP主机访问数
如何禁止特定IP访问Oracle数据库
通过使用数据库服务器端的sqlnet.ora文件可以实现禁止指定IP主机访问数据库的功能，这对于提升数据库的安全性有很大的帮助，与此同时，这个技术为我们管理和约束数据库访问控制提供了有效的手段。下面是实现这个目的的具体步骤仅供参考：
1.默认的服务器端sqlnet.ora文件的内容：
这里我们以Oracle10.2.0.3版本为例进行简述,先来看一下当前sqlnet.ora文件内容：
?
#?This?file?is?actually?generated?by?netca.?But?if?customers?choose?to?
#?install?"Software?Only",?this?file?wont?exist?and?without?the?native?
#?authentication,?they?will?not?be?able?to?connect?to?the?database?on?NT.?
SQLNET.AUTHENTICATION_SERVICES?=?(NTS)
?
2.确认客户端的IP地址：
?
C:\Documents?and?Settings\Administrator>ipconfig?
WindowsIP?Configuration?
Ethernet?adapter?Local?Area?Connection?2:?
Media?State?.?.?.?.?.?.?.?.?.?.?.?:?Media?disconnected?
Ethernet?adapter?Local?Area?Connection:?
Connection-specific?DNS?Suffix?.?:?
IP?Address.?.?.?.?.?.?.?.?.?.?.?.?:?9.123.112.16?
Subnet?Mask?.?.?.?.?.?.?.?.?.?.?.?:?255.255.255.0?
Default?Gateway?.?.?.?.?.?.?.?.?.?:?9.123.112.1
?
3.在客户端分别使用tnsping命令和sqlplus命令来验证数据库的连通性：
?
? C:\Documents?and?Settings\Administrator>tnsping?irmdb?
TNS?Ping?Utility?for?32-bit?Windows:?Version?10.2.0.3.0?-?Production?on?06-APR-2010?11:05:09?
Copyright?(c)?1997,?2006,?Oracle.?All?rights?reserved.?
Used?parameter?files:?
C:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora?
Used?TNSNAMES?adapter?to?resolve?the?alias?
Attempting?to?contact?(DESCRIPTION?=?(ADDRESS_LIST?=?(ADDRESS?=?(PROTOCOL?=?TCP)?
(HOST?=?9.123.112.34)(PORT?=?1521)))?(CONNECT_DATA?=?(SERVICE_NAME?=?irmdb)))
OK?(20?msec)?
C:\Documents?and?Settings\Administrator>sqlplus?/nolog?
SQL*Plus:?Release?10.2.0.3.0?-?Production?on?Tue?Apr?6?11:05:12?2010?
Copyright?(c)?1982,?2006,?Oracle.?All?Rights?Reserved.?
Connected.
?
到这里说明在客户端两种方式都证明的数据库的可连通性。
4.限制客户端IP地址9.123.112.16对当前irmdb数据库的访问：
我们只需要在服务器端的sqlnet.ora文件中添加下面的内容即可。
?
#?This?file?is?actually?generated?by?netca.?But?if?customers?choose?to?
#?install?"Software?Only",?this?file?wont?exist?and?without?the?native?
#?authentication,?they?will?not?be?able?to?connect?to?the?database?on?NT.?
SQLNET.AUTHENTICATION_SERVICES?=?(NTS)?
tcp.validnode_checking=yes?
tcp.invited_nodes=(9.123.112.34)?
tcp.excluded_nodes=(9.123.112.16)
?
第一行的含义：开启IP限制功能;
第二行的含义：允许访问数据库的IP地址列表，多个IP地址使用逗号分开，此例中我们写入数据库服务器的IP地址;
第三行的含义：禁止访问数据库的IP地址列表，多个IP地址使用逗号分开，此处我们写入欲限制的IP地址9.123.112.16。
5.重新启服务器端listener后生效(这里也可以通过lsnrctl reload方式实现)：
?
C:\Documents?and?Settings\Administrator>lsnrctl?stop?
LSNRCTL?for?32-bit?Windows:?Version?10.2.0.3.0?-?Production?on?06-APR-2010?11:07:48
Copyright?(c)?1991,?2006,?Oracle.?All?rights?reserved.?
Connecting?to?(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rmesvr34.cn.ibm.com)(POR?
T=1521)))?
The?command?completed?successfully?
C:\Documents?and?Settings\Administrator>lsnrctl?start?
LSNRCTL?for?32-bit?Windows:?Version?10.2.0.3.0?-?Production?on?06-APR-2010?11:07:52
Copyright?(c)?1991,?2006,?Oracle.?All?rights?reserved.?
Starting?tnslsnr:?please?wait...?
TNSLSNR?for?32-bit?Windows:?Version?10.2.0.3.0?-?Production?
System?parameter?file?is?C:\oracle\product\10.2.0\db_1\network\admin\listener.ora?
Log?messages?written?to?C:\oracle\product\10.2.0\db_1\network\log\listener.log?
Listening?on:?(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rmesvr34.cn.ibm.com)(POR?
T=1521)))?
Connecting?to?(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rmesvr34.cn.ibm.com)(POR?
T=1521)))?
STATUS?of?the?LISTENER?
------------------------?
Alias?LISTENER?
Version?TNSLSNR?for?32-bit?Windows:?Version?10.2.0.3.0?-?Produ?
ction?
Start?Date?06-APR-2010?11:07:53?
Uptime?0?days?0?hr.?0?min.?2?sec?
Trace?Level?off?
Security?ON:?Local?OS?Authentication?
SNMP?OFF?
Listener?Parameter?File?C:\oracle\product\10.2.0\db_1\network\admin\listener.o?
ra?
Listener?Log?File?C:\oracle\product\10.2.0\db_1\network\log\listener.log?
Listening?Endpoints?Summary...?
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rmesvr34.cn.ibm.com)(PORT=1521)))?
Services?Summary...?
Service?"PLSExtProc"?has?1?instance(s).?
Instance?"PLSExtProc",?status?UNKNOWN,?has?1?handler(s)?for?this?service...?
The?command?completed?successfully?
C:\Documents?and?Settings\Administrator>sqlplus?/nolog?
SQL*Plus:?Release?10.2.0.3.0?-?Production?on?Tue?Apr?6?11:07:57?2010?
Copyright?(c)?1982,?2006,?Oracle.?All?Rights?Reserved.?
SQL>?conn?/?as?sysdba;?
Connected.?
SQL>?alter?system?register;?
System?altered.?
SQL>?quit?
Disconnected?from?Oracle?Database?10g?Enterprise?Edition?Release?10.2.0.3.0?-?Pr?
oduction?
With?the?Partitioning,?OLAP?and?Data?Mining?options?
C:\Documents?and?Settings\Administrator>lsnrctl?status?
LSNRCTL?for?32-bit?Windows:?Version?10.2.0.3.0?-?Production?on?06-APR-2010?11:08:05
Copyright?(c)?1991,?2006,?Oracle.?All?rights?reserved.?
Connecting?to?(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rmesvr34.cn.ibm.com)(PORT=1521)))?
STATUS?of?the?LISTENER?
------------------------?
Alias?LISTENER?
Version?TNSLSNR?for?32-bit?Windows:?Version?10.2.0.3.0?-?Produ?
ction?
Start?Date?06-APR-2010?11:07:53?
Uptime?0?days?0?hr.?0?min.?12?sec?
Trace?Level?off?
Security?ON:?Local?OS?Authentication?
SNMP?OFF?
Listener?Parameter?File?C:\oracle\product\10.2.0\db_1\network\admin\listener.ora?
Listener?Log?File?C:\oracle\product\10.2.0\db_1\network\log\listener.log?
Listening?Endpoints?Summary...?
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rmesvr34.cn.ibm.com)(PORT=1521)))?
Services?Summary...?
Service?"PLSExtProc"?has?1?instance(s).?
Instance?"PLSExtProc",?status?UNKNOWN,?has?1?handler(s)?for?this?service...?
Service?"irmdb"?has?1?instance(s).?
Instance?"irmdb",?status?READY,?has?1?handler(s)?for?this?service...?
Service?"irmdb_XPT"?has?1?instance(s).?
Instance?"irmdb",?status?READY,?has?1?handler(s)?for?this?service...?
The?command?completed?successfully
?
6.在客户端(9.123.112.16)分别再次使用tnsping命令和sqlplus命令来验证数据库的连通性：
?
C:\Documents?and?Settings\Administrator>tnsping?irmdb?
TNS?Ping?Utility?for?32-bit?Windows:?Version?10.2.0.3.0?-?Production?on?06-APR-2010?11:09:20?
Copyright?(c)?1997,?2006,?Oracle.?All?rights?reserved.?
Used?parameter?files:?
C:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora?
Used?TNSNAMES?adapter?to?resolve?the?alias?
Attempting?to?contact?(DESCRIPTION?=?(ADDRESS_LIST?=?(ADDRESS?=?(PROTOCOL?=?TCP)?
(HOST?=?9.123.112.34)(PORT?=?1521)))?(CONNECT_DATA?=?(SERVICE_NAME?=?irmdb)))
TNS-12537:?TNS:connection?closed?
C:\Documents?and?Settings\Administrator>sqlplus?/nolog?
SQL*Plus:?Release?10.2.0.3.0?-?Production?on?Tue?Apr?6?11:09:23?2010?
Copyright?(c)?1982,?2006,?Oracle.?All?Rights?Reserved.?
ERROR:?
ORA-12537:?TNS:connection?closed
?
到这里我们就可以证明，通过修改sqlnet.ora的方法，我们实现了限制指定机器IP访问数据库的功能。
最后需要特别注意的是tcp.invited_nodes允许列表的优先级要高于tcp.excluded_nodes受限IP列表。也就是说如果同一个IP地址如果同时出现在两个列表里的话，那么这个IP地址还是可以访问对应的数据库的。
