觸發(fā)器限制指定IP訪問oracle數(shù)據(jù)庫

發(fā)布時(shí)間：2020-08-04 18:52:50 來源：ITPUB博客閱讀：222 作者：xypincle 欄目：大數(shù)據(jù)

---------------------來自德哥的BLOG，覺著很有用，記錄一下-------------------

最近有個(gè)項(xiàng)目需要限制某些數(shù)據(jù)庫用戶的訪問來源IP，在PG中比較好實(shí)現(xiàn)，但是ORACLE沒有比較簡便的操作。

如果不管用戶的話，僅僅限制來源IP對監(jiān)聽的訪問是比較容易實(shí)現(xiàn)的，通過配置數(shù)據(jù)庫服務(wù)器的sqlnet.ora文件或者修改數(shù)據(jù)庫服務(wù)器的IPTABLES等手段實(shí)現(xiàn)。

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的服務(wù)器

-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. 創(chuàng)建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所在服務(wù)器登錄.其他用戶不受限制.

2. 創(chuàng)建觸發(fā)器

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;

向AI問一下細(xì)節(jié)

觸發(fā)器限制指定IP訪問oracle數(shù)據(jù)庫

猜你喜歡

最新資訊

相關(guān)推薦

相關(guān)標(biāo)簽