触发器限制指定IP访问oracle数据库

最近有个项目需要限制某些数据库用户的访问来源IP,在PG中比较好实现,但是ORACLE没有比较简便的操作。
如果不管用户的话,仅仅限制来源IP对监听的访问是比较容易实现的,通过配置数据库服务器的sqlnet.ora文件或者修改数据库服务器的IPTABLES等手段实现。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
sqlnet.ora范例:
tcp.validnode_checking=yes
tcp.invited_nodes=(172.16.33.11,172.16.34.89)
iptables范例:
[root@kefu ~]# cat /etc/sysconfig/iptables
# Firewall configuration written by system-config-securitylevel
# Manual customization of this file is not recommended.
*filter
:INPUT ACCEPT [0:0]
:FORWARD ACCEPT [0:0]
:OUTPUT ACCEPT [0:0]
:RH-Firewall-1-INPUT - [0:0]
-A INPUT -j RH-Firewall-1-INPUT
-A FORWARD -j RH-Firewall-1-INPUT
# 允许访问1521的服务器
-A RH-Firewall-1-INPUT -s 172.16.3.68/32 -m state --state NEW -m tcp -p tcp --dport 1521 -j ACCEPT
-A RH-Firewall-1-INPUT -i lo -j ACCEPT
-A RH-Firewall-1-INPUT -p icmp --icmp-type any -j ACCEPT
-A RH-Firewall-1-INPUT -p 50 -j ACCEPT
-A RH-Firewall-1-INPUT -p 51 -j ACCEPT
-A RH-Firewall-1-INPUT -p udp --dport 5353 -d 224.0.0.251 -j ACCEPT
-A RH-Firewall-1-INPUT -p udp -m udp --dport 631 -j ACCEPT
-A RH-Firewall-1-INPUT -p tcp -m tcp --dport 631 -j ACCEPT
-A RH-Firewall-1-INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT
-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 22 -j ACCEPT
-A RH-Firewall-1-INPUT -j REJECT --reject-with icmp-host-prohibited
COMMIT

下面来看看如何限制特定用户和特定IP:

  1. 创建ACL表 (本例将ACL表建立在dsm用户下,随便建哪里都可以)
    1
    2
    3
    4
    create table dsm.tbl_iplimit (logonuser varchar2(32),ip_address varchar2(15),remark varchar2(64),create_time date default sysdate);
    insert into dsm.tbl_iplimit values ('DSM','172.16.18.81','digoal''s host.',sysdate);
    insert into dsm.tbl_iplimit values ('DSM','local','本地',sysdate);
    commit;

这里限制了DSM用户只能从172.16.18.81和ORACLE所在服务器登录.其他用户不受限制.

  1. 创建触发器

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    conn / as sysdba
    create or replace trigger "logon_audit" after
    logon on database
    declare
    record_num number;
    userip varchar2(15);
    isforbidden boolean:=true;
    begin
    userip:=nvl(sys_context ('userenv','ip_address'),'local');
    select count(*) into record_num from dsm.tbl_iplimit where logonuser=user;
    if (record_num>0) then
    select count(*) into record_num from dsm.tbl_iplimit where logonuser=user and ip_address=userip;
    if (record_num=0) then
    raise_application_error(-20003,'ip :'||userip||' is forbided');
    end if;
    end if;
    exception
    when value_error then
    sys.dbms_output.put_line('exception handed');
    when others then
    raise;
    end logon_audit;
    /
  2. 测试

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    在本地登录
    SQL> conn dsm/pwd
    正常
    delete from tbl_iplimit where ip_address='local';
    commit;
    exit;
    再在本地登录,已经受阻了.
    SQL> conn dsm/pwd
    ERROR:
    ORA-00604: error occurred at recursive SQL level 1
    ORA-20003: ip :local is forbided
    ORA-06512: at line 18

    换台机器(172.16.3.67)登录:
    SQL> conn dsm/pwd@//172.16.3.13:1521/sid
    ERROR:
    ORA-00604: error occurred at recursive SQL level 1
    ORA-20003: ip :local is forbided
    ORA-06512: at line 18
    受阻

    换台机器(172.16.3.81)登录:
    SQL> conn dsm/pwd@//172.16.3.13:1521/sid
    正常

如果IP范围比较宽,可以写一个IP比较的函数加入到上面的判断中,避免写很多条记录。

你活着,证明了什么?