Snowflake masking and row access policies: protect PII columns and rows by role

#Goal

Analysts should see ***MASKED*** in place of email addresses and only the rows for their own region, while the pipeline role and a small PII group see everything. You want this enforced on the table itself, so it applies through every view, BI tool and ad hoc query, without keeping separate secure-view copies of each table.

Snowflake has two features for this:

• A masking policy is attached to a column. It rewrites the value at query time, depending on context such as the role.
• A row access policy is attached to a table or view. It returns TRUE or FALSE for each row, and rows that return FALSE are hidden.

Both are schema-level objects. You create them once and attach them to many tables.

#Set up ownership first

Keep policies in a dedicated schema owned by a governance role, so table owners can't quietly detach them.

USE ROLE SECURITYADMIN;
CREATE ROLE IF NOT EXISTS GOVERNANCE_ADMIN;
GRANT ROLE GOVERNANCE_ADMIN TO ROLE SYSADMIN;

USE ROLE SYSADMIN;
CREATE SCHEMA IF NOT EXISTS GOVERNANCE.POLICIES;
GRANT USAGE ON DATABASE GOVERNANCE TO ROLE GOVERNANCE_ADMIN;
GRANT USAGE ON SCHEMA GOVERNANCE.POLICIES TO ROLE GOVERNANCE_ADMIN;
GRANT CREATE MASKING POLICY, CREATE ROW ACCESS POLICY, CREATE TAG
  ON SCHEMA GOVERNANCE.POLICIES TO ROLE GOVERNANCE_ADMIN;

USE ROLE ACCOUNTADMIN;
GRANT APPLY MASKING POLICY ON ACCOUNT TO ROLE GOVERNANCE_ADMIN;
GRANT APPLY ROW ACCESS POLICY ON ACCOUNT TO ROLE GOVERNANCE_ADMIN;
GRANT APPLY TAG ON ACCOUNT TO ROLE GOVERNANCE_ADMIN;

#Mask a column

#Create the policy

Use IS_ROLE_IN_SESSION() rather than CURRENT_ROLE() = '...'. It also returns true for roles the user inherits and for secondary roles, so it follows the role hierarchy you already have.

USE ROLE GOVERNANCE_ADMIN;

CREATE OR REPLACE MASKING POLICY GOVERNANCE.POLICIES.MASK_EMAIL
  AS (val STRING) RETURNS STRING ->
  CASE
    WHEN IS_ROLE_IN_SESSION('PII_READER') THEN val
    WHEN IS_ROLE_IN_SESSION('ANALYST')    THEN REGEXP_REPLACE(val, '.+@', '***@')
    ELSE '***MASKED***'
  END
  COMMENT = 'Full email for PII_READER, domain only for ANALYST';

The return type must match the input type. For numbers or dates, write a separate policy that returns NULL or a fixed value of the same type.

#Attach it

ALTER TABLE SALES.CRM.CUSTOMERS
  MODIFY COLUMN EMAIL SET MASKING POLICY GOVERNANCE.POLICIES.MASK_EMAIL;

A column can have only one masking policy. To swap it for another policy in one step, add FORCE:

ALTER TABLE SALES.CRM.CUSTOMERS
  MODIFY COLUMN EMAIL SET MASKING POLICY GOVERNANCE.POLICIES.MASK_EMAIL_V2 FORCE;

#Conditional masking on another column

A masking policy can take extra arguments from other columns in the same row. For example, show the value only when the customer has given consent:

CREATE OR REPLACE MASKING POLICY GOVERNANCE.POLICIES.MASK_PHONE_CONSENT
  AS (val STRING, consent BOOLEAN) RETURNS STRING ->
  CASE WHEN consent OR IS_ROLE_IN_SESSION('PII_READER') THEN val ELSE NULL END;

ALTER TABLE SALES.CRM.CUSTOMERS MODIFY COLUMN PHONE
  SET MASKING POLICY GOVERNANCE.POLICIES.MASK_PHONE_CONSENT USING (PHONE, MARKETING_CONSENT);

#Filter rows by region

Put the role-to-region rules in a mapping table, so you can change access without changing the policy.

