由 dawei
【方法】如何限定IP访问Oracle数据库
1.1 BLOG文档结构图
1.2 前言部分
1.2.1 导读和注意事项
各位技术爱好者，看完本文后，你可以掌握如下的技能，也可以学到一些其它你所不知道的知识，~O(∩_∩)O~：
① 限定IP访问Oracle数据库的3种方法（重点）
② 如何将信息写入到Oracle的告警日志中
③ RAISE_APPLICATION_ERROR不能抛出错误到客户端环境
④ 系统触发器
⑤ 隐含参数：_system_trig_enabled
Tips：
① 本文在itpub（http://blog.itpub.net/26736162）、博客园(http://www.cnblogs.com/lhrbest)和微信公众号（xiaomaimiaolhr）上有同步更新。
② 文章中用到的所有代码、相关软件、相关资料及本文的pdf版本都请前往小麦苗的云盘下载，小麦苗的云盘地址见：http://blog.itpub.net/26736162/viewspace-1624453/。
③ 若网页文章代码格式有错乱，请下载pdf格式的文档来阅读。
④ 在本篇BLOG中，代码输出部分一般放在一行一列的表格中。
本文若有错误或不完善的地方请大家多多指正，您的批评指正是我写作的最大动力。
1.3 本文简介
本文详细介绍了3种限制IP地址登录Oracle数据库的办法。
1.3.1 本文实验环境介绍
1.4 限定IP访问Oracle数据库的3种办法
1.4.1 利用登录触发器
1.4.1.1 简单版
客户端登录：
告警日志无输出。
1.4.1.2 复杂版
复杂版就是需要记录登录日志，并把报错信息输出到告警日志中。
客户端登录：
告警日志：
查询日志表：
SELECT * FROM XB_AUDIT_LOGON_LHR;
1.4.1.3 注意事项
需要注意的问题：
① 触发的对象类型可以为DATABASE，也可以为“用户名.SCHEMA”，如：
AFTER LOGON ON DATABASE
AFTER LOGON ON SCOTT.SCHEMA
② 当触发的对象类型为DATABASE的时候，登录用户不能拥有“ADMINISTER DATABASE TRIGGER”的系统权限；当触发的对象类型为“用户名.SCHEMA”的时候，登录用户不能拥有“ALTER ANY TRIGGER”的系统权限。否则，这些用户还是会正常登录到数据库，只是将相应的报错信息写入到告警日志中。所以，拥有IMP_FULL_DATABASE和DBA角色的用户以及SYS和EXFSYS用户将不能通过这种方式限制登录。
③ 隐含参数“_SYSTEM_TRIG_ENABLED”的默认值是TRUE，即允许DDL和系统触发器。当设置隐含参数“_SYSTEM_TRIG_ENABLED”为FALSE的时候，将禁用DDL和系统触发器。所以，当该值设置为FALSE的时候将不能通过这种方式限制登录。
一、测试第二点
第二点测试如下：
客户端登录：
告警日志：
继续测试：
客户端继续登录，发现不能正常登录。将触发器中的AFTER LOGON ON DATABASE修改为AFTER LOGON ON LHR8.SCHEMA，其他不变，继续测试：
发现可以正常登录了，告警日志：
二、测试第三点
将触发器中的AFTER LOGON ON LHR8.SCHEMA修改为AFTER LOGON ON DATABASE，其他不变，继续测试:
不能正常登录，下面禁用系统触发器：
进行登录：
发现可以正常登录了。将参数"_system_trig_enabled"修改回原值。
1.4.1.4 利用登录触发器实现时间段登录
Use Event Triggers
------------------
If you allow the users to log in the database only from Monday to Friday included,
and from 8AM to 6PM,create an event trigger that checks after logon on
database for each user (except the DBA users) that the connection occurs only
within this timeframe.
Example 1
-------
1. No check set up yet: any ordinary user can log into the database:
SQL> connect test_trigger/test_trigger
Connected.
2. The DBA creates an event trigger that checks if the connection occurs
between Monday and Friday,and within working hours: 8AM to 6PM.
Trigger created.
3. It is Friday 5PM : an ordinary user can log into the database:
Example 2
-------
Another example to restrict the logon periods for a users so that they can only
access the database betrween the periods to 17:00 - 24:00 daily.
If the user attempts to logon during a period outside of this range his logon
attempt will fail:
However,users with ADMINISTER DATABASE TRIGGER system privilege can log into
the database any time.
1.4.2 利用sqlnet.ora
第二种是修改$ORACLE_HOME/network/admin/sqlnet.ora文件，增加如下内容：
之后重新启动监听器即可。这样客户端在登录的时候会报“ORA-12537: TNS:connection closed”的错误。
需要注意的问题：
① 需要设置参数TCP.VALIDNODE_CHECKING为YES才能激活该特性。
② 一定要许可或不要禁止数据库服务器本机的IP地址，否则通过lsnrctl将不能启动或停止监听，因为该过程监听程序会通过本机的IP访问监听器，而该IP被禁止了，但是通过服务启动或关闭则不影响。
③ 当参数TCP.INVITED_NODES和TCP.EXCLUDED_NODES设置的地址相同的时候以TCP.INVITED_NODES的配置为主。
④ 修改之后，一定要重起监听才能生效，而不需要重新启动数据库。
⑤ 这个方式只是适合TCP/IP协议。
⑥ 这个配置适用于Oracle 9i以上版本。在Oracle 9i之前的版本使用文件protocol.ora。
⑦ 在服务器上直接连接数据库不受影响。
⑧ 这种限制方式是通过监听器来限制的。
⑨ 这个限制只是针对IP检测，对于用户名检测是不支持的。
删除之前创建的触发器，继续测试。
重启监听：
客户端连接：
监听报错：
使用192.168.59.1客户端进行登录：
发现可以正常登录。将TCP.INVITED_NODES的IP里加入192.168网段，则可以正常登录:
客户端登录：
1.4.3 利用防火墙
第3种是修改数据库服务器的IPTABLES（配置文件：/etc/sysconfig/iptables）来限制某些IP登录数据库服务器。如下：
则，192.168.59.129这台主机将不能连接到数据库服务器了，会报“ORA-12170: TNS:Connect timeout occurred”的错误。
测试：
该部分可以参考网络配置，小麦苗从网上找了很多。
我们可以通过以下的iptables的设置来限制用户访问oracle所在linux操作系统的安全。
1、清楚操作系统默认的iptables策略
我本机安装的是centos6.0,安装之后系统会提供iptables默认的policy策略,我们首先要清楚默认的策略
iptables -F
2、开发22和1521端口对局域网的某个IP，在本例中客户端ip是192.168.1.125,oracle所在机器的IP是192.168.1.144,在这里，设置仅有该客户端可以访问22和1521端口，局域网内的其他IP都不允许访问，
iptables -A INPUT -s 192.168.1.125/32 -i eth0 -p tcp --dport 22 -j ACCEPT
iptables -A INPUT -s 192.168.1.125/32 -i eth0 -p tcp --dport 1521 -j ACCEPT
iptables -A INPUT -s 192.168.1.0/24 -p tcp --dport 22 -j DROP
iptables -A INPUT -s 192.168.1.0/24 -p tcp --dport 1521 -j DROP
这样同一网段内除192.168.1.125之外其他IP都不能访问数据库服务器，即使ping命令也不可以
3、开发22和1521的OUTPUT链给192.168.1.125，否则已经启动的oracle instance的pmon进程无法动态注册到1521端口中
iptables -A OUTPUT -d 192.168.1.125/32 -p tcp --sport 22 -j ACCEPT
iptables -A OUTPUT -d 192.168.1.125/32 -p tcp --sport 1521 -j ACCEPT
4、保存当前设置的iptables规则
service iptables save
这时系统会将已经设置的规则保存到/etc/sysconfig/iptables文件中
否则重启之后之前设置的规则都会失效
先关闭所有的80端口
开启ip段192.168.1.0/24端的80口
开启ip段211.123.16.123/24端ip段的80口
# iptables -I INPUT -p tcp --dport 80 -j DROP
# iptables -I INPUT -s 192.168.1.0/24 -p tcp --dport 80 -j ACCEPT
# iptables -I INPUT -s 211.123.16.123/24 -p tcp --dport 80 -j ACCEPT
以上是临时设置。
1.先备份iptables
# cp /etc/sysconfig/iptables /var/tmp
2.然后保存iptables
# service iptables save
3.重启防火墙
#service iptables restart
以下是端口，先全部封再开某些的IP
iptables -I INPUT -p tcp --dport 9889 -j DROP
iptables -I INPUT -s 192.168.1.0/24 -p tcp --dport 9889 -j ACCEPT
如果用了NAT转发记得配合以下才能生效
iptables -I FORWARD -p tcp --dport 80 -j DROP
iptables -I FORWARD -s 192.168.1.0/24 -p tcp --dport 80 -j ACCEPT
常用的IPTABLES规则如下：
只能收发邮件，别的都关闭
iptables -I Filter -m mac --mac-source 00:0F:EA:25:51:37 -j DROP
iptables -I Filter -m mac --mac-source 00:0F:EA:25:51:37 -p udp --dport 53 -j ACCEPT
iptables -I Filter -m mac --mac-source 00:0F:EA:25:51:37 -p tcp --dport 25 -j ACCEPT
iptables -I Filter -m mac --mac-source 00:0F:EA:25:51:37 -p tcp --dport 110 -j ACCEPT
IPSEC NAT 策略
iptables -I PFWanPriv -d 192.168.100.2 -j ACCEPT
iptables -t nat -A PREROUTING -p tcp --dport 80 -d $INTERNET_ADDR -j DNAT --to-destination 192.168.100.2:80
iptables -t nat -A PREROUTING -p tcp --dport 1723 -d $INTERNET_ADDR -j DNAT --to-destination 192.168.100.2:1723
iptables -t nat -A PREROUTING -p udp --dport 1723 -d $INTERNET_ADDR -j DNAT --to-destination 192.168.100.2:1723
iptables -t nat -A PREROUTING -p udp --dport 500 -d $INTERNET_ADDR -j DNAT --to-destination 192.168.100.2:500
iptables -t nat -A PREROUTING -p udp --dport 4500 -d $INTERNET_ADDR -j DNAT --to-destination 192.168.100.2:4500
FTP服务器的NAT
iptables -I PFWanPriv -p tcp --dport 21 -d 192.168.100.200 -j ACCEPT
iptables -t nat -A PREROUTING -p tcp --dport 21 -d $INTERNET_ADDR -j DNAT --to-destination 192.168.100.200:21
只允许访问指定网址
iptables -A Filter -p udp --dport 53 -j ACCEPT
iptables -A Filter -p tcp --dport 53 -j ACCEPT
iptables -A Filter -d www.3322.org -j ACCEPT
iptables -A Filter -d img.cn99.com -j ACCEPT
iptables -A Filter -j DROP
开放一个IP的一些端口，其它都封闭
iptables -A Filter -p tcp --dport 80 -s 192.168.100.200 -d www.pconline.com.cn -j ACCEPT
iptables -A Filter -p tcp --dport 25 -s 192.168.100.200 -j ACCEPT
iptables -A Filter -p tcp --dport 109 -s 192.168.100.200 -j ACCEPT
iptables -A Filter -p tcp --dport 110 -s 192.168.100.200 -j ACCEPT
iptables -A Filter -p tcp --dport 53 -j ACCEPT
iptables -A Filter -p udp --dport 53 -j ACCEPT
iptables -A Filter -j DROP
多个端口
iptables -A Filter -p tcp -m multiport --destination-port 22,53,80,110 -s 192.168.20.3 -j REJECT
连续端口
iptables -A Filter -p tcp -m multiport --source-port 22,110 -s 192.168.20.3 -j REJECT iptables -A Filter -p tcp --source-port 2:80 -s 192.168.20.3 -j REJECT
指定时间上网
iptables -A Filter -s 10.10.10.253 -m time --timestart 6:00 --timestop 11:00 --days Mon,Tue,Wed,Thu,Fri,Sat,Sun -j DROP
iptables -A Filter -m time --timestart 12:00 --timestop 13:00 --days Mon,Sun -j ACCEPT
iptables -A Filter -m time --timestart 17:30 --timestop 8:30 --days Mon,Sun -j ACCEPT
禁止多个端口服务
iptables -A Filter -m multiport -p tcp --dport 21,23,80 -j ACCEPT
将WAN 口NAT到PC
iptables -t nat -A PREROUTING -i $INTERNET_IF -d $INTERNET_ADDR -j DNAT --to-destination 192.168.0.1
将WAN口8000端口NAT到192。168。100。200的80端口
iptables -t nat -A PREROUTING -p tcp --dport 8000 -d $INTERNET_ADDR -j DNAT --to-destination 192.168.100.200:80
MAIL服务器要转的端口
iptables -t nat -A PREROUTING -p tcp --dport 110 -d $INTERNET_ADDR -j DNAT --to-destination 192.168.100.200:110
iptables -t nat -A PREROUTING -p tcp --dport 25 -d $INTERNET_ADDR -j DNAT --to-destination 192.168.100.200:25
只允许PING 202。96。134。133,别的服务都禁止
iptables -A Filter -p icmp -s 192.168.100.200 -d 202.96.134.133 -j ACCEPT
iptables -A Filter -j DROP
禁用BT配置
iptables –A Filter –p tcp –dport 6000:20000 –j DROP
禁用QQ防火墙配置
iptables -A Filter -p udp --dport ! 53 -j DROP
iptables -A Filter -d 218.17.209.0/24 -j DROP
iptables -A Filter -d 218.18.95.0/24 -j DROP
iptables -A Filter -d 219.133.40.177 -j DROP
基于MAC，只能收发邮件，其它都拒绝
iptables -I Filter -m mac --mac-source 00:0A:EB:97:79:A1 -j DROP
iptables -I Filter -m mac --mac-source 00:0A:EB:97:79:A1 -p tcp --dport 25 -j ACCEPT
iptables -I Filter -m mac --mac-source 00:0A:EB:97:79:A1 -p tcp --dport 110 -j ACCEPT
禁用MSN配置
iptables -A Filter -p udp --dport 9 -j DROP
iptables -A Filter -p tcp --dport 1863 -j DROP
iptables -A Filter -p tcp --dport 80 -d 207.68.178.238 -j DROP
iptables -A Filter -p tcp --dport 80 -d 207.46.110.0/24 -j DROP
只允许PING 202。96。134。133 其它公网IP都不许PING
iptables -A Filter -p icmp -s 192.168.100.200 -d 202.96.134.133 -j ACCEPT
iptables -A Filter -p icmp -j DROP
禁止某个MAC地址访问internet:
iptables -I Filter -m mac --mac-source 00:20:18:8F:72:F8 -j DROP
禁止某个IP地址的PING:
iptables –A Filter –p icmp –s 192.168.0.1 –j DROP
禁止某个IP地址服务：
iptables –A Filter -p tcp -s 192.168.0.1 --dport 80 -j DROP
iptables –A Filter -p udp -s 192.168.0.1 --dport 53 -j DROP
只允许某些服务，其他都拒绝(2条规则)
iptables -A Filter -p tcp -s 192.168.0.1 --dport 1000 -j ACCEPT
iptables -A Filter -j DROP
禁止某个IP地址的某个端口服务
iptables -A Filter -p tcp -s 10.10.10.253 --dport 80 -j ACCEPT
iptables -A Filter -p tcp -s 10.10.10.253 --dport 80 -j DROP
禁止某个MAC地址的某个端口服务
iptables -I Filter -p tcp -m mac --mac-source 00:20:18:8F:72:F8 --dport 80 -j DROP
禁止某个MAC地址访问internet:
iptables -I Filter -m mac --mac-source 00:11:22:33:44:55 -j DROP
禁止某个IP地址的PING:
iptables –A Filter –p icmp –s 192.168.0.1 –j DROP
1.5 本文总结
在Oracle中，有3种办法可以限定特定IP访问数据库。第一种是利用登录触发器，如下：
需要注意的问题：
① 触发的对象类型可以为DATABASE，也可以为“用户名.SCHEMA”，如：
② 当触发的对象类型为DATABASE的时候，登录用户不能拥有“ADMINISTER DATABASE TRIGGER”的系统权限；当触发的对象类型为“用户名.SCHEMA”的时候，登录用户不能拥有“ALTER ANY TIGGER”的系统权限。否则，这些用户还是会正常登录到数据库，只是将相应的报错信息写入到告警日志中。所以，拥有IMP_FULL_DATABASE和DBA角色的用户以及SYS和EXFSYS用户将不能通过这种方式限制登录。
③ 隐含参数“_SYSTEM_TRIG_ENABLED”的默认值是TRUE，即允许DDL和系统触发器。当设置隐含参数“_SYSTEM_TRIG_ENABLED”为FALSE的时候，将禁用DDL和系统触发器。所以，当该值设置为FALSE的时候将不能通过这种方式限制登录。
第二种是修改$ORACLE_HOME/network/admin/sqlnet.ora文件，增加如下内容：
之后重新启动监听器即可。这样客户端在登录的时候会报“ORA-12537: TNS:connection closed”的错误。
需要注意的问题：
① 需要设置参数TCP.VALIDNODE_CHECKING为YES才能激活该特性。
② 一定要许可或不要禁止数据库服务器本机的IP地址，否则通过lsnrctl将不能启动或停止监听，因为该过程监听程序会通过本机的IP访问监听器，而该IP被禁止了，但是通过服务启动或关闭则不影响。
③ 当参数TCP.INVITED_NODES和TCP.EXCLUDED_NODES设置的地址相同的时候以TCP.INVITED_NODES的配置为主。
④ 修改之后，一定要重起监听才能生效，而不需要重新启动数据库。
⑤ 这个方式只是适合TCP/IP协议。
⑥ 这个配置适用于Oracle 9i以上版本。在Oracle 9i之前的版本使用文件protocol.ora。
⑦ 在服务器上直接连接数据库不受影响。
⑧ 这种限制方式是通过监听器来限制的。
⑨ 这个限制只是针对IP检测，对于用户名检测是不支持的。
第3种是修改数据库服务器的IPTABLES（配置文件：/etc/sysconfig/iptables）来限制某些IP登录数据库服务器。如下：
则，192.168.59.1这台主机将不能通过1521端口连接到数据库服务器了，会报“ORA-12170: TNS:Connect timeout occurred”的错误。
1.6 参考
1.6.1 MOS
1.6.1.1 Connecting as DBA Does not Fire RAISE_APPLICATION_ERROR in a AFTER LOGON ON DATABASE TRIGGER (文档 ID 226058.1)
APPLIES TO:
Oracle Database - Enterprise Edition - Version 8.1.7.4 to 11.2.0.2 [Release 8.1.7 to 11.2]
Information in this document applies to any platform.
***Checked for relevance on 03-Aug-2016***
SYMPTOMS
TRIGGER AFTER LOGON ON DATABASE does not fire
Login as a user with DBA privileges
ORA-00604: error occurred at recursive SQL level %s
ORA-06512: at %sline %s
CAUSE
Documented and expected behavior.
SOLUTION
Oracel Database 11.2:
Oracle? Database PL/SQL Language Reference 11g Release 2 (11.2)
Chapter 9 PL/SQL Triggers
Exception Handling in Triggers
In most cases,if a trigger runs a statement that raises an exception,and the exception is not handled by an exception handler,then the database rolls back the effects of both the trigger and its triggering statement.
In the following cases,the database rolls back only the effects of the trigger,not the effects of the triggering statement (and logs the error in trace files and the alert log):
The triggering event is either AFTER STARTUP ON DATABASE or BEFORE SHUTDOWN ON DATABASE.
The triggering event is AFTER LOGON ON DATABASE and the user has the ADMINISTER DATABASE TRIGGER privilege.
The triggering event is AFTER LOGON ON SCHEMA and the user either owns the schema or has the ALTER ANY TRIGGER privilege.
Oracle Server 11.1:
Oracle Database PL/SQL Language Reference 11g Release 1
Chapter: Using Triggers
Section: Error Conditions and Exceptions in the Trigger Body
If a predefined or user-defined error condition or exception is raised during the execution of a trigger body,then all effects of the trigger body,as well as the triggering statement,are rolled back (unless the error is trapped by an exception handler). Therefore,a trigger body can prevent the execution of the triggering statement by raising an exception. User-defined exceptions are commonly used in triggers that enforce complex security authorizations or integrity constraints.
The only exception to this is when the event under consideration is database STARTUP,SHUTDOWN,or LOGIN when the user logging in is SYSTEM. In these scenarios,only the trigger action is rolled back.
REFERENCES
BUG:1415194
- RAISE_APPLICATION_ERROR DOES NOT RAISE AN EXCEPTION INSIDE A AFTER LOGON TRIGGER
http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/triggers.htm#CIHGJCFI
http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/triggers.htm#autoId33