Snowflake Data Governance — Ensure Secure and Granular Access with Snowflake Policies!

Snowflake offers a comprehensive suite of data access policies to ensure secure, precise, and flexible data management. Snowflake Row Access policies, Dynamic Data masking, Conditional masking, Projection policies and Aggregation policies offer powerful tools for managing data security and access control. Discover the details of these features that enable organizations maintain the privacy of individuals and sensitive data, all while maintaining the flexibility and performance needed for modern data management.

Data Preparation

create database security;

create or replace table cust_sales
(spend_id integer, spend_amt integer, cust_name text, cust_id varchar(20), pan_id varchar(20), aadhar_no VARCHAR(14), phn_no string, region_id integer, region text);

insert into cust_sales (spend_id, spend_amt, cust_name, cust_id, pan_id, aadhar_no, phn_no, region_id, region)
values
(1, 100000, 'Cust1', 'S123', 'ABCDE1234F', '1111–2222–3333', '123–456–7890', 1, 'North America'),
(2, 200000, 'Cust2', 'G456', 'BCDEF2345G', '2222–3333–4444', '234–567–8901', 3, 'Asia'),
(3, 300000, 'Cust3', 'P789', 'CDEFG3456H', '3333–4444–5555', '345–678–9012', 3, 'Asia'),
(4, 400000, 'Cust4', 'G123', 'DEFGH4567I', '4444–5555–6666', '456–789–0123', 2, 'Europe'),
(5, 500000, 'Cust5', 'P123', 'EFGHI5678J', '5555–6666–7777', '567–890–1234', 1, 'North America'),
(6, 600000, 'Cust6', 'P456', 'FGHIJ6789K', '6666–7777–8888', '678–901–2345', 3, 'Asia');

create role R1_ACCESS;
create role R2_ACCESS;
create role R3_ACCESS;
create role GLOBAL_ACCESS;
create role SUPER_ACCESS;

GRANT USAGE ON DATABASE SECURITY TO ROLE PUBLIC;
GRANT USAGE ON SCHEMA SECURITY.PUBLIC TO ROLE PUBLIC;
GRANT USAGE ON WAREHOUSE DEMO_WH TO ROLE PUBLIC;

GRANT SELECT ON cust_sales TO ROLE R1_ACCESS;
GRANT SELECT ON cust_sales TO ROLE R2_ACCESS;
GRANT SELECT ON cust_sales TO ROLE R3_ACCESS;
GRANT SELECT ON cust_sales TO ROLE GLOBAL_ACCESS;
GRANT SELECT ON cust_sales TO ROLE SUPER_ACCESS;

select CURRENT_USER(); 

GRANT ROLE R1_ACCESS TO USER SFTRAINING;
GRANT ROLE R2_ACCESS TO USER SFTRAINING;
GRANT ROLE R3_ACCESS TO USER SFTRAINING;
GRANT ROLE GLOBAL_ACCESS TO USER SFTRAINING;
GRANT ROLE SUPER_ACCESS TO USER SFTRAINING;

create or replace table region_role_mapping (
region_id integer,
role string
);

insert into region_role_mapping (region_id, role) values
('1', 'R1_ACCESS'),
('2', 'R2_ACCESS'),
('3', 'R3_ACCESS'),
('1', 'GLOBAL_ACCESS'),
('2', 'GLOBAL_ACCESS'),
('3', 'GLOBAL_ACCESS'),
('1', 'SUPER_ACCESS'),
('2', 'SUPER_ACCESS'),
('3', 'SUPER_ACCESS');


create or replace ROW ACCESS POLICY region_level_access 
as (region_filter int) returns boolean ->
exists (
select 1
from region_role_mapping e
where e.region_id = region_filter
and e.role = current_role());

alter table cust_sales add row access policy region_level_access on (region_id);
-- alter table cust_sales drop row access policy region_level_access;

USE ROLE R1_ACCESS;
select * from cust_sales;

USE ROLE R2_ACCESS;
select * from cust_sales;

USE ROLE R3_ACCESS;
select * from cust_sales;

USE ROLE GLOBAL_ACCESS;
select * from cust_sales;

USE ROLE SUPER_ACCESS;
select * from cust_sales; 

create or replace MASKING POLICY cust_aadhar_mask
as (val string) returns string ->
case
when current_role() in
('SUPER_ACCESS') then val
when current_role() in
('GLOBAL_ACCESS') then regexp_replace(val,substring(val,1,10),'xxxx-xxxx-')
else 'xxxx-xxxx-xxxx'
end;

