행위

오라클 마스킹 함수

DB CAFE

thumb_up 추천메뉴 바로가기


1 오라클 데이터 마스킹 처리[편집]

1.1 테이블/컬럼 마스킹 처리 함수[편집]

CREATE OR REPLACE FUNCTION FN_MASKING(PV_STR VARCHAR, PN_START NUMBER, PN_COUNT NUMBER, PC_MARK CHAR DEFAULT '*', PN_DEBUG NUMBER DEFAULT 0) 
RETURN VARCHAR
IS
   VN_LEN    NUMBER;  -- 문자열 길이
   VN_POS1   NUMBER;  -- 마스킹 시작 위치 - 1
   VN_POS2   NUMBER;  -- 마스킹 종료 위치 + 1

   VV_MASK   VARCHAR2(4000);
   VV_RETURN VARCHAR2(4000);
--   VV_MSG    VARCHAR2(100);  -- 디버그 메시지 출력시 사용 -- FOR DEBUG
/* TEST CASE ***************************************************************
SELECT DATA||', '||P1||', '||P2||', '||P3 PARAM, FN_MASKING(DATA, P1, P2, P3, PN_DEBUG=>0) RESULT
  FROM (SELECT '1234567890' DATA FROM DUAL UNION ALL SELECT '가나다라마바사아자차' FROM DUAL) A
     , (SELECT 0 P1, 0 P2, '*' P3 FROM DUAL
        UNION ALL SELECT 1,   1, '*' FROM DUAL 
        UNION ALL SELECT 1,   5, '*' FROM DUAL
        UNION ALL SELECT 1, 100, '*' FROM DUAL
        UNION ALL SELECT 2, 100, '*' FROM DUAL
        UNION ALL SELECT 5,   1, '*' FROM DUAL
        UNION ALL SELECT 5,   2, '*' FROM DUAL
        UNION ALL SELECT 5,  10, '*' FROM DUAL
        UNION ALL SELECT 5,   0, '*' FROM DUAL
        UNION ALL SELECT 5,  -1, '*' FROM DUAL
        UNION ALL SELECT 5,  -5, '*' FROM DUAL
        UNION ALL SELECT -5,  1, '*' FROM DUAL
        UNION ALL SELECT -5,  5, '*' FROM DUAL
        UNION ALL SELECT -5,  0, '*' FROM DUAL
        UNION ALL SELECT -5,-10, '*' FROM DUAL
        UNION ALL SELECT 10,  1, '*' FROM DUAL
        UNION ALL SELECT 10,  2, '*' FROM DUAL
        UNION ALL SELECT 10,  0, '*' FROM DUAL
        UNION ALL SELECT 10, -1, '*' FROM DUAL
        UNION ALL SELECT 10,-10, '*' FROM DUAL
        UNION ALL SELECT 11, 11, '*' FROM DUAL ) B
*/
   
BEGIN
    IF PV_STR IS NULL THEN    -- 널이면 그대로 리턴
        VV_RETURN := PV_STR;
    ELSE
        VN_LEN := LENGTH(PV_STR);
        IF PN_START >= VN_LEN THEN  -- 시작위치가 문자길이 이상이면 마지막 자릿수 - 1;
            VN_POS1 := VN_LEN - 1;
        ELSIF PN_START < 0 THEN  -- 시작위치가 음수일 경우 뒤에서 부터 위치 선정, 최소 시작값 1
            VN_POS1 := GREATEST(VN_LEN + PN_START, 1);
        ELSE
            VN_POS1 := PN_START - 1;
        END IF;
        
        IF PN_COUNT < 1 THEN    -- 마스킹 자릿수가 음수이면 뒤에서 -N번째 이전까지 마스킹
            VN_POS2 := GREATEST(VN_LEN + PN_COUNT + 1, VN_POS1 + 2);  -- 최소 1개 마스킹 위해 POS1 + 2
        ELSE 
            VN_POS2 := LEAST(VN_LEN + 1, VN_POS1 + PN_COUNT + 1);
        END IF;

--        VV_MSG := '[DEBUG]: ' ||TO_CHAR(VN_POS1)||', '||TO_CHAR(VN_POS2);  -- FOR DEBUG
       
