Row Access and Masking Policies

Row access policies and masking policies are Snowflake's data-level access controls. A masking policy rewrites the value of a single column at query time based on context (role, session attributes, business hours); a row access policy appends a predicate to the query plan so unauthorized rows never appear in the result set. Both are evaluated by the optimizer, both run inline with the query, and both let you keep one physical table serving every persona instead of fragmenting into per-role views.

1. Query Flow

The diagram shows where row and column controls plug into the query lifecycle. Row access runs before masking — there is no point masking columns of rows the caller will never see. 

┌──────────────────────────────────────────────────────────────────────┐
│                  QUERY EVALUATION PIPELINE                           │
│  ┌────────────────────────────────────────────────────────────────┐  │
│  │  1. User issues SELECT statement                               │  │
│  └────────────────────────────┬───────────────────────────────────┘  │
│                               ▼                                      │
│  ┌────────────────────────────────────────────────────────────────┐  │
│  │  2. Snowflake resolves CURRENT_ROLE / IS_ROLE_IN_SESSION       │  │
│  └────────────────────────────┬───────────────────────────────────┘  │
│                               ▼                                      │
│  ┌────────────────────────────────────────────────────────────────┐  │
│  │  3. Row Access Policy evaluated  → predicate appended to WHERE │  │
│  └────────────────────────────┬───────────────────────────────────┘  │
│                               ▼                                      │
│  ┌────────────────────────────────────────────────────────────────┐  │
│  │  4. Masking Policies evaluated  → CASE wrapped around column   │  │
│  └────────────────────────────┬───────────────────────────────────┘  │
│                               ▼                                      │
│  ┌────────────────────────────────────────────────────────────────┐  │
│  │  5. Filtered, masked result set returned to client             │  │
│  └────────────────────────────────────────────────────────────────┘  │
└──────────────────────────────────────────────────────────────────────┘

2. Masking Policies

A masking policy is a UDF whose first argument is the column value being masked. It must return the same data type. Snowflake injects the policy as a CASE expression around every reference to the column. 


USE ROLE SECURITYADMIN;

CREATE OR REPLACE MASKING POLICY pii_email_mask AS
  (val STRING) RETURNS STRING ->
    CASE
      WHEN CURRENT_ROLE() IN ('PII_READER', 'COMPLIANCE')
        THEN val
      WHEN CURRENT_ROLE() = 'ANALYST'
        THEN REGEXP_REPLACE(val, '.+@', '****@')
      ELSE '***REDACTED***'
    END;

ALTER TABLE customers
  MODIFY COLUMN email
  SET MASKING POLICY pii_email_mask;

Conditional Masking by Role

IS_ROLE_IN_SESSION is preferred over CURRENT_ROLE when secondary roles are in play — it returns true if the role appears anywhere in the active role set, which is what most enterprise identity stacks want. 


CREATE OR REPLACE MASKING POLICY ssn_mask AS
  (val STRING) RETURNS STRING ->
    CASE
      WHEN IS_ROLE_IN_SESSION('PII_READER')          THEN val
      WHEN IS_ROLE_IN_SESSION('FRAUD_INVESTIGATOR')  THEN 'XXX-XX-' || RIGHT(val, 4)
      ELSE SHA2(val, 256)   -- deterministic pseudonym for joins
    END;

-- Conditional masking: peek at another column on the same row
CREATE OR REPLACE MASKING POLICY salary_mask AS
  (val NUMBER, dept STRING) RETURNS NUMBER ->
    CASE
      WHEN CURRENT_ROLE() = 'HR_ADMIN'                     THEN val
      WHEN CURRENT_ROLE() = 'MANAGER' AND dept = 'ENG'     THEN val
      ELSE NULL
    END;

ALTER TABLE employees
  MODIFY COLUMN salary
  SET MASKING POLICY salary_mask USING (salary, department);

3. Row Access Policies

A row access policy is a UDF that returns boolean for each row. The optimizer pushes this predicate into the table scan, so unauthorized rows are never read off disk. 


