用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#)
   )