create or replace MASKING POLICY cust_phone_mask
as (val string) returns string ->
case
when current_role() in
('SUPER_ACCESS') then val
else regexp_replace(val,substring(val,1,8),'xxx-xxx-')
end;

ALTER TABLE cust_sales MODIFY COLUMN aadhar_no SET MASKING POLICY cust_aadhar_mask;
ALTER TABLE cust_sales MODIFY COLUMN phn_no SET MASKING POLICY cust_phone_mask;

-- ALTER TABLE cust_sales MODIFY COLUMN aadhar_no UNSET MASKING POLICY;
-- ALTER TABLE cust_sales MODIFY COLUMN phn_no UNSET MASKING POLICY;

USE ROLE R1_ACCESS;
select * from cust_sales;

USE ROLE GLOBAL_ACCESS;
select * from cust_sales;

USE ROLE SUPER_ACCESS;
select * from cust_sales;

create or replace MASKING policy cust_pan_mask 
as (pan_id string, cust_id string) returns string ->
  case
    when current_role() = 'SUPER_ACCESS' and substr(cust_id,1,1) = 'P' then pan_id
    else '**********'
end;

ALTER TABLE IF EXISTS cust_sales MODIFY COLUMN pan_id 
SET MASKING POLICY cust_pan_mask USING (pan_id, cust_id);

-- ALTER TABLE cust_sales MODIFY COLUMN pan_id UNSET MASKING POLICY;

select * from cust_sales;

USE ROLE GLOBAL_ACCESS;
select * from cust_sales;

USE ROLE SUPER_ACCESS;
select * from cust_sales;

create or replace PROJECTION POLICY cust_aadhar_projection
as () RETURNS PROJECTION_CONSTRAINT ->
CASE
  WHEN CURRENT_ROLE() = 'SUPER_ACCESS'
    THEN PROJECTION_CONSTRAINT(ALLOW => true)
  ELSE PROJECTION_CONSTRAINT(ALLOW => false)
END;


create or replace PROJECTION POLICY cust_pan_projection
as () RETURNS PROJECTION_CONSTRAINT ->
CASE
  WHEN CURRENT_ROLE() = 'SUPER_ACCESS'
    THEN PROJECTION_CONSTRAINT(ALLOW => true)
  ELSE PROJECTION_CONSTRAINT(ALLOW => false)
END;

ALTER TABLE cust_sales MODIFY COLUMN aadhar_no SET PROJECTION POLICY cust_aadhar_projection;
ALTER TABLE cust_sales MODIFY COLUMN pan_id SET PROJECTION POLICY cust_pan_projection;

-- ALTER TABLE cust_sales MODIFY COLUMN aadhar_no UNSET PROJECTION POLICY;
-- ALTER TABLE cust_sales MODIFY COLUMN pan_id UNSET PROJECTION POLICY;

select * from cust_sales;
select cust_id, pan_id, aadhar_no from cust_sales;
select spend_id, spend_amt, cust_name, cust_id, phn_no, region_id, region from cust_sales;

use role super_access;
select cust_id, pan_id, aadhar_no from cust_sales;

create or replace AGGREGATION POLICY cust_sales_agg_policy
as () RETURNS AGGREGATION_CONSTRAINT ->
CASE
WHEN CURRENT_ROLE() = 'SUPER_ACCESS'
THEN NO_AGGREGATION_CONSTRAINT()
ELSE AGGREGATION_CONSTRAINT(MIN_GROUP_SIZE => 2)
END;

ALTER TABLE cust_sales SET aggregation POLICY cust_sales_agg_policy;
-- ALTER TABLE cust_sales UNSET aggregation POLICY;

select * from cust_sales;
select max(spend_amt) from cust_sales;
select sum(spend_amt) from cust_sales;

SELECT sum(spend_amt) FROM cust_sales where region_id = 2;
SELECT region_id, sum(spend_amt) as spend_sum_agg from cust_sales group by 1;


insert into cust_sales  (spend_id, spend_amt, cust_name, cust_id, pan_id, aadhar_no, phn_no, region_id, region)
values
(7, 700000, 'Cust7', 'P890', 'GHIJK7890L', '7777-8888-9999', '789-012-3456', 4, 'Africa');

delete from cust_sales where region_id = 4;

Policies List

show row access policies;
show masking policies;
show projection policies;
show aggregation policies;

References: https://docs.snowflake.com/

Follow and Clap if you like the content and feel free to ask if you have any questions in the comments. I will be more than happy to assist and guide you.