oracle11 sqlnet,ORACLE 11g sqlnet.ora 设置限制IP 访问

昨天看三思的mysql  看到一节 说 mysql 创建用户 :

CREATE USER 'usernmae'@'192.168.2.3' IDENTIFIED BY 'password'   是表明 只有从192.168.2.3 发起的usernmae 访问才被允许接入。

联想到ORACLE 也有登入策略通过sqlnet.ora 设置参数限制IP访问策略。

参考文档(Doc ID 462933.1)：

metalink

In this Document

Goal

Fix

APPLIES TO:

Oracle Net Services - Version 9.2.0.1.0 and later

Information in this document applies to any platform.

此功能适合于9.2.0.1.0 以后的版本

GOAL

How to control access to the database and understand validnode checking.

通过节点检查控制访问数据库

FIX

You can configure the sqlnet.ora file to allow and deny access to the database via the validnode checking parmeters. (Earlier versions of Oracle, 8i and lower used the protocol.ora file)

通过在sqlnet.ora 里面配置参数控制通过或者拒绝访问数据库，8i之前的数据库是配置protocol.ora文件

TCP.VALIDNODE_CHECKING

TCP.VALIDNODE_CHECKING 参数

Use to specify whether to screen access to the database.Value is either YES or ON

指定是否设置保护数据库

TCP.EXCLUDED_NODES

Use to specify which clients using the TCP/IP protocol are denied access to the database. Hostname and ipaddress can be used

TCP.EXCLUDED_NODES 参数设置数据库拒绝访问的IP 使用tcp/ip 协议。 主机名和ip地址都可使用

TCP.INVITED_NODES

Use to specify which clients using the TCP/IP protocol are allowed access to the database. Hostname and ipadddress can be used.

Example sqlnet.ora file (set where database is running)

TCP.INVITED_NODES 参数设置数据库通过访问的IP 使用tcp/ip 协议。 主机名和ip地址都可使用

注意设置了的时候必须包括本机

TCP.VALIDNODE_CHECKING = YES

TCP.EXCLUDED_NODES= (138.3.33.33)

TCP.INVITED_NODES=(138.4.44.44, hammer)

注意设置了的时候必须包括本机

Would cause the SQL*plus from client "138.3.33.33" to error

sqlplus scott/tiger@orcl

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Oct 16 11:48:40 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

ERROR:

ORA-12537: TNS:connection closed

Level 16 listener trace will show

nttvlser: valid node check on incoming node 138.3.33.33

nttvlser: Denied Entry: 138.3.33.33

nttcon: exit

nserror: entry

nserror: nsres: id=1, p=65, ns=12546, ns2=12560; nt[0]=516, nt[1]=0, nt[2]=0; ora[0]=0, ora[1]=0, ora[2]=

Listener log will show

16-OCT-2007 11:48:40 * 12546

TNS-12546: TNS:permission denied

TNS-12560: TNS:protocol adapter error

TNS-00516: Permission denied

But would allow connections from machines "138.4.44.44" and "hammer" to pass.Please note that without the servers host name or ip address in the invited list, then PMON will not register with the listener.

Any changes to the values requires the TNS listener to be reloaded

All host names must be resolvable or the TNS listener will not start

Invited list takes precedence over excluded listed

All entries must be on one line(Best to add entries via Net Manager)

SCAN and TCP.INVITED_NODES will require the SCAN VIPs and Node Vips to be added to the Grid Infrastructure SQLNET.ORA file.