使用sqlnet.ora禁止特定IP訪問資料庫

使用Oracle的sqlnet.ora檔案可以實現禁止指定IP主機訪問資料庫功能，這對於提升資料庫的安全性有很大的幫助，與此同時，這個技術為我們管理和約束資料庫訪問控制提供了有效的手段。
簡單記錄一下，供參考。

1.預設sqlnet.ora內容
這裡我們以Oracle 11.2.0.1.0版本為例進行探索。
/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

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

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

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

>

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）停止監聽
/home/oracle$ lsnrctl stop

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

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

2）啟動監聽
/home/oracle$ lsnrctl start

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

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）檢視監聽狀態，確保監聽啟動成功
/home/oracle$ lsnrctl status

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

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

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

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

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列表中，看一下效果。
/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

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

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

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/20976446/viewspace-674112/，如需轉載，請註明出處，否則將追究法律責任。

使用sqlnet.ora禁止特定IP訪問資料庫

相關文章