Column Level Security In Snowflake

8 min readApr 16, 2024

To safeguard PII (or PHI or Sensitive Information) column-data/fields, Snowflake has introduced a schema-level masking policy object. This object allows for column-level security and masking/obfuscation to protect sensitive data. This “Column Level Security (CLS) in Snowflake” video tutorial is a comprehensive guide to help snowflake data developers to understand how it can be achived using policy objects.

This tutorial delves beyond SQL syntax and examines all the significant aspects of dynamic data masking, facilitating column-level security in the Snowflake cloud data warehouse platform.

Once you complete this visual guided tour, you would be able to understand and answer following questions

Difference between static and dynamic masking?
How to apply column level masking in snowflake using masking policy
How to re-use policy objects to mask one or more columns within and across the tables.
The data masking and impact on where clause?
Importance of context function when creating masking policies?

Dynamic Data Masking SQL Scripts

Create Database, Schema & Table

use role sysadmin;
-- step-1
-- create databse and schema
create or replace database demo;
create or replace schema my_sch;

-- step-2 Create a customer table and load the data using webui.
create or replace table customer (
 customer_id varchar(),
 first_name varchar(),
 last_name varchar(),
 gender varchar(6),
 govt_id varchar(),
 date_of_birth date,
 annual_income number(38,2),
 credit_card_number varchar(20),
 card_provider varchar(20),
 mobile_number number(20),
 address varchar(),
 created_on timestamp_ntz(9)
);

-- lets describe the table and also view the table under object explorer
desc table customer;

-- now load the sample data ... and query the table.
select * from customer limit 10;

Sample Customer Data (100 Records)

customer_id,customer_first_name,customer_last_name,gender,govt_id,date_of_birth,annual_income,credit_card_number,card_provider,mobile_number,address,created_on
d1f6f86c-029a-4245-bb91-433a6aa79987,Mandy,Evans,Male,378-25-4428,1992-08-22,108103.56,4516-4829-8251-8697,Visa,9164391029,"13550 Morgan Pass Smithburgh, WI 76537",2024-02-04 17:36:43
6a1eccda-a70c-41b5-9978-cec0495cfa4f,Daniel,Maldonado,Female,187-95-6145,1964-12-05,147782.33,4031-7382-4968-2022,American Express,6215881561,"35852 Morris Causeway Lake Seanfurt, AS 24026",2024-01-03 06:34:07
372139f3-5e25-43dd-b65b-9045c5bc647a,Erika,Juarez,Female,493-80-1009,1935-02-24,76017.27,4596-8351-8217-2537,American Express,7286257254,"2818 Dwayne Shoals Blackwellville, KS 66714",2024-03-19 20:44:27
f46d4e1d-c05f-49af-8a5b-257910ed0260,Steven,White,Female,189-04-6110,1978-03-31,41702.97,4622-9836-8651-1731,Mastercard,8196693009,"283 Bradley Crossroad West Kimberlyton, MN 68224",2024-01-21 21:17:44
27fafa5d-1165-4edb-8242-81d3a11908e0,Nathan,Erickson,Male,037-36-7276,1983-06-13,24475.13,4479-4477-0470-3526,Mastercard,9189343318,"PSC 1019, Box 3672 APO AA 46262",2024-02-10 11:55:45
50b7da40-b9b3-4ee7-8cd6-8a77e6c0bc68,Meghan,Jones,Male,309-48-0663,1990-05-01,121852.32,4212-4356-3021-8059,Discover,9814758205,"17293 Hudson Knolls Lake Justin, PR 12530",2024-01-19 06:00:18
811348f1-8caa-497d-b513-6f4187111aa9,Diane,Ortiz,Female,537-02-8520,1953-11-26,40485.83,4927-6705-0658-9052,Mastercard,9127128898,"5642 Cunningham Centers South Stephanieville, RI 23322",2024-01-07 11:00:13
d5c29cea-4f10-4a2e-8d1c-6a5ef0a8c3b4,Jay,Davis,Male,512-61-0965,1990-11-21,50461.15,4352-6983-7238-9830,Discover,8199761201,"31181 Joseph Freeway Apt. 437 Jessicamouth, TN 53394",2024-02-18 09:02:42
d64bc69f-dd71-4d12-b35a-a0baa692beb2,Robin,Rodriguez,Male,031-66-8851,1983-05-09,40351.97,4671-6715-3746-9122,Visa,6217016304,"9208 Johnson Neck Suite 340 Richardsonside, KS 30706",2024-01-18 22:03:35
4053559f-343b-435b-8b3a-ae05eb872685,Samantha,Smith,Male,155-73-9537,1951-05-31,37797.93,4329-9484-0366-9670,Mastercard,7281146961,"481 Mata Squares Suite 260 Lake Rachelville, KY 87464",2024-03-02 02:41:04
64722c12-bb61-4f0c-a9a8-3797e34eb93e,Thomas,Jarvis,Female,705-83-1986,1991-12-01,117820.07,4907-2052-4262-4592,American Express,8166412170,"3970 Lambert Parks New Katrina, MN 90937",2024-02-27 23:55:21