--        FOR IX IN VN_POS1..(VN_POS2-2)  -- 각각 -1, +1로 인해 2의 GAP이 있음
--        LOOP
--            VV_MASK := VV_MASK || PC_MARK;
--        END LOOP;
        VV_MASK := RPAD(PC_MARK, VN_POS2 - VN_POS1 -1, PC_MARK);
        
        IF VN_POS1 < 1 THEN
            VV_RETURN := VV_MASK || SUBSTR(PV_STR, VN_POS2);
        ELSIF VN_POS2 > VN_LEN THEN
            VV_RETURN := SUBSTR(PV_STR, 1, VN_POS1) || VV_MASK;
        ELSE
            VV_RETURN := SUBSTR(PV_STR, 1, VN_POS1) || VV_MASK || SUBSTR(PV_STR, VN_POS2);
        END IF;
        
    END IF;
    
    IF PN_DEBUG = 1 THEN
--        RETURN VV_RETURN||VV_MSG;   -- FOR DEBUG
        RETURN VV_RETURN||'[DEBUG]: ' ||TO_CHAR(VN_POS1)||', '||TO_CHAR(VN_POS2);  -- FOR DEBUG
    ELSE
        RETURN VV_RETURN;
    END IF;
--EXCEPTION WHEN OTHERS THEN      -- FOR DEBUG
--    RETURN SQLERRM||VV_MSG;     -- FOR DEBUG
END;
/
--------------------------------------------------------------------------------
-- 읍,면,동 마스킹 처리 
--------------------------------------------------------------------------------
WITH T
AS(
  SELECT '서울시 용산구 논현동 114번지' AS TEST FROM DUAL UNION ALL
  SELECT '부천시 소사구 심곡본동 527' AS TEST FROM DUAL UNION ALL
  SELECT '강원도 횡성군 청일면 신대리' AS TEST FROM DUAL UNION ALL
  SELECT '강원도 횡성군 청일면 소포동길 26' AS TEST FROM DUAL UNION ALL
  SELECT '강원도 횡성군 차동면 소포동길 26' AS TEST FROM DUAL UNION ALL
  SELECT '강원도 진도군 진도읍 소포리' AS TEST FROM DUAL
)
SELECT
       REGEXP_REPLACE(TEST, '[^ ]+([동리가로])', '**\1')   AS JUSO
     , REGEXP_REPLACE(TEST, '[^ ]+([동리가로])', '**\2')   AS JUSO
     , REGEXP_REPLACE(TEST, '[^ ]+([동리가로])', '**\1')   AS JUSO
     , REGEXP_REPLACE(TEST, '[^ ]+([읍면동가로])', '**\1') AS JUSO
FROM T
;

-- 결과
1  서울시 용산구 **동 114번지  서울시 용산구 ** 114번지  서울시 용산구 **동 114번지  서울시 용산구 **동 114번지
2  부천시 소사구 **동 527  부천시 소사구 ** 527  부천시 소사구 **동 527  부천시 소사구 **동 527
3  강원도 횡성군 청일면 **리  강원도 횡성군 청일면 **  강원도 횡성군 청일면 **리  강원도 횡성군 **면 신대리
4  강원도 횡성군 청일면 **동길 26  강원도 횡성군 청일면 **길 26  강원도 횡성군 청일면 **동길 26  강원도 횡성군 **면 **동길 26
5  강원도 횡성군 **동면 **동길 26  강원도 횡성군 **면 **길 26  강원도 횡성군 **동면 **동길 26  강원도 횡성군 **면 **동길 26
6  강원도 진도군 진도읍 **리  강원도 진도군 진도읍 **  강원도 진도군 진도읍 **리  강원도 진도군 **읍 소포리
;

1.2 마스킹 대상 관리 테이블[편집]

1.2.1 마스킹 대상 테이블[편집]

CREATE TABLE TB_MGR_MASKING
(
  NO           NUMBER(3),
  MSK_TARGET   VARCHAR2(30 BYTE)                NOT NULL,
  TARGET_TYPE  VARCHAR2(30 BYTE),
  FILTER       VARCHAR2(100 BYTE),
  ENCRYPT_YN   VARCHAR2(1 BYTE),
  FMT          VARCHAR2(100 BYTE),
  ARGS         VARCHAR2(100 BYTE),
  FUNC         VARCHAR2(500 BYTE),
  FUNC_CRYPT   VARCHAR2(500 BYTE)
)
;

