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

secooler發表於2010-03-28
使用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 --

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/519536/viewspace-630591/,如需轉載,請註明出處,否則將追究法律責任。

相關文章