【LISTENER】使用sqlnet.ora禁止特定IP訪問資料庫
使用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 --
簡單記錄一下，供參考。
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 --
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/519536/viewspace-630591/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 使用sqlnet.ora禁止特定IP訪問資料庫SQL資料庫
- 限制特定IP訪問資料庫資料庫
- 使用Oracle Net實現限制特定IP訪問資料庫Oracle資料庫
- 禁止某個IP訪問Oracle資料庫Oracle資料庫
- Nginx禁止特定使用者代理（UserAgents）訪問（轉）Nginx
- 使用SQLNET.ora檔案限制Ip地址訪問SQL
- Oracle資料庫限制訪問IPOracle資料庫
- destoonphp中如何禁止IP訪問PHP
- windows設定僅訪問特定ipWindows
- 如何限制ip訪問Oracle資料庫Oracle資料庫
- 【方法】如何限定IP訪問Oracle資料庫Oracle資料庫
- 如何限定IP訪問Oracle資料庫-轉Oracle資料庫
- 資料庫限制/允許某些IP訪問資料庫
- linux安全篇：禁止頻繁訪問的ip訪問nginxLinuxNginx
- 使用sqlnet.ora檔案，限定特定IP地址登入的實驗。SQL
- Oracle配置sqlnet.ora限制ip訪問[Oracle基礎]OracleSQL
- 通過SQLNET.ora檔案限制Ip地址訪問SQL
- 透過SQLNET.ora檔案限制Ip地址訪問SQL
- 禁止頻繁請求的ip訪問nginxNginx
- 觸發器限制指定IP訪問oracle資料庫觸發器Oracle資料庫
- 設定oracle資料庫IP訪問限制(IP白名單黑名單)Oracle資料庫
- jmeter 使用 ssh 方式訪問資料庫JMeter資料庫
- ORACLE 11g sqlnet.ora 設定限制IP 訪問OracleSQL
- apache(包含虛擬主機)禁止使用IP訪問的實現方法Apache
- oracle資料庫中listener.ora sqlnet.ora tnsnames.ora的區別Oracle資料庫SQL
- linux centos7新增ip黑名單禁止某個ip訪問LinuxCentOS
- 使用 @NoRepositoryBean 簡化資料庫訪問Bean資料庫
- 恆訊科技分析：如何禁止用IP訪問網站？網站
- 教你如何處理Nginx禁止ip加埠訪問的問題Nginx
- JDBC資料庫訪問JDBC資料庫
- Sqlserver限制賬戶在哪些ip下才可以訪問資料庫SQLServer資料庫
- Linux禁止某個IP地址訪問的幾種方法Linux
- 使用speedment以Java 8 Stream訪問資料庫Java資料庫
- 使用PowerBuilder同時訪問多個資料庫UI資料庫
- Oracle資料庫訪問控制Oracle資料庫
- 異構資料庫訪問資料庫
- jboss訪問資料庫的問題資料庫
- 9000埠使用ip不能訪問