[ORACLE] 개인정보에 따른 마스킹 처리 방법

엠케이로그 2024. 7. 8. 00:04

1. 개인정보 마스킹

개인정보 마스킹은 개인정보를 보호하기 위해 일부 또는 전체 정보를 비식별화하는 방법을 의미

개인정보보호법 및 관련 규정에 따라 다양한 마스킹 방법이 사용

2. 마스킹 필요 예시

이름, 주소지, 등등 타인에게 노출될 수 있을 때 마스킹 필요

3. 개인정보에 따른 마스킹 처리 방법

이름

마스킹 방법: 성을 제외한 나머지 부분을 마스킹
예시: 하○○, 박○○

-- ORACLE 
WITH ORDER_USER AS (
  SELECT '하이요' AS NAME FROM DUAL UNION ALL
  SELECT '김코딩' AS NAME FROM DUAL UNION ALL
  SELECT '박개발' AS NAME FROM DUAL
)
SELECT
  NAME,
  SUBSTR(NAME, 1, 1) || LPAD('*', LENGTH(NAME) - 1, '*') AS MASKING_NAME
FROM
  ORDER_USER;

SUBSTR(NAME, 1, 1): 이름의 첫 글자를 추출
LENGTH(NAME) - 1: 이름의 길이에서 첫 글자를 제외한 나머지 길이를 계산
LPAD('*', LENGTH(NAME) - 1, '*'): 나머지 길이만큼 '*'로 채우기
SUBSTR(NAME, 1, 1) || LPAD('*', LENGTH(NAME) - 1, '*'): 첫 글자와 '*'로 채운 문자열을 결합해서 마스킹처리

*LPAD 함수

- 문자열의 왼쪽에 지정된 문자로 패딩(padding)을 추가하여 지정된 길이만큼 문자열을 확장하는 데 사용

SELECT LPAD('Hello', 10, '*') AS padded_string FROM dual;

padded_string
-------------
*****Hello

-- MYSQL
WITH ORDER_USER AS (
  SELECT '하이요' AS NAME
  UNION ALL
  SELECT '김코딩' AS NAME
  UNION ALL
  SELECT '박개발' AS NAME
)
SELECT
  NAME,
  CONCAT(SUBSTRING(NAME, 1, 1), REPEAT('*', CHAR_LENGTH(NAME) - 1)) AS MASKING_NAME
FROM
  ORDER_USER;

[MYSQL]

SUBSTRING(NAME, 1, 1): 이름의 첫 글자를 추출.
CHAR_LENGTH(NAME) - 1: 이름의 길이에서 첫 글자를 제외한 나머지 길이를 계산
REPEAT('*', CHAR_LENGTH(NAME) - 1): 나머지 길이만큼 '*'로 채우기
CONCAT(SUBSTRING(NAME, 1, 1), REPEAT('*', CHAR_LENGTH(NAME) - 1)): 첫 글자와 '*'로 채운 문자열을 결합해서 마스킹처리

주소

마스킹 방법: 상세 주소 마스킹
예시: 서울특별시 강남구 테헤란로 123 (중간 부분 생략)

-- ORACLE
WITH address_table AS (
  SELECT '서울특별시 강남구 역삼동 123-45' AS address FROM dual UNION ALL
  SELECT '부산광역시 해운대구 우동 678-90' AS address FROM dual UNION ALL
  SELECT '대구광역시 수성구 범어동 111-22' AS address FROM dual
)
SELECT 
  address,
  REGEXP_REPLACE(address, '^([^ ]+ [^ ]+ ).+', '\1*') AS masked_address
FROM 
  address_table;

^([^ ]+ [^ ]+ ).+: 정규표현식 패턴

^: 문자열의 시작을 의미.
([^ ]+ [^ ]+ ): 두 개의 단어(시와 구 단위)를 캡처. 여기서 각각의 [^ ]+는 공백이 아닌 문자의 연속을 의미함.
.+: 나머지 모든 문자를 의미

