테이블/컬럼 마스킹 처리 함수
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 강원도 진도군 진도읍 **리 강원도 진도군 진도읍 ** 강원도 진도군 진도읍 **리 강원도 진도군 **읍 소포리
;
마스킹 대상 관리 테이블
마스킹 대상 테이블
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);
마스킹 대상 제외 테이블
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);
마스킹 대상 관리 뷰
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임을 꼭 밝혀주세요. 밝히지 않을시 저작권 문제 제기 할수 있음.