컬럼 마스킹 (Column Masking)

관리자 (admin 그룹):
  SELECT email FROM customers → alice@company.com

분석가 (analysts 그룹):
  SELECT email FROM customers → al***@company.com

일반 사용자:
  SELECT email FROM customers → ***@***.***

CREATE OR REPLACE FUNCTION production.policies.mask_email(email_col STRING)
  RETURNS STRING
  COMMENT '이메일 주소를 역할에 따라 마스킹합니다'
  RETURN
    CASE
      WHEN IS_ACCOUNT_GROUP_MEMBER('admin') THEN email_col
      WHEN IS_ACCOUNT_GROUP_MEMBER('analysts') THEN
        CONCAT(LEFT(email_col, 2), '***@', SPLIT(email_col, '@')[1])
      ELSE '***@***.***'
    END;

CREATE OR REPLACE FUNCTION production.policies.mask_phone(phone_col STRING)
  RETURNS STRING
  COMMENT '전화번호를 역할에 따라 마스킹합니다'
  RETURN
    CASE
      WHEN IS_ACCOUNT_GROUP_MEMBER('admin') THEN phone_col
      WHEN IS_ACCOUNT_GROUP_MEMBER('analysts') THEN
        CONCAT(LEFT(phone_col, 3), '-****-', RIGHT(phone_col, 4))
      ELSE '***-****-****'
    END;

CREATE OR REPLACE FUNCTION production.policies.mask_ssn(ssn_col STRING)
  RETURNS STRING
  COMMENT '주민등록번호를 역할에 따라 마스킹합니다'
  RETURN
    CASE
      WHEN IS_ACCOUNT_GROUP_MEMBER('admin') THEN ssn_col
      WHEN IS_ACCOUNT_GROUP_MEMBER('analysts') THEN
        CONCAT(LEFT(ssn_col, 8), '******')  -- 생년월일만 노출
      ELSE '******-*******'
    END;

CREATE OR REPLACE FUNCTION production.policies.mask_card_number(card_col STRING)
  RETURNS STRING
  COMMENT '카드번호를 마스킹합니다 (마지막 4자리만 노출)'
  RETURN
    CASE
      WHEN IS_ACCOUNT_GROUP_MEMBER('admin') THEN card_col
      ELSE CONCAT('****-****-****-', RIGHT(REGEXP_REPLACE(card_col, '[^0-9]', ''), 4))
    END;

-- 이메일 컬럼에 마스킹 적용
ALTER TABLE production.ecommerce.customers
  ALTER COLUMN email SET MASK production.policies.mask_email;

-- 전화번호 컬럼에 마스킹 적용
ALTER TABLE production.ecommerce.customers
  ALTER COLUMN phone SET MASK production.policies.mask_phone;

-- 주민번호 컬럼에 마스킹 적용
ALTER TABLE production.ecommerce.customers
  ALTER COLUMN ssn SET MASK production.policies.mask_ssn;

-- 특정 컬럼의 마스킹 제거
ALTER TABLE production.ecommerce.customers
  ALTER COLUMN email DROP MASK;

-- 본인 데이터는 원본, 타인 데이터는 마스킹
CREATE OR REPLACE FUNCTION production.policies.mask_salary(
  salary_col DECIMAL(10,2),
  owner_email STRING
)
  RETURNS DECIMAL(10,2)
  RETURN
    CASE
      WHEN IS_ACCOUNT_GROUP_MEMBER('admin') THEN salary_col
      WHEN CURRENT_USER() = owner_email THEN salary_col  -- 본인 급여는 원본
      ELSE NULL  -- 타인 급여는 NULL
    END;

-- 적용 (마스킹 함수에 다른 컬럼도 전달 가능)
ALTER TABLE production.hr.employees
  ALTER COLUMN salary SET MASK production.policies.mask_salary
  USING COLUMNS (email);

-- 원본 대신 해시값을 반환 (분석용 조인키로 활용 가능)
CREATE OR REPLACE FUNCTION production.policies.hash_email(email_col STRING)
  RETURNS STRING
  RETURN
    CASE
      WHEN IS_ACCOUNT_GROUP_MEMBER('admin') THEN email_col
      ELSE SHA2(email_col, 256)  -- 해시값으로 대체
    END;

-- 정확한 나이 대신 연령대로 표시
CREATE OR REPLACE FUNCTION production.policies.mask_age(age_col INT)
  RETURNS STRING
  RETURN
    CASE
      WHEN IS_ACCOUNT_GROUP_MEMBER('admin') THEN CAST(age_col AS STRING)
      ELSE CONCAT(CAST(FLOOR(age_col / 10) * 10 AS STRING), '대')
    END;

-- 테이블에 적용된 마스킹 함수 확인
DESCRIBE TABLE EXTENDED production.ecommerce.customers;

-- 마스킹이 올바르게 동작하는지 테스트
-- (관리자 계정으로 실행)
SELECT email, phone FROM production.ecommerce.customers LIMIT 5;

-- 특정 사용자가 보는 결과를 시뮬레이션하려면
-- 해당 사용자 계정으로 직접 쿼리를 실행해야 합니다

-- 행 필터: 지역별 데이터 제한
ALTER TABLE production.ecommerce.customers
  SET ROW FILTER production.policies.region_row_filter ON (region);

-- 컬럼 마스킹: 이메일, 전화번호 마스킹
ALTER TABLE production.ecommerce.customers
  ALTER COLUMN email SET MASK production.policies.mask_email;

ALTER TABLE production.ecommerce.customers
  ALTER COLUMN phone SET MASK production.policies.mask_phone;

-- 결과: 서울 담당 분석가는
-- 1) 서울 지역 고객만 보이고 (행 필터)
-- 2) 이메일/전화번호는 부분 마스킹됩니다 (컬럼 마스킹)

-- 4단계 마스킹 함수 예시
CREATE OR REPLACE FUNCTION production.policies.mask_email_v2(email_col STRING)
  RETURNS STRING
  RETURN
    CASE
      -- L0: DPO, 법무팀은 원본 확인 가능
      WHEN IS_ACCOUNT_GROUP_MEMBER('data_protection_officer') THEN email_col
      -- L1: CS팀은 부분 마스킹 (고객 식별은 가능)
      WHEN IS_ACCOUNT_GROUP_MEMBER('customer_support') THEN
        CONCAT(LEFT(email_col, 2), '***@', SPLIT(email_col, '@')[1])
      -- L2: 분석가는 해시값 (조인키로 활용 가능)
      WHEN IS_ACCOUNT_GROUP_MEMBER('analysts') THEN
        SHA2(LOWER(TRIM(email_col)), 256)
      -- L3: 그 외는 완전 마스킹
      ELSE '***@***.***'
    END;