COMMENT ON TABLE TB_MGR_MASKING IS '마스킹대상관리';
COMMENT ON COLUMN TB_MGR_MASKING.NO IS '번호';
COMMENT ON COLUMN TB_MGR_MASKING.MSK_TARGET IS '마스킹대상';
COMMENT ON COLUMN TB_MGR_MASKING.TARGET_TYPE IS '대상유형';
COMMENT ON COLUMN TB_MGR_MASKING.FILTER IS '추출방식';
COMMENT ON COLUMN TB_MGR_MASKING.ENCRYPT_YN IS '암호화여부';
COMMENT ON COLUMN TB_MGR_MASKING.FMT IS '형식';
COMMENT ON COLUMN TB_MGR_MASKING.ARGS IS '함수인자';
COMMENT ON COLUMN TB_MGR_MASKING.FUNC IS '함수적용(일반)';
COMMENT ON COLUMN TB_MGR_MASKING.FUNC_CRYPT IS '함수적용(암호화)';


CREATE UNIQUE INDEX PK_TB_MGR_MASKING ON TB_MGR_MASKING
(MSK_TARGET)
;

ALTER TABLE TB_MGR_MASKING ADD (
  CONSTRAINT PK_TB_MGR_MASKING
  PRIMARY KEY
  (MSK_TARGET)
  USING INDEX PK_TB_MGR_MASKING
  ENABLE VALIDATE);

1.2.2 마스킹 대상 제외 테이블[편집]

CREATE TABLE TB_MGR_MASKING_EXCEPT
(
  OWNER             VARCHAR2(30 BYTE),
  TABLE_NAME        VARCHAR2(30 BYTE),
  COLUMN_NAME       VARCHAR2(30 BYTE),
  EXCEPTION_TYPE    VARCHAR2(30 BYTE),
  TABLE_DESC        VARCHAR2(300 BYTE),
  COLUMN_DESC       VARCHAR2(300 BYTE),
  CHARGER           VARCHAR2(100 BYTE),
  EXCEPTION_REASON  VARCHAR2(300 BYTE)
)
;

COMMENT ON COLUMN TB_MGR_MASKING_EXCEPT.OWNER IS '소유자';
COMMENT ON COLUMN TB_MGR_MASKING_EXCEPT.TABLE_NAME IS '테이블명';
COMMENT ON COLUMN TB_MGR_MASKING_EXCEPT.COLUMN_NAME IS '컬럼명';
COMMENT ON COLUMN TB_MGR_MASKING_EXCEPT.EXCEPTION_TYPE IS '예외유형';
COMMENT ON COLUMN TB_MGR_MASKING_EXCEPT.TABLE_DESC IS '테이블설명';
COMMENT ON COLUMN TB_MGR_MASKING_EXCEPT.COLUMN_DESC IS '컬럼설명';
COMMENT ON COLUMN TB_MGR_MASKING_EXCEPT.CHARGER IS '담당자';
COMMENT ON COLUMN TB_MGR_MASKING_EXCEPT.EXCEPTION_REASON IS '예외사유';

CREATE UNIQUE INDEX PK_TB_MGR_MASKING_EXCEPT ON TB_MGR_MASKING_EXCEPT
(OWNER, TABLE_NAME, COLUMN_NAME)
;

ALTER TABLE TB_MGR_MASKING_EXCEPT ADD (
  CONSTRAINT PK_TB_MGR_MASKING_EXCEPT
  PRIMARY KEY
  (OWNER, TABLE_NAME, COLUMN_NAME)
  USING INDEX PK_TB_MGR_MASKING_EXCEPT
  ENABLE VALIDATE);

1.3 마스킹 대상 관리 뷰[편집]

