Column-Level and Row-Level Security

Snowflake Management, Governance & Collaboration

Emily Melhuish

Technical Curriculum Developer, Snowflake

The Problem With Table-Level Access

Users table with table access

Claro's users table contains names, emails, credit scores, and credit band
Granting SELECT to the analyst role exposes all columns
Campaign analysts don't need raw credit scores
Table-level access control can't make that distinction

What is Column-Level Security?

The column stays in results - only the value changes
Privileged roles see real values; unprivileged roles see masked values
Masking happens at query time based on the querying role = dynamic data masking

Dynamic Data Masking

Diagram showing two tables one with dynamic data masking and one without

Creating and Applying a Masking Policy

CREATE MASKING POLICY mask_credit_score
  AS (val NUMBER) RETURNS NUMBER ->
  CASE
    WHEN IS_ROLE_IN_SESSION('CREDIT_ADMIN') THEN val
    ELSE -9999
  END;

ALTER TABLE core.users
  MODIFY COLUMN credit_score
  SET MASKING POLICY mask_credit_score;

CURRENT_ROLE returns the active primary role

¹ https://docs.snowflake.com/en/sql-reference/sql/create-masking-policy#example-normal-masking-policy

External Tokenization

An alternative to Dynamic Data Masking
Requires a third-party tokenization provider and an External Function
Sensitive values replaced with tokens that are consistent across systems
Useful when the same token must be recognized outside Snowflake

¹ https://docs.snowflake.com/en/user-guide/security-column-ext-token-use

What is Row-Level Security?

Two tables showing how an analyst can only see certain rows

How Row Access Policies Work

Diagram showing a query from US_ANALYST passing through a Row Access Policy on the users table, with only US rows returned, and EU_ANALYST returning only EU rows from the same query

Policy evaluates querying role and applies a filter

Creating and Applying a Row Access Policy

CREATE ROW ACCESS POLICY region_filter
  AS (region_col VARCHAR) RETURNS BOOLEAN ->
  CASE
    WHEN IS_ROLE_IN_SESSION('US_ANALYST') THEN region_col = 'US'
    WHEN IS_ROLE_IN_SESSION('EU_ANALYST') THEN region_col = 'EU'
    ELSE FALSE
  END;

ALTER TABLE core.users
  ADD ROW ACCESS POLICY region_filter ON (region);

Using Data Masking and Row Access Policies Together

Dynamic data masking and row access policies

Let's practice!

Snowflake Management, Governance & Collaboration