Check Customer Data

use role sysadmin;
select * from customer limit 10;
use role public; -- will not be accessible
select * from customer limit 10;
use role useradmin; -- will not be accessible
select * from customer limit 10;

Grant Privilege & Check Table

-- step-3 granting compute and other privileges to different roles
use role securityadmin;
grant usage on warehouse compute_wh to role sysadmin;
grant usage on warehouse compute_wh to role public;
grant usage on warehouse compute_wh to role useradmin;

grant usage on database demo to role useradmin;
grant usage on database demo to role public;

grant usage on schema demo.my_sch to role public;
grant usage on schema demo.my_sch to role useradmin;

grant select on table demo.my_sch.customer TO ROLE public;
grant select on table demo.my_sch.customer TO ROLE useradmin;

use role sysadmin;
use schema demo.my_sch;
use warehouse compute_wh;

select * from customer;

use role public;
use schema demo.my_sch;
use warehouse compute_wh;

select * from customer;

use role useradmin;
use schema demo.my_sch;
use warehouse compute_wh;

select * from customer;

Creating Dynamic Masking Policy


use role sysadmin;

create or replace masking policy pii_masking_policy as (pii_text string)
    returns string ->
    case
        when current_role() in ('SYSADMIN')
            then pii_text
        when current_role() in ('USERADMIN') then 
            regexp_replace(pii_text,substring(pii_text,1,7),'xxx-xx-')
        else '***Masked***'
end;

-- how to list all policies
show masking policies;

-- how to describe a masking policy
desc masking policy mask_govt_id_pii;

-- get_ddl function support masking policy?
select get_ddl('POLICY','mask_govt_id_pii');

-- where it is stored in information schema
-- account usage schema.

-- map this masking policy to a column in a table.
alter table customer modify column govt_id set masking policy mask_govt_id_pii;

Adding Masking Policy

create or replace masking policy card_number_pii as (card_number string)
    returns string ->
    case
        when current_role() in ('SYSADMIN')
            then card_number
        when current_role() in ('USERADMIN') then regexp_replace(card_number,substring(card_number,1,15),'xxxx-xxxx-xxxx-')
        else '***Card-No-Masked***'
end;
alter table customer modify column credit_card_number set masking policy card_number_pii;

create or replace masking policy dob_pii as (date_of_birth date)
    returns date ->
    case
        when current_role() in ('SYSADMIN')
            then date_of_birth
        else '1999-01-01'::date
end;
alter table customer modify column date_of_birth set masking policy dob_pii;

Conditional Data Masking — Part 02

Customer Data File (10 Rows)

