Skip to content
Tutorials Center
My learning

Topics

Lesson 28 of 38 ยท Security and Governance

Dynamic Data Masking and Row Access Policies

Protect sensitive data in Snowflake with dynamic data masking, row access policies, tags and classification so different roles see different values from one table.

Giving a role SELECT on a table exposes every row and every column. Real data is rarely that uniform: support agents may see the last four digits of a card, analysts should not see emails at all, and a regional manager should only see their own region. Snowflake solves this with policies attached to columns and tables, so the same query returns different results depending on who runs it, with no copies of the data.

These policies run inside Snowflake, which this site cannot run. Their SQL is shown read-only, and each concept is modelled with views and a "current role" table in SQLite so you can run the logic.

Dynamic data masking

A masking policy is a function attached to a column. At query time it decides what the user sees, based on context such as CURRENT_ROLE(). The stored data never changes.

SQL
CREATE MASKING POLICY email_mask AS (val STRING) RETURNS STRING ->
  CASE
    WHEN CURRENT_ROLE() IN ('PII_ADMIN') THEN val
    WHEN CURRENT_ROLE() IN ('SUPPORT')   THEN REGEXP_REPLACE(val, '.+@', '*****@')
    ELSE '*** MASKED ***'
  END;

ALTER TABLE customers MODIFY COLUMN email SET MASKING POLICY email_mask;

Now SELECT email FROM customers returns the real address for PII_ADMIN, a partially hidden one for SUPPORT and a placeholder for everyone else. Notes:

  • The input type and the return type of the policy must match the column's type.
  • Policy logic can call IS_ROLE_IN_SESSION('ROLE') instead of CURRENT_ROLE(), which also works with role inheritance.
  • A policy can read other columns (conditional masking): AS (val STRING, region STRING).
  • One policy can be reused on many columns; changing the policy updates them all.
  • Masking policies and ALTER ... UNSET MASKING POLICY are privileges you grant to a small governance role (for example APPLY MASKING POLICY).
  • Masked values still work in joins and filters in a controlled way, so be careful: a user who cannot see a value might infer it by filtering. Keep policy design in mind, and consider tokenized values.

Alternatives to masking: tokenization through external functions, and differential privacy policies for aggregate-only access.

Modelling masking

SQL One table, different views per role (stand-in) ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The three result sets show the same rows with three levels of detail. Snowflake attaches the logic to the column itself, so there is no view to bypass, and every access path (views, shares, BI tools) obeys it.

Row access policies

A row access policy decides which rows a user may see. It is a boolean function over one or more columns, applied to the table:

SQL
CREATE TABLE region_map (role_name STRING, region STRING);

CREATE ROW ACCESS POLICY region_filter AS (row_region STRING) RETURNS BOOLEAN ->
  IS_ROLE_IN_SESSION('GLOBAL_MANAGER')
  OR EXISTS (
    SELECT 1 FROM region_map m
    WHERE m.role_name = CURRENT_ROLE() AND m.region = row_region
  );

ALTER TABLE sales ADD ROW ACCESS POLICY region_filter ON (region);

The mapping table lets business owners change access by editing data, not code. A table can have one row access policy, applied before masking policies.

SQL Row filtering by role (stand-in) ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The Berlin manager sees only Berlin departments; the global manager sees all. This is exactly the shape of a real row access policy.

Tags, classification and tag-based masking

Policies are only as good as your knowledge of where the sensitive data is. Snowflake helps:

  • Object tagging: attach key-value labels such as pii = 'email' to tables and columns, in any quantity.
  • Classification: SYSTEM$CLASSIFY and automatic classification detect likely personal data (names, emails, national IDs) and propose semantic tags.
  • Tag-based masking: attach a masking policy to a tag, and every column carrying that tag is protected automatically, including new ones.
SQL
CREATE TAG pii ALLOWED_VALUES 'email', 'phone', 'name';
ALTER TAG pii SET MASKING POLICY email_mask;       -- for STRING columns
ALTER TABLE customers MODIFY COLUMN email SET TAG pii = 'email';

Together with ACCESS_HISTORY (which records which columns each query read) and POLICY_REFERENCES (which objects have which policy), you can answer audit questions: who read personal data last quarter, and is every tagged column protected?

Design guidance

  1. Classify first; protect by tag, not column by column.
  2. Keep policy logic short; call a mapping table for anything that changes.
  3. Test with each role (USE ROLE) before releasing.
  4. Remember the owner role of the table may need exemptions for maintenance.
  5. Combine with secure views and sharing when data leaves your account.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does a masking policy change?

    Masking is applied at query time; the stored data is untouched.
  2. 2. A row access policy returns what type?

    It returns TRUE for rows the user may see and FALSE for hidden rows.
  3. 3. Why is tag-based masking useful?

    Attaching the policy to the tag scales protection across many columns without repeating statements.
  4. 4. Where can you see which columns a query actually read?

    ACCESS_HISTORY records the objects and columns accessed by each query, which supports audits.

Summary

  • Masking policies change column values per context; row access policies filter rows.
  • Both attach to the data, so every access path follows them.
  • Tags and classification let you protect sensitive data at scale.
  • Use mapping tables, test with every role, audit with ACCESS_HISTORY.

Next: Secure views and sharing