\1*: 대체 문자열
- \1: 첫 번째 캡처 그룹을 참조 (시와 구 단위).
- *: 나머지 주소를 대체할 문자

시/구 단위까지만 남기고 그 뒤의 주소를 *로 대체

-- MYSQL
SELECT 
  address,
  CONCAT(
    LEFT(address, LOCATE(' ', address, LOCATE(' ', address) + 1) - 1),
    ' *'
  ) AS masked_address
FROM
  (
    SELECT '서울특별시 강남구 역삼동 000-00' AS address
    UNION ALL
    SELECT '경기도 수원시 팔달구 111-11' AS address
    UNION ALL
    SELECT '대구광역시 수성구 범어동 222-22' AS address
  ) AS address_table;

주민등록번호

마스킹 방법: 뒤 7자리 마스킹
예시: 900101-1******

SELECT SUBSTR(주민등록번호, 1, 6) || '-' || RPAD('*', 7, '*') AS 마스킹된_주민등록번호
FROM 사용자;

전화번호

마스킹 방법: 가운데 4자리 마스킹
예시: 010-****-5678

SELECT SUBSTR(전화번호, 1, 3) || '-****-' || SUBSTR(전화번호, 9, 4) AS 마스킹된_전화번호
FROM 사용자;

이메일 주소

마스킹 방법: '@' 이전 부분을 부분 마스킹
예시: joh****@example.com

SELECT SUBSTR(이메일, 1, 3) || RPAD('*', INSTR(이메일, '@') - 4, '*') || SUBSTR(이메일, INSTR(이메일, '@')) AS 마스킹된_이메일
FROM 사용자;

카드번호

마스킹 방법: 중간 8자리 마스킹
예시: 1234---5678

SELECT SUBSTR(카드번호, 1, 4) || '-' || RPAD('*', 4, '*') || '-' || RPAD('*', 4, '*') || '-' || SUBSTR(카드번호, -4) AS 마스킹된_카드번호
FROM 사용자;

계좌번호

마스킹 방법: 중간 자리 마스킹
예시: 123-****-567890

SELECT SUBSTR(계좌번호, 1, 4) || RPAD('*', 3, '*') || SUBSTR(계좌번호, 7) AS 마스킹된_계좌번호
FROM 사용자;

4. 데이터 마스킹 처리에 많이 사용되는 함수 Oracle 함수

1) SUBSTR 함수
: 문자열의 일부분을 추출

SUBSTR(string, start_position, length)

-- 예시 

SELECT SUBSTR('서울특별시 강남구 테헤란로 123', 1, 5) FROM DUAL;

-- 결과: '서울특별시'

2) INSTR 함수

: 문자열 내에서 특정 문자의 위치를 반환

INSTR(string, substring, start_position, occurrence)

-- 예시
SELECT INSTR('john.doe@example.com', '@') FROM DUAL;

-- 결과: 9

3) LENGTH 함수
: 문자열의 길이를 반환

LENGTH(string)

-- 예시
SELECT LENGTH('서울특별시 강남구 테헤란로 123') FROM DUAL;

-- 결과: 18

4) RPAD 함수
: 문자열의 오른쪽에 지정된 길이가 될 때까지 특정 문자를 추가

RPAD(string, length, pad_string)


-- 예시
SELECT RPAD('김', 3, '*') FROM DUAL;

-- 결과: '김**'

5) LPAD 함수
: 문자열의 왼쪽에 지정된 길이가 될 때까지 특정 문자를 추가

LPAD(string, length, pad_string)

-- 예시
SELECT LPAD('5432', 8, '*') FROM DUAL;

-- 결과: '****5432'

6) CONCAT 함수
: 두 개의 문자열을 결합

CONCAT(string1, string2)

-- 예시
SELECT CONCAT('김', '**') FROM DUAL;
-- 결과: '김**'