[Snowflake] Dynamic Data Masking

1. Understanding data masking

• 일부 사용자에게 일부 데이터를 보이지 않도록 설정하는 보안기능

 

2. Creating a masking policy

• 데이터베이스 변경

USE DEMO_DB;

 

• role 변경

USE ROLE ACCOUNTADMIN;

 

• 테이블 생성

create or replace table customers(
  id number,
  full_name varchar, 
  email varchar,
  phone varchar,
  spent number,
  create_date DATE DEFAULT CURRENT_DATE);

 

• 데이터 삽입

insert into customers (id, full_name, email,phone,spent)
values
  (1,'Lewiss MacDwyer','lmacdwyer0@un.org','262-665-9168',140),
  (2,'Ty Pettingall','tpettingall1@mayoclinic.com','734-987-7120',254),
  (3,'Marlee Spadazzi','mspadazzi2@txnews.com','867-946-3659',120),
  (4,'Heywood Tearney','htearney3@patch.com','563-853-8192',1230),
  (5,'Odilia Seti','oseti4@globo.com','730-451-8637',143),
  (6,'Meggie Washtell','mwashtell5@rediff.com','568-896-6138',600);

 

• role 생성 

CREATE OR REPLACE ROLE ANALYST_MASKED;
CREATE OR REPLACE ROLE ANALYST_FULL;

 

• 테이블에 권한 부여

GRANT SELECT ON TABLE DEMO_DB.PUBLIC.CUSTOMERS TO ROLE ANALYST_MASKED;
GRANT SELECT ON TABLE DEMO_DB.PUBLIC.CUSTOMERS TO ROLE ANALYST_FULL;

 

• 스키마에 권한 부여

GRANT USAGE ON SCHEMA DEMO_DB.PUBLIC TO ROLE ANALYST_MASKED;
GRANT USAGE ON SCHEMA DEMO_DB.PUBLIC TO ROLE ANALYST_FULL;

 

• 웨어하우스에 권한 부여

GRANT USAGE ON WAREHOUSE COMPUTE_WH TO ROLE ANALYST_MASKED;
GRANT USAGE ON WAREHOUSE COMPUTE_WH TO ROLE ANALYST_FULL;

 

• 유저에 권한 부여

GRANT ROLE ANALYST_MASKED TO USER (유저 이름);
GRANT ROLE ANALYST_FULL TO USER (유저 이름);

 

• making policy 생성

create or replace masking policy phone 
    as (val varchar) returns varchar ->
            case        
            when current_role() in ('ANALYST_FULL', 'ACCOUNTADMIN') then val
            else '##-###-##'
            end;

 

• masking polity를 특정 열에 설정

ALTER TABLE IF EXISTS CUSTOMERS MODIFY COLUMN phone 
SET MASKING POLICY PHONE;

 

• masking policy 확인

USE ROLE ANALYST_FULL;
SELECT * FROM CUSTOMERS;

 

• masking policy 확인(mask된 데이터 확인)

USE ROLE ANALYST_MASKED;
SELECT * FROM CUSTOMERS;

 

3. Unset & replace policy

• role 변경

USE ROLE ACCOUNTADMIN;

 

1) 여러 열에 정책 적용

• 특정 열에 정책 적용

ALTER TABLE IF EXISTS CUSTOMERS MODIFY COLUMN full_name 
SET MASKING POLICY phone;

 

• 또 다른 열에 적책 적용

ALTER TABLE IF EXISTS CUSTOMERS MODIFY COLUMN phone
SET MASKING POLICY phone;

 

2) 정책 삭제 및 대체

• 정책 삭제 - 정책이 열에 적용되어 있으면 삭제 불가

DROP masking policy phone;

 

• 정책 확인

DESC MASKING POLICY phone;
SHOW MASKING POLICIES;

 

• 정책이 적용된 열 확인 

SELECT * FROM table(information_schema.policy_references(policy_name=>'phone'));

 

• 정책이 적용된 열에 대해 정책 제거 

ALTER TABLE IF EXISTS CUSTOMERS MODIFY COLUMN full_name 
SET MASKING POLICY phone;