CREATE TABLE IF NOT EXISTS GOVERNANCE.POLICIES.REGION_ACCESS (
  ROLE_NAME STRING, REGION STRING);

INSERT INTO GOVERNANCE.POLICIES.REGION_ACCESS VALUES
  ('ANALYST_EU', 'EU'), ('ANALYST_US', 'US');

CREATE OR REPLACE ROW ACCESS POLICY GOVERNANCE.POLICIES.RAP_REGION
  AS (region STRING) RETURNS BOOLEAN ->
  IS_ROLE_IN_SESSION('PII_READER')
  OR IS_ROLE_IN_SESSION('ETL_ROLE')
  OR EXISTS (
    SELECT 1 FROM GOVERNANCE.POLICIES.REGION_ACCESS m
    WHERE IS_ROLE_IN_SESSION(m.ROLE_NAME) AND m.REGION = region);

ALTER TABLE SALES.CRM.ORDERS ADD ROW ACCESS POLICY GOVERNANCE.POLICIES.RAP_REGION ON (REGION);

Warning

Always let the pipeline role through, as ETL_ROLE is above. If the role that runs MERGE, UPDATE or DELETE can't see the rows, it silently skips them. You get missed updates and duplicate inserts, not an error.

To replace a row access policy, drop the old one and add the new one in a single statement:

ALTER TABLE SALES.CRM.ORDERS
  DROP ROW ACCESS POLICY GOVERNANCE.POLICIES.RAP_REGION,
  ADD ROW ACCESS POLICY GOVERNANCE.POLICIES.RAP_REGION_V2 ON (REGION);

#Scale it with tag-based masking

Attaching a policy to each column doesn't scale beyond a few tables. Assign masking policies to a tag instead, then tag the columns. New columns are protected as soon as they're tagged.

CREATE TAG IF NOT EXISTS GOVERNANCE.POLICIES.PII ALLOWED_VALUES 'email', 'phone', 'name';

ALTER TAG GOVERNANCE.POLICIES.PII SET MASKING POLICY GOVERNANCE.POLICIES.MASK_EMAIL;

ALTER TABLE SALES.CRM.LEADS MODIFY COLUMN CONTACT_EMAIL
  SET TAG GOVERNANCE.POLICIES.PII = 'email';

A tag can carry one masking policy per data type, for example one for STRING and one for NUMBER. If a column has both a policy attached directly and one inherited from a tag, the direct one wins.

#Check what is protected

For one table, using the INFORMATION_SCHEMA table function (results are current):

SELECT POLICY_NAME, POLICY_KIND, REF_COLUMN_NAME, POLICY_STATUS
FROM TABLE(SALES.INFORMATION_SCHEMA.POLICY_REFERENCES(
  REF_ENTITY_NAME => 'SALES.CRM.CUSTOMERS', REF_ENTITY_DOMAIN => 'table'));

For the whole account, using ACCOUNT_USAGE (this view has some latency, so recent changes can take a while to show up):

SELECT POLICY_NAME, POLICY_KIND, REF_DATABASE_NAME, REF_SCHEMA_NAME,
       REF_ENTITY_NAME, REF_COLUMN_NAME
FROM SNOWFLAKE.ACCOUNT_USAGE.POLICY_REFERENCES
ORDER BY REF_DATABASE_NAME, REF_ENTITY_NAME;

To see a policy's body, run DESCRIBE MASKING POLICY GOVERNANCE.POLICIES.MASK_EMAIL;. To test it as a given role, run USE ROLE ANALYST_EU; and then query the table.

#Common problems

Caution

CREATE OR REPLACE on a policy that is attached to columns fails. Use ALTER MASKING POLICY ... SET BODY -> ... to change the logic in place. Any change to a policy body takes effect for every query on every table that uses the policy, so test it on a clone first.

#Prevent it next time

• Keep policy DDL in Git and deploy it through CI. Changes to who can see PII then get reviewed like code.
• Tag sensitive columns as part of onboarding a table, and use tag-based masking so new tables are protected without extra work.
• Run a scheduled check that joins your PII column list to ACCOUNT_USAGE.POLICY_REFERENCES and alerts on any column that has no policy.