CREATE OR REPLACE FORCE VIEW VW_DBA_MASKING_TABLE
(
    OWNER
  , TABLE_NAME
  , CNT
  , SQL_SELECT
  , SQL_UPDATE
)
AS
    WITH
        V1
        AS
            (SELECT /*+ FULL(A) FULL(B) FULL(C) USE_HASH(A B C) */
                    A.OWNER
                  , A.TABLE_NAME
                  , A.TNAME
                  , A.CID
                  , A.COLUMN_NAME
                  , A.COLNAME
                  , A.DB_TYPE_LEN
                  , A.STD_KO_DOMAIN
                  , A.STD_KO_UDOMAIN
                  , B.ENCRYPT_YN
                  , C.EXCEPTION_TYPE
                  --     , B.FUNC, B.FUNC_CRYPT
                  ,    CASE
                           WHEN B.ENCRYPT_YN = 'Y'
                           THEN
                               CASE
                                   WHEN C.EXCEPTION_TYPE = 'ENCRYPT_NO'
                                   THEN
                                          '/*예외*/'
                                       || REPLACE (B.FUNC
                                                 , 'COLUMN'
                                                 , A.COLUMN_NAME)
                                   ELSE
                                       REPLACE (B.FUNC_CRYPT
                                              , 'COLUMN'
                                              , A.COLUMN_NAME)
                               END
                           ELSE
                               REPLACE (B.FUNC, 'COLUMN', A.COLUMN_NAME)
                       END
                    || ' AS '
                    || A.COLUMN_NAME    SQL_SELECT
                  ,    A.COLUMN_NAME
                    || ' = '
                    || CASE
                           WHEN B.ENCRYPT_YN = 'Y'
                           THEN
                               CASE
                                   WHEN C.EXCEPTION_TYPE = 'ENCRYPT_NO'
                                   THEN
                                          '/*예외*/'
                                       || REPLACE (B.FUNC
                                                 , 'COLUMN'
                                                 , A.COLUMN_NAME)
                                   ELSE
                                       REPLACE (B.FUNC_CRYPT
                                              , 'COLUMN'
                                              , A.COLUMN_NAME)
                               END
                           ELSE
                               REPLACE (B.FUNC, 'COLUMN', A.COLUMN_NAME)
                       END              SQL_UPDATE
               FROM ERD_ADM.VW_COL_CHK  A
                    -- 마스킹 대상
                    JOIN TB_MGR_MASKING B
                        ON (   (    B.TARGET_TYPE = '도메인'
                                AND B.MSK_TARGET = A.STD_KO_UDOMAIN)
                            OR (    B.TARGET_TYPE = '용어'
                                AND A.COLNAME LIKE B.FILTER))
                    -- 마스킹 예외 처리
                    LEFT JOIN
                    TB_MGR_MASKING_EXCEPT C
                        ON (    C.OWNER = A.OWNER
                            AND C.TABLE_NAME = A.TABLE_NAME
                            AND C.COLUMN_NAME = A.COLUMN_NAME)
              WHERE NVL (C.EXCEPTION_TYPE, 'X') <> 'MASK_NO' --               (A.OWNER, A.TABLE_NAME, A.COLUMN_NAME)
                                                            --                     NOT IN (SELECT OWNER,TABLE_NAME, COLUMN_NAME
                                                            --                              FROM TB_MGR_MASKING_EXCEPT
                                                            --                              WHERE EXCEPTION_TYPE='MASK_NO')
                                                            )
      --SELECT * FROM V1;  -- 뷰확인
      --SELECT *
      -- FROM (
      SELECT OWNER
           , TABLE_NAME
           , COUNT (*)    CNT
           ,    'SELECT '
             || LISTAGG (SQL_SELECT, ', ') WITHIN GROUP (ORDER BY CID)
             || ' FROM '
             || OWNER
             || '.'
             || TABLE_NAME
             || ';'       SQL_SELECT
           ,    'UPDATE '
             || OWNER
             || '.'
             || TABLE_NAME
             || '
        SET '
             || LISTAGG (SQL_UPDATE, ',
            ') WITHIN GROUP (ORDER BY CID)
             || ';'       SQL_UPDATE
        FROM V1
    --       WHERE     1 = 1
    -- AND TABLE_NAME IN (SELECT DISTINCT X.TABLE_NAME
    --                  FROM TB_MGR_MASKING_TASK X)
    --   AND COLUMN_NAME LIKE '%RRN'
    GROUP BY OWNER, TABLE_NAME
-- )
-- WHERE SQL_SELECT LIKE '%DB_ENCRYPT%'
--ORDER BY 1, 2;
;
  • 출처는 DBCAFE임을 꼭 밝혀주세요. 밝히지 않을시 저작권 문제 제기 할수 있음.