透過SQLNET.ora檔案限制Ip地址訪問

在Oracle資料庫中，我們可以透過SQLNET.ora檔案實現地址訪問限制。

在SQLNET.ora檔案中設定以下引數可以實現IP訪問限制：[@more@]

tcp.validnode_checking=yes
tcp.invited_nodes=(ip1,ip2......)
tcp.excluded_nodes=(ip1,ip2......)

在未設定這些引數前，測試資料庫可以正常訪問：

D:>tnsping eygle

TNS Ping Utility for 32-bit Windows: Version 10.2.0.3.0 - Production on 28-1月 -2008 14:52:52

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

已使用的引數檔案，:
C:oracle10.2.0networkadminsqlnet.ora

已使用 TNSNAMES 介面卡來解析別名
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.33.11)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = eygle)))
OK (30 毫秒)

當設定引數之後：

[oracle@jumper admin]$ cat sqlnet.ora
# SQLNET.ORA Network Configuration File: /opt/oracle/product/9.2.0/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)

tcp.validnode_checking=yes
tcp.invited_nodes=(172.16.33.11,172.16.34.89)

重新啟動監聽器使設定生效：

[oracle@jumper admin]$ lsnrctl start

LSNRCTL for Linux: Version 9.2.0.4.0 - Production on 28-JAN-2008 14:42:01
Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved.
Starting /opt/oracle/product/9.2.0/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 9.2.0.4.0 - Production
System parameter file is /opt/oracle/product/9.2.0/network/admin/listener.ora
Log messages written to /opt/oracle/product/9.2.0/network/log/listener.log
Trace information written to /opt/oracle/product/9.2.0/network/trace/listener.trc
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.33.11)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.33.11)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 9.2.0.4.0 - Production
Start Date 28-JAN-2008 14:42:01
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level support
Security ON
SNMP OFF
Listener Parameter File /opt/oracle/product/9.2.0/network/admin/listener.ora
Listener Log File /opt/oracle/product/9.2.0/network/log/listener.log
Listener Trace File /opt/oracle/product/9.2.0/network/trace/listener.trc
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.33.11)(PORT=1521)))
Services Summary...
Service "eygle" has 1 instance(s).
Instance "eygle", status UNKNOWN, has 1 handler(s) for this service...
Service "julia" has 1 instance(s).
Instance "eygle", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

我們再來看客戶端的訪問：

D:>tnsping eygle

TNS Ping Utility for 32-bit Windows: Version 10.2.0.3.0 - Production on 28-1月 -2008 14:53:19
Copyright (c) 1997, 2006, Oracle. All rights reserved.

已使用的引數檔案:
C:oracle10.2.0networkadminsqlnet.ora


已使用 TNSNAMES 介面卡來解析別名
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.33.11)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = eygle)))
TNS-12547: TNS: 丟失連線

需要注意的是一定要將本地地址，或者Cluster群集其他節點的地址都加入到允許列表，否則監聽器可能無法啟動。
修改引數之後，重啟監聽器設定即可生效。

透過監聽器的限制，通常屬於輕量級，比在資料庫內部透過觸發器進行限制效率要高。

-The End-