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

———————来自德哥的BLOG，觉着很有用，记录一下——————-

最近有个项目需要限制某些数据库用户的访问来源IP，在PG中比较好实现，但是ORACLE没有比较简便的操作。

如果不管用户的话，仅仅限制来源IP对监听的访问是比较容易实现的，通过配置数据库服务器的sqlnet.ora文件或者修改数据库服务器的IPTABLES等手段实现。

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用户下,随便建哪里都可以)

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所在服务器登录.其他用户不受限制.

2. 创建触发器

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;

exception

when value_error then

sys.dbms_output.put_line(‘exception handed’);

when others then

raise;

end logon_audit;

原创文章，作者：ItWorker，如若转载，请注明出处：https://blog.ytso.com/tech/opensource/197444.html

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

相关推荐

发表回复