限制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 the TCP.INVITED_NODES parameter, 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 the sqlnet.invited_node list.

       注意这句话： 如果要验证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

eth0      Link encap:Ethernet  HWaddr 00:0D:56:2F:78:2D 

          inet addr:192.168.2.245  Bcast:192.168.2.255  Mask:255.255.255.0

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

          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric: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 on  25 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

 

 

 

 

 

 

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