IP 체크 테이블 및 허용 IP 체크 QUERY

 다음과 같은 DB 테이블이 설계되어 있다.

 

 

* DB 생성 SCRIPT 는 다음과 같다.

 

ALTER TABLE 스키마명.HURT635
 DROP PRIMARY KEY CASCADE;

DROP TABLE 스키마명.HURT635 CASCADE CONSTRAINTS;

CREATE TABLE 스키마명.HURT635
(
  KEY_SEQ    NUMBER(5)                          NOT NULL,
  ALLW_YN    VARCHAR2(1 BYTE)                   DEFAULT 'Y'                   NOT NULL,
  IP_1       VARCHAR2(3 BYTE)                   DEFAULT '*'                   NOT NULL,
  IP_2       VARCHAR2(3 BYTE)                   DEFAULT '*'                   NOT NULL,
  IP_3       VARCHAR2(3 BYTE)                   DEFAULT '*'                   NOT NULL,
  IP_4       VARCHAR2(3 BYTE)                   DEFAULT '*'                   NOT NULL,
  REMK       VARCHAR2(400 BYTE),
  INPT_ID    VARCHAR2(20 BYTE),
  INPT_DTTM  DATE                               DEFAULT SYSDATE               NOT NULL,
  INPT_IP    VARCHAR2(20 BYTE),
  MOD_ID     VARCHAR2(20 BYTE),
  MOD_DTTM   DATE,
  MOD_IP     VARCHAR2(20 BYTE)
)
TABLESPACE SNU_DATA
PCTUSED    40
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE 스키마명.HURT635 IS '직원근태시각등록IP';

COMMENT ON COLUMN 스키마명.HURT635.KEY_SEQ IS '키순번';

COMMENT ON COLUMN 스키마명.HURT635.ALLW_YN IS '허용여부';

COMMENT ON COLUMN 스키마명.HURT635.IP_1 IS 'IP1';

COMMENT ON COLUMN 스키마명.HURT635.IP_2 IS 'IP2';

COMMENT ON COLUMN 스키마명.HURT635.IP_3 IS 'IP3';

COMMENT ON COLUMN 스키마명.HURT635.IP_4 IS 'IP4';

COMMENT ON COLUMN 스키마명.HURT635.REMK IS '비고';

COMMENT ON COLUMN 스키마명.HURT635.INPT_ID IS '입력자ID';

COMMENT ON COLUMN 스키마명.HURT635.INPT_DTTM IS '입력일시';

COMMENT ON COLUMN 스키마명.HURT635.INPT_IP IS '입력자IP';

COMMENT ON COLUMN 스키마명.HURT635.MOD_ID IS '수정자ID';

COMMENT ON COLUMN 스키마명.HURT635.MOD_DTTM IS '수정일시';

COMMENT ON COLUMN 스키마명.HURT635.MOD_IP IS '수정자IP';

CREATE UNIQUE INDEX 스키마명.PK_HURT635 ON 스키마명.HURT635
(KEY_SEQ)
LOGGING
TABLESPACE SNU_DATA
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;

ALTER TABLE 스키마명.HURT635 ADD (
  CONSTRAINT PK_HURT635
  PRIMARY KEY
  (KEY_SEQ)
  USING INDEX 스키마명.PK_HURT635);

 

 

 

* 다음과 같은 IP 허용 or 불허에 해당하는 아이피를 등록 한다.

 

 

 

정규표현식을 사용하여 현재 접속된 IP 피가 허용된 IP 인지 확인한다.

 

#strIp#  = > 현재 접속한 IP

<!--
   허용된 IP인지 확인합니다.
 -->
 

/*  허용된 IP인지 확인*/
  

SELECT  DECODE(COUNT(1), 0, 'N', 'Y') AS ALLW_YN
    FROM  HURT635 A
   WHERE  A.ALLW_YN = 'Y'
     AND (A.IP_1 = '*' OR A.IP_1 = REGEXP_SUBSTR(#strIp#, '[^.]+', 1, 1))
     AND (A.IP_2 = '*' OR A.IP_2 = REGEXP_SUBSTR(#strIp#, '[^.]+', 1, 2))
     AND (A.IP_3 = '*' OR A.IP_3 = REGEXP_SUBSTR(#strIp#, '[^.]+', 1, 3))
     AND (A.IP_4 = '*' OR A.IP_4 = REGEXP_SUBSTR(#strIp#, '[^.]+', 1, 4))
     AND  NOT EXISTS (SELECT  1
                        FROM  HURT635 B
                       WHERE  B.ALLW_YN = 'N'
                         AND (B.IP_1 = '*' OR B.IP_1 = REGEXP_SUBSTR(#strIp#, '[^.]+', 1, 1))
                         AND (B.IP_2 = '*' OR B.IP_2 = REGEXP_SUBSTR(#strIp#, '[^.]+', 1, 2))
                         AND (B.IP_3 = '*' OR B.IP_3 = REGEXP_SUBSTR(#strIp#, '[^.]+', 1, 3))
                         AND (B.IP_4 = '*' OR B.IP_4 = REGEXP_SUBSTR(#strIp#, '[^.]+', 1, 4))
                     )