sqlnet.ora中進行下列參數的設定可以限制或允許使用者從特定的客戶機連接配接到資料庫中。
tcp.validnode_checking=yes|no
tcp.invited_nodes=(ip|hostname,...)
tcp.excluded_nodes=(ip|hostname,...)
##如果是hostname 則需要在/etc/hosts 裡面配置對應的ip
tcp.validnode_checking 參數确定是否對客戶機ip位址進行檢查;
tcp.invited_nodes 參數列舉允許連接配接的客戶機的ip位址;
tcp.excluded_nodes 參數列舉不允許連接配接的客戶機的ip位址。
需要注意的地方:
1、tcp.invited_nodes與tcp.excluded_nodes都存在,以tcp.invited_nodes為主
2、一定要許可或不要禁止伺服器本機的ip位址,否則通過lsnrctl将不能啟動或停止監聽,因為該過程監聽程式會通過本機的ip通路監聽器,而該ip被禁止了,但是通過服務啟動或關閉則不影響。
3、修改之後,分兩種情況
如果是第一次使用sqlnet.ora 檔案,則需要重新開機資料庫。
如果之前已經使用了sqlnet.ora 則不需要重新開機資料庫,reload 監聽就可以!
4、任何平台都可以,但是隻适用于tcp/ip協定
下面做實驗測試通路控制:
環境:、
資料庫:yangdb 主機名:rac3 ip 10.250.7.241
主機名:rac1 ip 10.250.7.225
在 yangdb 上面的sqlnet.ora 設定,在rac1伺服器端進行通路!
<b>場景一:修改檔案,不啟動監聽</b>
oracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin>vi sqlnet.ora
tcp.validnode_checking=yes
#允許通路的ip
tcp.invited_nodes =(10.250.7.241,10.250.7.225)
#不允許通路的ip
#tcp.excluded_nodes=(ip1,ip2,…x…)
在rac1 端通路,顯示tns-12547: tns:lost contact
oracle@rac1:/opt/rac/oracle/11.2.0/dbs/network/admin>tnsping yangdb
tns ping utility for linux: version 11.2.0.1.0 - production on 27-sep-2011 21:50:35
copyright (c) 1997, 2009, oracle. all rights reserved.
used parameter files:
used tnsnames adapter to resolve the alias
attempting to contact (description = (address = (protocol = tcp)(host = 10.250.7.241)(port = 1521)) (connect_data = (server = dedicated) (service_name = yangdb)))
tns-12547: tns:lost contact
tns ping utility for linux: version 11.2.0.1.0 - production on 27-sep-2011 21:53:58
tns ping utility for linux: version 11.2.0.1.0 - production on 27-sep-2011 21:54:49
tns-12537: tns:connection closed~
<b>在 rac3 上進行reload 指令:</b>
oracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin>lsnrctl reload
lsnrctl for linux: version 11.2.0.1.0 - production on 27-sep-2011 21:55:05
copyright (c) 1991, 2009, oracle. all rights reserved.
connecting to (address=(protocol=tcp)(host=)(port=1521))
the command completed successfully
<b>再次通路yangdb,則可以通路</b>
<b>在yangdb 上建立表</b>
yang@yangdb-rac3> create table yang1 as select * from dba_objects ;
table created.
tns ping utility for linux: version 11.2.0.1.0 - production on 27-sep-2011 21:55:10
ok (10 msec)
oracle@rac1:/opt/rac/oracle/11.2.0/dbs/network/admin>sqlplus yang/yang@yangdb
sql*plus: release 11.2.0.1.0 production on tue sep 27 21:55:17 2011
copyright (c) 1982, 2009, oracle. all rights reserved.
connected to:
oracle database 11g enterprise edition release 11.2.0.1.0 - 64bit production
with the partitioning, olap, data mining and real application testing options
yang@yangdb> select count(*) from yang1
count(*)
----------
72508
yang@yangdb> exit
<b>場景二:修改rac3 上的sqlnet.ora 檔案,進行reload操作,rac1 通路rac3的yangdb受限制</b>
#tcp.invited_nodes =(10.250.7.241,10.250.7.225)
tcp.invited_nodes =(10.250.7.241)
#tcp.excluded_nodes=(ip1,ip2,…x…)
tns ping utility for linux: version 11.2.0.1.0 - production on 27-sep-2011 21:57:20
tns-12537: tns:connection closed
tns ping utility for linux: version 11.2.0.1.0 - production on 27-sep-2011 21:58:11
<b>場景三 在sqlnet.ora 中同時設定 tcp.invited_nodes,tcp.excluded_nodes 以tcp.invited_nodes 為準!</b>
#tcp.invited_nodes =(10.250.7.241)
tcp.excluded_nodes=(10.250.7.225) "sqlnet.ora" 7l, 186c 已寫入
oracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin>
lsnrctl for linux: version 11.2.0.1.0 - production on 27-sep-2011 21:58:19
oracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin> oracle@rac1:/opt/rac/oracle/11.2.0/dbs/network/admin>tnsping yangdb
tns ping utility for linux: version 11.2.0.1.0 - production on 27-sep-2011 21:58:25
ok (0 msec)