Oracle11g限制指定IP访问

Oracle11gR2限制指定的IP才能连接

Oracle11gR2版本： Version 11.2.0.1.0 - Production

1. 先停止监听

lsnrctl stop

2. 配置监听文件

1. 编辑文件：E:\app\product\11.2.0\dbhome_1\NETWORK\ADMIN\sqlnet.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:连接关闭

3. tnsnames.ora中配置IP

E:\app\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora

# tnsnames.ora Network Configuration File: E:\app\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.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