customer_id,customer_first_name,customer_last_name,gender,govt_id,date_of_birth,annual_income,credit_card_number,card_provider,mobile_number,region,address,created_on
d1f6f86c-029a-4245-bb91-433a6aa79987,Mandy,Evans,Female,378-25-4428,1992-08-30,106352.09,4516-4829-8251-8697,American Express,7282841760,Europe,"13550 Etienne Pass Adamburgh, WI 76537",2024-02-04 17:36:43
6a1eccda-a70c-41b5-9978-cec0495cfa4f,Daniel,Maldonado,Male,187-95-6145,1964-12-13,122214.74,4031-7382-4968-2022,American Express,9177901828,Asia-Pacific,"358, Brar Path, North Dumdum 007162",2024-01-03 06:34:07
372139f3-5e25-43dd-b65b-9045c5bc647a,Erika,Juarez,Female,493-80-1009,1935-03-04,104182.97,4596-8351-8217-2537,American Express,8163516356,North America,"2818 Dwayne Shoals Blackwellville, KS 66714",2024-03-19 20:44:27
f46d4e1d-c05f-49af-8a5b-257910ed0260,Steven,White,Female,189-04-6110,1978-04-08,125087.0,4622-9836-8651-1731,Mastercard,8876132656,Asia-Pacific,"35/94, Luthra Street Bhilwara-058891",2024-01-14 04:41:18
b56471c8-e15e-46c1-9f08-199927fafa5d,Cynthia,Hill,Female,455-88-6041,1987-12-23,111953.71,4794-4770-4703-5201,American Express,7210201009,Asia-Pacific,"H.No. 936, Chand Chowk Karnal 456194",2024-03-12 12:53:39
6b547c70-0a5b-4380-9d6f-3010c8bec05c,Jake,Miller,Female,838-40-3806,1978-08-04,131063.06,4302-1805-6503-1728,Visa,7216299221,Europe,"852 Baudoux Well Apt. 183 Wattieztown, WI 68657",2024-01-30 10:30:57
968c80b9-534a-4d58-8818-361096a4087e,Kathryn,Yoder,Male,469-54-7203,1936-08-20,25581.51,4065-8905-7705-5641,Discover,8860902790,Asia-Pacific,90/91 Bhargava Chowk Srikakulam-004381,2024-03-30 09:50:03
6c610ed7-0f13-4e3f-b816-be637fcf8723,Jeffrey,Walker,Female,764-21-6279,1966-03-09,104724.96,4238-9832-7031-1817,American Express,8190831079,Asia-Pacific,33/43 Goyal Chowk Sambhal 781808,2024-04-14 21:31:14
3d242bf4-5e33-484f-ae4e-9557e9072678,Ricardo,Cohen,Female,432-60-1820,1977-11-24,48232.64,4746-9123-9649-2081,American Express,7220962689,Asia-Pacific,"53, Atwal Road, Ambarnath 843134",2024-01-02 03:15:39
95004140-903f-43f6-a6bd-e4db043c8620,Kristina,Glover,Female,337-28-2104,1975-01-03,88868.44,4036-6967-9848-4817,American Express,6218628778,North America,"260 Jessica Village Apt. 456 Woodsfurt, UT 84700",2024-01-24 12:41:58

SQL Scripts

-- step-1 Create a global customer table and load the data using webui.
create or replace table global_customer (
 customer_id varchar(),
 first_name varchar(),
 last_name varchar(),
 gender varchar(6),
 govt_id varchar(),
 date_of_birth date,
 annual_income number(38,2),
 credit_card_number varchar(20),
 card_provider varchar(20),
 mobile number(20),
    region text,
 address varchar(),
 created_on timestamp_ntz(9)
);

select * from global_customer;
desc table global_customer;

--create a masking policy as we have done in our previous chapter.
create or replace masking policy 
    simple_masking_policy as (pii_text string)
    returns string -> 
    to_varchar('**** Masked ****'); 
end;

alter table global_customer 
    modify column govt_id 
    set masking policy simple_masking_policy;

alter table global_customer 
    modify column govt_id 
    unset masking policy;

create or replace masking policy 
    pii_conditional_masking_policy 
    as (pii_text string, region string)
    returns string ->
    case
        when lower(region) != 'europe'
            then pii_text
        else '***Masked***'
    end;

desc masking policy pii_conditional_masking_policy;

alter table global_customer 
    modify column govt_id 
    set masking policy pii_conditional_masking_policy
    using(govt_id, region);

select * from global_customer order ;

show masking policies;

Tag Based Data Masking — Part 03

Create Tag Object

--step-1 create tag
create or replace tag 
    pii_policy_tag 
    comment = 'This is PII policy tag object';
-- run show command to list all tags.
show tags;

Create Table With Tags

-- step-3 Create a customer table and load the data
create or replace table tag_customer (
 customer_id varchar(),
 first_name varchar(),
 last_name varchar(),
 gender varchar(6),
 govt_id varchar(100) with tag (pii_policy_tag = 'PII'),
 date_of_birth date,
 annual_income number(38,2),
 credit_card_number varchar(20) with tag (pii_policy_tag = 'PII'),
 card_provider varchar(20) ,
 mobile varchar(15) with tag (pii_policy_tag = 'PII'),
    region text,
 address varchar(500) with tag (pii_policy_tag = 'PII'),
 created_on timestamp_ntz(9)
);

Sample Customer Data

