Oracle11g限制指定IP访问

Oracle11gR2版本： Version 11.2.0.1.0 - Production

lsnrctl stop

1. 编辑文件：E:appproduct11.2.0dbhome_1NETWORKADMINsqlnet.ora
2. 如这里至允许本地、192.168.1.115、192.168.1.16等主机访问，详细配置如下

# This file is actually generated by netca. But if customers choose to 
# install "Software Only", this file wont exist and without the native 
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES = (NTS)

tcp.validnode_checking = yes
tcp.invited_nodes = (192.168.1.115,localhost,127.0.0.1,192.168.1.16)

2. 配置说明

tcp.validnode_checking = yes
#tcp.invited_nodes=() #允许访问的IP列表或主机名，用逗号分隔
如：tcp.invited_nodes=(192.168.1.115,localhost,127.0.0.1,192.168.1.16)
#这里需要注意的是必须把本机ip地址加进来
#tcp.excluded_nodes=() #限制访问的IP列表或主机名，用逗号分隔

#说明：
#同时使用tcp.invited_nodes和tcp.excluded_nodes,则会以tcp.invited_nodes为主
# 支持通配符，如tcp.invited_nodes = (192.168.1.*,192.168.1.23/22) 
# 只能限制TCP协议
# 需要重启监听生效：
lsnrctl stop
lsnrctl start

3. 配置后，如果非允许的主机IP连接数据库时，会提示 ORA-12537: TNS:lost contact 或中文提示 ORA-12537: TNS:连接关闭

E:appproduct11.2.0dbhome_1NETWORKADMINtnsnames.ora

# tnsnames.ora Network Configuration File: E:appproduct11.2.0dbhome_1NETWORKADMINtnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.115)(PORT = 1521))

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

ORCL119 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.119)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

lsnrctl stop
lsnrctl start

tcp.validnode_checking = yes
tcp.invited_nodes = (192.168.1.115,localhost,127.0.0.1)

ORA-12541: TNS:监听程序当前无法识别连接描述符中请求的服务

ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

package com.yuan;

import java.sql.Connection;
import java.sql.DriverManager;

public class OracleDBConnect {
    public static void main(String[] args) {
        oracle();
    }
    public static void oracle() {
        try {
            Connection conn;
            Class.forName("oracle.jdbc.driver.OracleDriver");//加载数据驱动
            conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.115:1521:ORCL", "yuan_test", "yuan_test");
            System.out.println(conn);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

执行上面连接代码后出现下面信息

Connected to the target VM, address: '127.0.0.1:51801', transport: 'socket'
java.sql.SQLException: Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
 
	at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:458)
	at oracle.jdbc.driver.PhysicalConnection.(PhysicalConnection.java:546)
	at oracle.jdbc.driver.T4CConnection.(T4CConnection.java:236)
	at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
	at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:521)
	at java.sql.DriverManager.getConnection(DriverManager.java:664)
	at java.sql.DriverManager.getConnection(DriverManager.java:247)
	at com.yuan.OracleDBConnect.oracle(OracleDBConnect.java:15)
	at com.yuan.OracleDBConnect.main(OracleDBConnect.java:8)
Caused by: oracle.net.ns.NetException: Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
 
	at oracle.net.ns.NSProtocol.connect(NSProtocol.java:395)
	at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1102)
	at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:320)
	... 8 more
Disconnected from the target VM, address: '127.0.0.1:51801', transport: 'socket'

Process finished with exit code 0
 

在：E:appproduct11.2.0dbhome_1NETWORKADMINlistener.ora文件中加入下面信息

(SID_DESC =
#注意这里的全局数据库名称要写正确,因为笔者这里安装时默认的数据库名称就是orcl
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = E:appproduct11.2.0dbhome_1)
(SID_NAME = ORCL)
)

listener.ora完整内容如下

# listener.ora Network Configuration File: E:appproduct11.2.0dbhome_1NETWORKADMINlistener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
    (GLOBAL_DBNAME = orcl)
     (ORACLE_HOME = E:appproduct11.2.0dbhome_1)
     (SID_NAME = ORCL)
     )
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = E:appproduct11.2.0dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:E:appproduct11.2.0dbhome_1binoraclr12.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.115)(PORT = 1521))
	  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )