【oracle】sqlnet.ora 訪問控制策略

楊奇龍發表於2011-09-29
OracleSQL
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伺服器端進行訪問！ 
場景一：修改檔案，不啟動監聽
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
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:53:58
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
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:54:49
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-12537: TNS:connection closed~           
在 rac3 上進行reload 命令：
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
再次訪問yangdb，則可以訪問
在yangdb 上建立表
YANG@yangdb-rac3>  create table yang1 as select * from dba_objects ;
Table created.


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:55:10
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)))
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

場景二：修改rac3 上的sqlnet.ora 檔案，進行reload操作，rac1 訪問rac3的yangdb受限制
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)
tcp.invited_nodes =(10.250.7.241)
#不允許訪問的ip
#tcp.excluded_nodes=(ip1,ip2,…x…)                     
                                                                                                                                        
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:57:20
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-12537: TNS:connection closed
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:11
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
場景三 在sqlnet.ora 中同時設定 tcp.invited_nodes，tcp.excluded_nodes 以tcp.invited_nodes 為準！
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)
#tcp.invited_nodes =(10.250.7.241)
#不允許訪問的ip
tcp.excluded_nodes=(10.250.7.225)                                         "sqlnet.ora" 7L, 186C 已寫入
oracle@rac3:/opt/oracle/11.2.0/alifpre/network/admin>
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:58:19
Copyright (c) 1991, 2009, Oracle.  All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
The command completed successfully
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
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)))
OK (0 msec)

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

相關文章