限制IP 访问 Oracle 的方法

在论坛里看到一个帖子，如何限制某些IP访问数据库。这种限制可以通过两种层面实现。一是数据库层面，另一个是防火墙的层面。这里只说明一下数据库层面的限制。测试如下。

一.修改sqlnet.ora文件

sqlnet.ora文件在$ORACLE_HOME/network/admin目录下，添加如下内容：

tcp.validnode_checking=yes

#允许访问的IP

tcp.invited_nodes=(ip1,ip2…)

#禁止访问的IP

tcp.excluded_nodes=(ip1,ip2…)

修改之后重新启动监听器即可，DB服务器本身连接实例不走监听，所以不受此限制。

1.1先看官网对这几个参数的说明：

（1）TCP.VALIDNODE_CHECKING

Purpose

To create a hard failure when host names in the invited or excluded list fail to resolve to an IP address. This ensures a customer's desired configuration is enforced, meaning that valid node checking cannot take place unless the host names are resolvable to IP addresses.

This is important in the context of theTCP.INVITED_NODESparameter, because it requires that every one of the client nodes be listed in the server's sqlnet.invited_nodes list. When one of the clients is decommissioned, and removed from the host name database, it becomes unresolvable, and causes the listener to fail to start.

Note:

In order to use the TCP.VALIDNODE_CHECKING parameter invited nodes, the host name database must be kept in up-to-date with thesqlnet.invited_nodelist.

注意这句话：如果要验证invited节点，最新的数据库主机名必须要在sqlnet.invited_node列表中。

（2）TCP.EXCLUDED_NODES

Purpose

To specify which clients are denied access to the database. This parameter does not use wildcards for IP addresses or partial IP addresses.

Syntax

TCP.EXCLUDED_NODES=(hostname| ip_address, hostname | ip_address, ...)

Example

TCP.EXCLUDED_NODES=(finance.us.example.com, mktg.us.example.com, 192.168.2.25, 172.30.*, 2001:DB8:200C:417A/32)

（3）TCP.INVITED_NODES

Purpose

To specify which clients are allowed access to the database. This parameter does not use wildcards for IP addresses or partial IP addresses. This list takes precedence over the TCP.EXCLUDED_NODES parameter if both lists are present.

Syntax

TCP.INVITED_NODES=(hostname| ip_address, hostname | ip_address, ...)

Example

TCP.INVITED_NODES=(sales.us.example.com, hr.us.example.com, 192.168.2.73)

From：

http://download.oracle.com/docs/cd/E11882_01/network.112/e10835/sqlnet.htm#NETRF238

1.2测试

[wangou@qs-test-web log]$ ifconfig eth0

eth0Link encap:EthernetHWaddr 00:0D:56:2F:78:2D

inet addr:192.168.2.245Bcast:192.168.2.255Mask:255.255.255.0

inet6 addr: fe80::20d:56ff:fe2f:782d/64 Scope:Link

UP BROADCAST RUNNING MULTICASTMTU:1500Metric:1

RX packets:9490305 errors:0 dropped:0 overruns:0 frame:0

TX packets:3268512 errors:0 dropped:0 overruns:0 carrier:0

collisions:0 txqueuelen:1000

RX bytes:1010246991 (963.4 MiB)TX bytes:3052311073 (2.8 GiB)

Interrupt:201 Memory:fcf10000-fcf20000

1.2.1添加参数之前：

[wangou@qs-test-web log]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on ÐÇÆÚ¶þ 1ÔÂ 25 11:01:13 2011

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

SQL> conn sys/admin@dave as sysdba;

SQL> select name from v$database;

NAME

---------

NEWCCS

1.2.2添加到Exclude列表：

tcp.validnode_checking=yes

#允许访问的IP

tcp.invited_nodes=(daviddai, 192.168.3.*)

#注意这里的DB主机名，daviddai,如果不写，连不上并报ORA-12505错误。

#禁止访问的IP

tcp.excluded_nodes=(192.168.2.*)

[wangou@qs-test-web log]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on ÐÇÆÚ¶þ 1ÔÂ 25 11:08:20 2011

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

SQL> conn sys/admin@dave as sysdba;

ERROR:

ORA-12537: TNS: connection closed

当IP被添加到受限访问时，会报ORA-12537错误：

[wangou@qs-test-web log]$ oerr tns 12537

12537, 00000, "TNS:connection closed"

// *Cause: "End of file" condition has been reached; partner has disconnected.

// *Action: None needed; this is an information message.

1.2.3添加到Invited列表

tcp.validnode_checking=yes

#允许访问的IP

tcp.invited_nodes=( daviddai,192.168.2.*)

#注意这里的DB主机名，daviddai,如果不写，连不上并报ORA-12505错误。

#禁止访问的IP

tcp.excluded_nodes=(192.168.3.*)

[wangou@qs-test-web admin]$ sqlplus system/admin@dave;

SQL*Plus: Release 11.2.0.1.0 Production on25 11:24:01 2011

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name from v$database;

NAME

---------

NEWCCS

二.通过触发器

2.1创建触发器

create or replace trigger logon_audit

after logon on database

begin

if ora_client_ip_address='192.168.2.245' or ora_client_ip_address='192.168.3.115' then

raise_application_error(-20001,'该用户不允许登录',false);

----抛出自定义的错误

end if;

end;

注意：AFTER LOGON ON DATABASE触发器对有DBA权限的用户不起作用。

2.2测试

SQL> conn dave/dave@dave;

ERROR:

ORA-00604: error occurred at recursive SQL level 1

ORA-20001:该用户不允许登录--用户自己定义

ORA-06512:在line 4

------------------------------------------------------------------------------