ALTER TABLE IF EXISTS CUSTOMERS MODIFY COLUMN email
UNSET MASKING POLICY;


ALTER TABLE IF EXISTS CUSTOMERS MODIFY COLUMN phone
UNSET MASKING POLICY;

• 정책 재 생성

create or replace masking policy names as (val varchar) returns varchar ->
            case
            when current_role() in ('ANALYST_FULL', 'ACCOUNTADMIN') then val
            else CONCAT(LEFT(val,2),'*******')
            end;

 

• 정책 적용

ALTER TABLE IF EXISTS CUSTOMERS MODIFY COLUMN full_name
SET MASKING POLICY names;

 

• 정책 확인

USE ROLE ANALYST_FULL;
SELECT * FROM CUSTOMERS;


USE ROLE ANALYST_MASKED;
SELECT * FROM CUSTOMERS;

 

 

4. Alter an existing policy

• 정책이 적용된 열 확인

USE ROLE ANALYST_MASKED;
SELECT * FROM CUSTOMERS;

USE ROLE ACCOUNTADMIN;

 

• 정책 재설정

alter masking policy phone set body ->
case        
 when current_role() in ('ANALYST_FULL', 'ACCOUNTADMIN') then val
 else '**-**-**'
 end;

 

• 정책이 적용된 열 확인

USE ROLE ANALYST_MASKED;
SELECT * FROM CUSTOMERS;

USE ROLE ACCOUNTADMIN;

 

 

  
5. Real life examples

example 1 : 이메일의 도메인을 보고싶은 경우

• role 변경

USE ROLE ACCOUNTADMIN;

 

• regexp_replace를 사용하여 정책 생성

create or replace masking policy emails as (val varchar) returns varchar ->
case
  when current_role() in ('ANALYST_FULL') then val
  when current_role() in ('ANALYST_MASKED') then regexp_replace(val,'.+\@','*****@') -- leave email domain unmasked
  else '********'
end;

 

• 정책 적용

ALTER TABLE IF EXISTS CUSTOMERS MODIFY COLUMN email
SET MASKING POLICY emails;

 

• 적용한 정책 확인 

USE ROLE ANALYST_FULL;
SELECT * FROM CUSTOMERS;

USE ROLE ANALYST_MASKED;
SELECT * FROM CUSTOMERS;

USE ROLE ACCOUNTADMIN;

 

 

example 2 : 이름을 식별될 수 없는 암호화된 상태로 보고싶을 때

• sha2를 사용하여 정책 생성

create or replace masking policy sha2 as (val varchar) returns varchar ->
case
  when current_role() in ('ANALYST_FULL') then val
  else sha2(val) -- return hash of the column value
end;

 

• 정책 적용

ALTER TABLE IF EXISTS CUSTOMERS MODIFY COLUMN full_name
SET MASKING POLICY sha2;

ALTER TABLE IF EXISTS CUSTOMERS MODIFY COLUMN full_name
UNSET MASKING POLICY;

 

• 적용한 정책 확인

USE ROLE ANALYST_FULL;
SELECT * FROM CUSTOMERS;

USE ROLE ANALYST_MASKED;
SELECT * FROM CUSTOMERS;

USE ROLE ACCOUNTADMIN;

 

example 3 : 날짜를 원하는 숫자로 설정하고 싶을 때

• date_from_parts를 사용하여 정책 생성

create or replace masking policy dates as (val date) returns date ->
case
  when current_role() in ('ANALYST_FULL') then val
  else date_from_parts(0001, 01, 01)::date -- returns 0001-01-01 00:00:00.000
end;

 

• 정책 적용 

ALTER TABLE IF EXISTS CUSTOMERS MODIFY COLUMN create_date 
SET MASKING POLICY dates;

 

• 적용한 정책 확인

USE ROLE ANALYST_FULL;
SELECT * FROM CUSTOMERS;

USE ROLE ANALYST_MASKED;
SELECT * FROM CUSTOMERS;

 

 

 

 

Udemy의 'Snowflake - The Complete Masterclass (2023 Edition)'를 공부한 내용을 바탕으로 작성하였습니다.