Data Governance · Topic 4 of 7

Column-level Security and Masking

Protect Snowflake columns with Dynamic Data Masking, external tokenization, projection policies and role based masking, and pick the right one.

  • 5 min
In a nutshell
  • Masking policy: signature plus matching return type; one per column; evaluated at query time; Enterprise.
  • External tokenization: tokens stored, detokenized through an external function inside the policy.
  • Projection policy: PROJECTION_CONSTRAINT(ALLOW => ...); filter and join allowed, return blocked.

What it is

Column-level security controls what a user sees in a column, not whether they can query the table. Snowflake offers three tools, and the exam asks you to choose between them.

  • Dynamic Data Masking changes the value returned, for example ***-**-1234.
  • External tokenization replaces values with tokens made by a service outside Snowflake, and swaps them back at query time for authorized users.
  • Projection policies let users filter or join on a column but never return it.

All three need Enterprise edition or higher. The data in storage is never changed, only what the query returns.

User querySELECT emailpolicy checksRole and contextCURRENT_ROLEdecidesReal valueor masked value or token

How it works

Dynamic Data Masking

A masking policy is a schema level object with a signature (the input value and its type) and a return type that must match. Its body decides, at query time, what to return.

CREATE MASKING POLICY mask_email AS (val STRING) RETURNS STRING ->
  CASE
    WHEN CURRENT_ROLE() IN ('HR_ADMIN') THEN val
    ELSE REGEXP_REPLACE(val, '.+@', '*****@')
  END;

ALTER TABLE customers MODIFY COLUMN email SET MASKING POLICY mask_email;
ALTER TABLE customers MODIFY COLUMN email UNSET MASKING POLICY;
  • A column can have one masking policy, and one policy can protect many columns.
  • The policy is evaluated for every query, using the session context such as CURRENT_ROLE() or IS_ROLE_IN_SESSION().
  • Conditional masking passes extra columns: AS (val STRING, region STRING) and ... SET MASKING POLICY p USING (email, region).
  • The policy can be set on a table, view or tag, and the data stays unchanged.
  • Masked output is the same whether the user reads the column directly, through a view or in a join.
TechniqueResult
Full mask'*****'
Partial maskKeep the last 4 digits
HashSHA2(val), stays joinable
NULLValue hidden
Exam tip

A hash keeps a masked column joinable, which is useful when analysts match customers across tables without seeing the real value.

External tokenization

External tokenization calls a service outside Snowflake from inside the masking policy, using an external function.

CREATE MASKING POLICY detokenize AS (val STRING) RETURNS STRING ->
  CASE
    WHEN CURRENT_ROLE() IN ('FRAUD_TEAM') THEN ext_detokenize(val)
    ELSE val        -- stays a token for everyone else
  END;
  • The stored value is a token, created by a tokenization provider before load.
  • Authorized roles see the real value, because the policy detokenizes at query time through an external function and an API integration.
  • Other roles see the token, which is useless on its own.
  • The provider holds the mapping. Snowflake never stores the real value.
Dynamic Data MaskingExternal tokenization
Stored valueReal valueToken
Who masksSnowflake policyExternal service
ReversalPolicy shows the real value to chosen rolesPolicy calls the service to detokenize
Use whenHide values by role inside SnowflakeA company rule says real values must never be stored in Snowflake
Raw valuebefore loadtokenize outsideTokenstored in tablepolicy for authorized roleExternal functiondetokenizereturnsReal valueto that role only

Projection policies

A projection policy prevents a column from being projected, meaning returned in the SELECT list. The column can still be used in WHERE, JOIN and GROUP BY.

CREATE PROJECTION POLICY block_ssn AS ()
  RETURNS PROJECTION_CONSTRAINT ->
  CASE WHEN CURRENT_ROLE() = 'PRIVACY_OFFICER'
       THEN PROJECTION_CONSTRAINT(ALLOW => TRUE)
       ELSE PROJECTION_CONSTRAINT(ALLOW => FALSE)
  END;

ALTER TABLE customers MODIFY COLUMN ssn SET PROJECTION POLICY block_ssn;

This allows a question like "how many customers have SSN 123-45-6789?" while the SSN never leaves in a result set. It is useful for identifiers that analysts must filter and join on but never read.

Watch out

A projection policy does not mask. If projection is blocked, selecting the column fails, while a masking policy returns a hidden value. Do not use it when users need to see a masked version.

Masking with RBAC

RBAC decides which roles can see which data. Masking policies read the active role and return clear or hidden values.

  • Separation of duties. A policy administrator role holds CREATE MASKING POLICY and APPLY MASKING POLICY, so table owners do not decide who sees clear values.
  • Role hierarchy. IS_ROLE_IN_SESSION('ANALYST_FULL') is true if the current role inherits that role. A plain comparison with CURRENT_ROLE() matches only the exact role.
  • Everyone is included. The policy applies to all roles, including ACCOUNTADMIN, unless the body allows them.
  • Views. Roles using a view get the masking of the underlying column based on the role in the session.
Policy admin roleowns masking policiesapplies toColumnsmany tablesevaluated perActive roleof each userreturnsClear or maskedper row

Common mistakes

Mismatched types. A masking policy for STRING cannot be set on a NUMBER column, and it must return the same type.

Using a masking policy to hide a column from SELECT. Use a projection policy when the column must never be returned.

Assuming ACCOUNTADMIN sees clear values. The policy body controls that.

Expecting stored data to change. Masking works at query time on unchanged data.

What to remember for the exam

  • Masking policy: signature plus matching return type; one per column; evaluated at query time; Enterprise.
  • External tokenization: tokens stored, detokenized through an external function inside the policy.
  • Projection policy: PROJECTION_CONSTRAINT(ALLOW => ...); filter and join allowed, return blocked.
  • Use IS_ROLE_IN_SESSION for role hierarchy; separate policy administration from table ownership.

Check yourself

  • Which policy lets analysts filter on a column but never see its values?

    A projection policy with PROJECTION_CONSTRAINT(ALLOW => FALSE).

  • What is stored in a table that uses external tokenization?

    Tokens produced by an outside service, which the masking policy detokenizes for authorized roles.

  • Why use IS_ROLE_IN_SESSION instead of comparing CURRENT_ROLE()?

    IS_ROLE_IN_SESSION also matches roles that inherit the named role.

Topic 4 of 7 in Data Governance

Test yourself on Data Governance

The full Snowflake DEA-C02 question bank has 709 questions, with timed practice exams and an explanation for every answer.

This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.