限制IP 访问 Oracle 的方法
在论坛里看到一个帖子，如何限制某些IP访问数据库。 这种限制可以通过两种层面实现。一是数据库层面，另一个是防火墙的层面。这里只说明一下数据库层面的限制。 测试如下。
一. 修改sqlnet.ora 文件
sqlnet.ora文件在$ORACLE_HOME/network/admin 目录下，添加如下内容：
#允许访问的IP
tcp.invited_nodes=(ip1,ip2…)
#禁止访问的IP
tcp.excluded_nodes=(ip1,ip2…)
修改之后重新启动监听器即可，DB 服务器本身连接实例不走监听，所以不受此限制。
1.1 先看官网对这几个参数的说明：
（1）TCP.VALIDNODE_CHECKING
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列表中。
To specify which clients are denied access to the database. This parameter does not use wildcards for IP addresses or partial IP addresses.
TCP.EXCLUDED_NODES=(hostname | ip_address, hostname | ip_address, ...)
TCP.EXCLUDED_NODES=(finance.us.example.com, mktg.us.example.com, 192.168.2.25, 172.30.*, 2001:DB8:200C:417A/32)
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.
TCP.INVITED_NODES=(hostname | ip_address, hostname | ip_address, ...)
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
------------------------------------------------------------------------------
Blog： http://blog.csdn.net/tianlesoftware
网上资源： 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表空间和数据文件的关系，否则拒绝申请
