概述
在论坛里看到一个帖子，如何限制某些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
------------------------------------------------------------------------------
QQ:492913789
Email:ahdba@qq.com
Blog: http://www.cndba.cn/dave
网上资源： http://tianlesoftware.download.csdn.net
相关视频：http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx
DBA1 群：62697716(满); DBA2 群：62697977(满)
DBA3 群：62697850 DBA 超级群：63306533;
聊天 群：40132017
--加群需要在备注说明Oracle表空间和数据文件的关系，否则拒绝申请
最后
以上就是坚强果汁为你收集整理的限制IP 访问 Oracle 的方法的全部内容，希望文章能够帮你解决限制IP 访问 Oracle 的方法所遇到的程序开发问题。
如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。
发表评论 取消回复