CREATE OR REPLACE ROW ACCESS POLICY region_access AS
  (region STRING) RETURNS BOOLEAN ->
    CASE
      WHEN IS_ROLE_IN_SESSION('GLOBAL_READER')  THEN TRUE
      WHEN IS_ROLE_IN_SESSION('EMEA_READER')    THEN region = 'EMEA'
      WHEN IS_ROLE_IN_SESSION('AMER_READER')    THEN region = 'AMER'
      WHEN IS_ROLE_IN_SESSION('APAC_READER')    THEN region = 'APAC'
      ELSE FALSE
    END;

ALTER TABLE orders
  ADD ROW ACCESS POLICY region_access ON (shipping_region);

Mapping Tables for Row Filtering

Hard-coding role-to-region in the policy doesn't scale. Use a mapping table: a small lookup of (role, allowed_value) pairs the policy joins against. Updating who sees what becomes a SQL update, not a DDL change. 


CREATE OR REPLACE TABLE governance.role_region_map (
  role_name   STRING,
  region      STRING
);

INSERT INTO governance.role_region_map VALUES
  ('EMEA_READER',   'EMEA'),
  ('AMER_READER',   'AMER'),
  ('APAC_READER',   'APAC'),
  ('GLOBAL_READER', 'EMEA'),
  ('GLOBAL_READER', 'AMER'),
  ('GLOBAL_READER', 'APAC');

CREATE OR REPLACE ROW ACCESS POLICY region_via_map AS
  (region STRING) RETURNS BOOLEAN ->
    EXISTS (
      SELECT 1
      FROM   governance.role_region_map m
      WHERE  m.region    = region
        AND  IS_ROLE_IN_SESSION(m.role_name)
    );

ALTER TABLE orders
  DROP ROW ACCESS POLICY region_access;

ALTER TABLE orders
  ADD ROW ACCESS POLICY region_via_map ON (shipping_region);

A single physical orders table now serves every regional persona. Adding a new region is one INSERT into the mapping table. The policy itself never changes.

4. Policies on Views and Shares

Policies attached to a base table propagate through views and secure data sharing. A consumer of a Snowflake share sees the data filtered by the producer's policies under the consumer's role context — the value of CURRENT_ROLE() in the policy resolves on the consumer side. 


-- Policies follow the column through views
CREATE SECURE VIEW vw_customer_summary AS
SELECT customer_id, email, region, total_orders
FROM   customers;
-- email is still masked, region is still filtered, no extra DDL needed

-- Apply at the view level if the column is computed only in the view
ALTER VIEW vw_customer_summary
  MODIFY COLUMN email
  SET MASKING POLICY pii_email_mask;

5. Auditing Policy Coverage

Two ACCOUNT_USAGE views are the source of truth for "is this column actually protected?". 


-- Every column with a masking policy attached
SELECT ref_database_name, ref_schema_name, ref_entity_name,
       ref_column_name, policy_name, policy_kind
FROM   SNOWFLAKE.ACCOUNT_USAGE.POLICY_REFERENCES
WHERE  policy_kind = 'MASKING_POLICY';

-- Every table with a row access policy
SELECT ref_database_name, ref_schema_name, ref_entity_name,
       policy_name, policy_kind
FROM   SNOWFLAKE.ACCOUNT_USAGE.POLICY_REFERENCES
WHERE  policy_kind = 'ROW_ACCESS_POLICY';

-- Find PII-tagged columns that have NO masking policy attached
SELECT t.object_database, t.object_schema, t.object_name, t.column_name
FROM   SNOWFLAKE.ACCOUNT_USAGE.TAG_REFERENCES t
LEFT   JOIN SNOWFLAKE.ACCOUNT_USAGE.POLICY_REFERENCES p
  ON   t.object_database = p.ref_database_name
  AND  t.object_schema   = p.ref_schema_name
  AND  t.object_name     = p.ref_entity_name
  AND  t.column_name     = p.ref_column_name
WHERE  t.tag_name = 'PII'
  AND  p.policy_name IS NULL;

6. Operational Pitfalls

↑ Back to Top

© Kevin Thomas Luzbetak, MSc