上一篇
下一篇
用sql语句判断防火墙策略
作者:GDNPC 日期:2009-03-14
去年做系统有个模块需要判断防火墙策略。
例如添加一条策略：10.10.10.24:20000 到10.20.20.24:20000
添加前需要做以下判断。
假如大策略：10.10.10.0/24:20000到10.20.20.0/24:20000已经存在，那么以上策略是不需要添加的。
否则则需要添加到数据库。
数据库表设计如下：
create table RSMS_FIREWALLS
(
ID NUMBER(4) not null,
FW_SIP VARCHAR2(15), ------源地址
FW_OIP VARCHAR2(15), ------目标地址
FW_SSUBNET NUMBER(2), ------源地址mask
FW_OSUBNET NUMBER(2), ------目标地址mask
FW_PORTSTART NUMBER(5), ------开始端口
FW_PORTEND NUMBER(5), ------结束端口
OIP1 NUMBER(3), ------目标地址第一位ip值
OIP2 NUMBER(3), ------目标地址第二位ip值
OIP3 NUMBER(3), ------目标地址第三位ip值
OIP4 NUMBER(3), ------目标地址第四位ip值
SIP1 NUMBER(3), -------源地址第一位ip值
SIP2 NUMBER(3), -------源地址第二位ip值
SIP3 NUMBER(3), -------源地址第三位ip值
SIP4 NUMBER(3), -------源地址第四位ip值
SMAXIP1 NUMBER(3), -------在当前mask下源地址第一位ip最大值
SMAXIP2 NUMBER(3), -------在当前mask下源地址第二位ip最大值
SMAXIP3 NUMBER(3), -------在当前mask下源地址第三位ip最大值
SMAXIP4 NUMBER(3), -------在当前mask下源地址第四位ip最大值
OMAXIP1 NUMBER(3), -------在当前mask下目标地址第一位ip最大值
OMAXIP2 NUMBER(3), -------在当前mask下目标地址第二位ip最大值
OMAXIP3 NUMBER(3), -------在当前mask下目标地址第三位ip最大值
OMAXIP4 NUMBER(3), -------在当前mask下目标地址第四位ip最大值
)
策略写入数据库后根据mask算出ip各个位的最大值，
例如：10.10.10.0/24,maxip1=10,maxip2=10,maxip3=10,maxip4=255
又如：10.10.10.0/23,maxip1=10,maxip2=10,maxip3=11,maxip4=255
如此类推。
判断sql语句如下(ibatis)，原理是根据mask判断当前策略是否被数据库中已有策略包含。
Select
distinct(id)
FROM
rsms_firewalls
Where
(sip1 = #sip1# and sip2 = #sip2# and sip3 = #sip3# and sip4 <= #sip4# and #sip4# <= smaxip4 and fw_ssubnet between 24 and 32
AND (
(oip1 = #oip1# and oip2 = #oip2# and oip3 = #oip3# and oip4 <= #oip4# and #oip4# <= omaxip4 and fw_osubnet between 24 and 32)
OR
(oip1 = #oip1# and oip2 = #oip2# and oip3 <= #oip3# and #oip3# <= omaxip3 and fw_osubnet between 16 and 23)
OR
(oip1 = #oip1# and oip2 <= #oip2# and #oip2# <= omaxip2 and fw_osubnet between 8 and 15)
OR
(oip1 <= #oip1# and #oip1# <= omaxip1 and fw_osubnet between 0 and 7)
)
AND
(fw_portstart <= #fwPortStart#
AND
fw_portend >= #fwPortEnd#)
)
or
(sip1 = #sip1# and sip2 = #sip2# and sip3 <= #sip3# and #sip3# <= smaxip3 and fw_ssubnet between 16 and 23
AND (
(oip1 = #oip1# and oip2 = #oip2# and oip3 = #oip3# and oip4 <= #oip4# and #oip4# <= omaxip4 and fw_osubnet between 24 and 32)
OR
(oip1 = #oip1# and oip2 = #oip2# and oip3 <= #oip3# and #oip3# <= omaxip3 and fw_osubnet between 16 and 23)
OR
(oip1 = #oip1# and oip2 <= #oip2# and #oip2# <= omaxip2 and fw_osubnet between 8 and 15)
OR
(oip1 <= #oip1# and #oip1# <= omaxip1 and fw_osubnet between 0 and 7)
)
AND
(fw_portstart <= #fwPortStart#
AND
fw_portend >= #fwPortEnd#)
)
OR
(sip1 = #sip1# and sip2 <= #sip2# and #sip2# <= smaxip2 and fw_ssubnet between 8 and 15
AND (
(oip1 = #oip1# and oip2 = #oip2# and oip3 = #oip3# and oip4 <= #oip4# and #oip4# <= omaxip4 and fw_osubnet between 24 and 32)
OR
(oip1 = #oip1# and oip2 = #oip2# and oip3 <= #oip3# and #oip3# <= omaxip3 and fw_osubnet between 16 and 23)
OR
(oip1 = #oip1# and oip2 <= #oip2# and #oip2# <= omaxip2 and fw_osubnet between 8 and 15)
OR
(oip1 <= #oip1# and #oip1# <= omaxip1 and fw_osubnet between 0 and 7)
)
AND
(fw_portstart <= #fwPortStart#
AND
fw_portend >= #fwPortEnd#)
)
OR
(sip1 <= #sip1# and #sip1# <= smaxip1 and fw_ssubnet between 0 and 7
AND (
(oip1 = #oip1# and oip2 = #oip2# and oip3 = #oip3# and oip4 <= #oip4# and #oip4# <= omaxip4 and fw_osubnet between 24 and 32)
OR
(oip1 = #oip1# and oip2 = #oip2# and oip3 <= #oip3# and #oip3# <= omaxip3 and fw_osubnet between 16 and 23)
OR
(oip1 = #oip1# and oip2 <= #oip2# and #oip2# <= omaxip2 and fw_osubnet between 8 and 15)
OR
(oip1 <= #oip1# and #oip1# <= omaxip1 and fw_osubnet between 0 and 7)
)
AND
(fw_portstart <= #fwPortStart#
AND
fw_portend >= #fwPortEnd#)
)
文章来自: 
引用通告: 
Tags: 