customer_id,customer_first_name,customer_last_name,gender,govt_id,date_of_birth,annual_income,credit_card_number,card_provider,mobile_number,region,address,created_on
d1f6f86c-029a-4245-bb91-433a6aa79987,Mandy,Evans,Female,378-25-4428,1992-08-30,106352.09,4516-4829-8251-8697,American Express,7282841760,Europe,"13550 Etienne Pass Adamburgh, WI 76537",2024-02-04 17:36:43
6a1eccda-a70c-41b5-9978-cec0495cfa4f,Daniel,Maldonado,Male,187-95-6145,1964-12-13,122214.74,4031-7382-4968-2022,American Express,9177901828,Asia-Pacific,"358, Brar Path, North Dumdum 007162",2024-01-03 06:34:07
372139f3-5e25-43dd-b65b-9045c5bc647a,Erika,Juarez,Female,493-80-1009,1935-03-04,104182.97,4596-8351-8217-2537,American Express,8163516356,North America,"2818 Dwayne Shoals Blackwellville, KS 66714",2024-03-19 20:44:27
f46d4e1d-c05f-49af-8a5b-257910ed0260,Steven,White,Female,189-04-6110,1978-04-08,125087.0,4622-9836-8651-1731,Mastercard,8876132656,Asia-Pacific,"35/94, Luthra Street Bhilwara-058891",2024-01-14 04:41:18
b56471c8-e15e-46c1-9f08-199927fafa5d,Cynthia,Hill,Female,455-88-6041,1987-12-23,111953.71,4794-4770-4703-5201,American Express,7210201009,Asia-Pacific,"H.No. 936, Chand Chowk Karnal 456194",2024-03-12 12:53:39
6b547c70-0a5b-4380-9d6f-3010c8bec05c,Jake,Miller,Female,838-40-3806,1978-08-04,131063.06,4302-1805-6503-1728,Visa,7216299221,Europe,"852 Baudoux Well Apt. 183 Wattieztown, WI 68657",2024-01-30 10:30:57
968c80b9-534a-4d58-8818-361096a4087e,Kathryn,Yoder,Male,469-54-7203,1936-08-20,25581.51,4065-8905-7705-5641,Discover,8860902790,Asia-Pacific,90/91 Bhargava Chowk Srikakulam-004381,2024-03-30 09:50:03
6c610ed7-0f13-4e3f-b816-be637fcf8723,Jeffrey,Walker,Female,764-21-6279,1966-03-09,104724.96,4238-9832-7031-1817,American Express,8190831079,Asia-Pacific,33/43 Goyal Chowk Sambhal 781808,2024-04-14 21:31:14
3d242bf4-5e33-484f-ae4e-9557e9072678,Ricardo,Cohen,Female,432-60-1820,1977-11-24,48232.64,4746-9123-9649-2081,American Express,7220962689,Asia-Pacific,"53, Atwal Road, Ambarnath 843134",2024-01-02 03:15:39
95004140-903f-43f6-a6bd-e4db043c8620,Kristina,Glover,Female,337-28-2104,1975-01-03,88868.44,4036-6967-9848-4817,American Express,6218628778,North America,"260 Jessica Village Apt. 456 Woodsfurt, UT 84700",2024-01-24 12:41:58

Create Masking Policy

-- creating a simple masking object.
create or replace masking policy 
    mask_pii_text_data as (pii_input string)
    returns string ->
    to_varchar('--PII--');
-- associate this masking object with tag.
alter tag pii_policy_tag set masking policy mask_pii_text_data;

-- unset the tag
use role accountadmin;
ALTER TAG finance_policy_tag unset MASKING POLICY tag_pii_masking_policy;

-- if I have multiple tags then how it works
use role sysadmin;
create or replace tag 
    finance_policy_tag 
    comment = 'This is finance policy tag object';

Multiple Tags & Masking Policy Limitations

create or replace table tag_customer_02 (
 customer_id varchar(),
 first_name varchar(),
 last_name varchar(),
 gender varchar(6),
 govt_id varchar(100) with tag ( pii_policy_tag = 'PII'),
 date_of_birth date,
 annual_income number(38,2),
 credit_card_number varchar(20) with tag ( pii_policy_tag = 'PII', finance_policy_tag = 'Card'),
 card_provider varchar(20) with tag ( finance_policy_tag = 'Card'),
 mobile varchar(100) with tag ( pii_policy_tag = 'PII'),
    region text,
 address varchar() with tag ( pii_policy_tag = 'PII'),
 created_on timestamp_ntz(9)
);

Alter Tag To Associate Masking Policy

-- creating another masking policy.
create or replace masking policy 
    tag_card_masking_policy as (input_text string)
    returns string -> 
    to_varchar('xxxx-xxxx-xxxx'); 

show masking policies;
use role accountadmin;

alter tag finance_policy_tag set masking POLICY tag_card_masking_policy;