使用Oracle的sqlnet.ora文件可以实现禁止指定IP主机访问数据库功能，这对于提升数据库的安全性有很大的帮助，与此同时，这个技术为我们管理和约束数据库访问控制提供了有效的手段。
简单记录一下，供参考。

1.默认sqlnet.ora内容
这里我们以Oracle 11.2.0.1.0版本为例进行探索。
secooler@secDB /home/oracle$ vi $ORACLE_HOME/network/admin/sqlnet.ora
# sqlnet.ora Network Configuration File: /oracle/ora11gR2/product/11.2.0/dbhome_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

ADR_BASE = /oracle/ora11gR2

~

2.确认本机的IP地址
C:\>ipconfig

Windows IP Configuration

Ethernet adapter 本地连接:

   Connection-specific DNS Suffix . :
   Link-local IPv6 Address . . . . . : fe80::fd5c:c9ef:9a92:5c48%12
   IPv4 Address. . . . . . . . . . . : 10.66.28.206
   Subnet Mask . . . . . . . . . . . : 255.255.255.192
   Default Gateway . . . . . . . . . : 10.66.28.245

3.使用tnsping命令和sqlplus命令验证数据库的连接性
1）使用tnsping命令验证服务名是否可用
C:\>tnsping secooler

TNS Ping Utility for 32-bit Windows: Version 10.2.0.3.0 - Production on 28-MAR-2010 18:56:58

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 = (PROTOCOL = TCP)(HOST = 144.127.192.136)(PORT = 1526)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = secooler)))
OK (0 msec)

tnsping命令验证通过。

2）使用sqlplus尝试连接数据库测试
C:\>sqlplus sec/sec@secooler

SQL*Plus: Release 10.2.0.3.0 - Production on Sun Mar 28 18:57:02 2010

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

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

sec@secooler>

OK，成功连接。

4.限制IP地址10.66.28.206对数据库的访问
在sqlnet.ora文件中添加如下内容：
tcp.validnode_checking=yes
tcp.invited_nodes=(144.127.192.136)
tcp.excluded_nodes=(10.66.28.206)

第一行的含义：启用IP限制功能；
第二行的含义：允许访问数据库的IP地址列表，多个IP地址使用逗号分开，此例中我们写入数据库服务器的IP地址；
第三行的含义：禁止访问数据库的IP地址列表，多个IP地址使用逗号分开，此处我们写入欲限制的IP地址 10.66.28.206。

5.重新启动监听后生效
1）停止监听
secooler@secDB /home/oracle$ lsnrctl stop

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 28-MAR-2010 19:15:21

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

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

2）启动监听
secooler@secDB /home/oracle$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 28-MAR-2010 19:15:30

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

Starting /oracle/ora11gR2/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /oracle/ora11gR2/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /oracle/ora11gR2/diag/tnslsnr/secDB/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=secDB)(PORT=1526)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                28-MAR-2010 19:15:30
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/ora11gR2/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /oracle/ora11gR2/diag/tnslsnr/secDB/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=secDB)(PORT=1526)))
The listener supports no services
The command completed successfully

3）查看监听状态，确保监听启动成功
secooler@secDB /home/oracle$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 28-MAR-2010 19:16:40

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                28-MAR-2010 19:15:30
Uptime                    0 days 0 hr. 1 min. 9 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/ora11gR2/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /oracle/ora11gR2/diag/tnslsnr/secDB/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=secDB)(PORT=1526)))
Services Summary...
Service "secooler" has 1 instance(s).
Instance "secooler", status READY, has 2 handler(s) for this service...
The command completed successfully

OK，到此监听重新启动完成。

6.再次尝试使用tnsping和sqlplus 命令验证数据库的连接性
1）使用tnsping命令验证secooler服务名的连接性
C:\>tnsping secooler

TNS Ping Utility for 32-bit Windows: Version 10.2.0.3.0 - Production on 28-MAR-2010 19:08: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 = (PROTOCOL = TCP)(HOST = 144.127.192.136)(PORT = 1526)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = secooler)))
TNS-12547: TNS:lost contact

此时提示“lost contact”，已经丢失连接。

2）使用sqlplus命令验证数据库的链接
C:\>sqlplus sec/sec@secooler

SQL*Plus: Release 10.2.0.3.0 - Production on Sun Mar 28 19:08:23 2010

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

ERROR:
ORA-12547: TNS:lost contact

Enter user-name:

同样的ORA-12547提示信息，丢失连接。

可见，此时我们已经达到限制IP地址为10.66.28.206客户端访问数据库的请求。

7.尝试将IP地址同时写入到tcp.invited_nodes 和tcp.excluded_nodes
如果我们将一个IP地址既写入到tcp.invited_nodes列表中，又写入到tcp.excluded_nodes列表中，看一下效果。
secooler@secDB /home/oracle$ vi $ORACLE_HOME/network/admin/sqlnet.ora
# sqlnet.ora Network Configuration File: /oracle/ora11gR2/product/11.2.0/dbhome_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

ADR_BASE = /oracle/ora11gR2

tcp.validnode_checking=yes
tcp.invited_nodes=(144.127.192.136,10.66.28.206)
tcp.excluded_nodes=(10.66.28.206)

~

重新启动监听（这里省略重启过程）。

再次使用tnsping和sqlplus验证连接性。
C:\>tnsping secooler

TNS Ping Utility for 32-bit Windows: Version 10.2.0.3.0 - Production on 28-MAR-2010 19:21:53

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 = (PROTOCOL = TCP)(HOST = 144.127.192.136)(PORT = 1526)) (CONNECT_DATA = (SERVER = DE
DICATED) (SERVICE_NAME = secooler)))
OK (0 msec)

C:\>sqlplus sec/sec@secooler

SQL*Plus: Release 10.2.0.3.0 - Production on Sun Mar 28 19:21:59 2010

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

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

经验证，此时没有对10.66.28.206地址进行限制，也就是说，tcp.invited_nodes允许列表的优先级要高于tcp.excluded_nodes受限IP列表。

8.小结
在使用这个技术对具体IP进行限制的时候，一定要充分意识到修改后对系统造成的影响。
在任何生产系统上做调整之前，一定要在测试环境下做好充分的测试。

Good luck.

secooler
10.03.28

-- The End --

【LISTENER】使用sqlnet.ora禁止特定IP访问数据库