Course contents

SnowPro Core COF-C03 Study GuidePart 3: Access control and securityColumn-Level Security

Column-Level Security

Column-Level Security

In short

A masking policy returns a different value from the same stored column depending on who asks. The data on disk never changes.

The problem

Safia's team needs real employee email addresses. Everyone else should query the same table and see masked ones. Copying the table into a "masked" and an "unmasked" version would double storage and drift out of sync.

The mental model

Column-level security is a filter on values, applied at read time. Snowflake defines it as exactly two features: Dynamic Data Masking and External Tokenization. Both work through masking policies, and both need Enterprise edition or higher. Snowflake's editions, from smallest, are Standard, Enterprise, Business Critical and Virtual Private Snowflake.

How it works

A masking policy is a schema-level object that looks like a small SQL function. It receives the column value (and optionally other columns from the same row) and returns what the query should see. It runs at query time, every time the column is read, through any access path. Figure 3.14 follows one value to two readers.

One stored value, two results. EMPLOYEES.EMAIL stores karim.haddad@ismafrost.com once. The masking policy EMAIL_MASK runs at query time for each reader: if HR_ADMIN is in the session it returns the real value, otherwise it hides the part before the @. Safia, with HR_ADMIN, sees the real address. Thomas, whose DATA_ENGINEER role owns the table, sees *****@ismafrost.com.COLUMNEMPLOYEES.EMAILstored once:karim.haddad@ismafrost.comPOLICYEMAIL_MASKif HR_ADMIN is in thesession: real valueotherwise: hide thepart before @HR_ADMIN · SEESSafiakarim.haddad@ismafrost.comDATA_ENGINEER · SEESThomastable owner, sees*****@ismafrost.comOwning the table gives no exemption. Only the policy body decides.read atquery timereal valuemasked

COLUMNEMPLOYEES.EMAILstored once:karim.haddad@ismafrost.com

read at query time

POLICYEMAIL_MASKif HR_ADMIN is in the session: real valueotherwise: hide the part before @

real value

HR_ADMIN · SEESSafiakarim.haddad@ismafrost.com

masked

DATA_ENGINEER · SEESThomastable owner, sees*****@ismafrost.com

Owning the table gives no exemption. Only the policy body decides.

Figure 3.14 One stored value, two results. The policy runs at query time for each reader. Even the table's owner sees the masked value, because only the policy body grants exemptions.

Worked example: masking emails

Setup, from the grant sheet: Safia's HR_ADMIN has SELECT on HR_DB.CORE.EMPLOYEES, and Thomas's pipeline created the table, so DATA_ENGINEER owns it.

CREATE MASKING POLICY governance_db.policies.email_mask
  AS (val STRING) RETURNS STRING ->
  CASE
    WHEN IS_ROLE_IN_SESSION('HR_ADMIN') THEN val
    ELSE REGEXP_REPLACE(val, '.+@', '*****@')   -- keep the domain for support work
  END;

ALTER TABLE hr_db.core.employees MODIFY COLUMN email
  SET MASKING POLICY governance_db.policies.email_mask;

Expected result. Safia, with HR_ADMIN in her session, sees karim.haddad@ismafrost.com. Thomas, querying as the table's owner, sees *****@ismafrost.com. Same query, same table, one stored value.

Which role test to use? CURRENT_ROLE() checks only the primary role. IS_ROLE_IN_SESSION() checks the primary role, the active secondary roles and every role they inherit. Use IS_ROLE_IN_SESSION() by default: it matches what the session can actually do, so the policy agrees with the privilege check. Then decide who may inherit a sensitive role. With IS_ROLE_IN_SESSION('HR_ADMIN'), any role above HR_ADMIN would see real values, which is exactly why HR_ADMIN stays outside the tree. Use CURRENT_ROLE() only when the result should change with USE ROLE, for example to make an admin switch roles on purpose before seeing clear values.

Limits and traps

  • The policy's input and return types must match the column's type.
  • A column carries one masking policy at a time. One policy can protect many columns.
  • No role is exempt by default, including ACCOUNTADMIN and the table owner. Only the policy body grants exemptions.
  • The policy applies through views, shares, CREATE TABLE … AS SELECT (CTAS) and unloads to files with COPY INTO a stage. There's no side path to the raw value.
  • Conditional masking passes extra columns to the policy as more arguments; that argument list is the policy's signature. The decision can then depend on another column in the row, for example "show the email only where consent_flag = TRUE." It is still column-level security.
  • Tag-based masking attaches a policy to a tag, so every column carrying the tag is masked. That's how masking scales to hundreds of columns. It is a way of delivering masking, not a third column-level security feature.

Which one to use: masking or tokenization?

Dynamic Data MaskingExternal Tokenization
Stored in SnowflakeThe real valueA token. The real value is replaced before loading, by a third-party tokenization provider
At query timeThe policy hides the value from unauthorized rolesThe policy calls an external function, code that runs at the tokenization provider, to detokenize for authorized roles
Use it whenThe clear value may be stored in SnowflakeRegulation forbids storing the clear value in Snowflake

A scenario asking for protection "without altering stored data" points to masking.

Exam takeaway
  • Column-level security = Dynamic Data Masking + External Tokenization, Enterprise and above.
  • Masking rewrites at query time and never changes stored data. Tokenization changes stored data.
  • Conditional masking depends on another column. Tag-based masking attaches policies through tags.
  • Nobody is exempt unless the policy body says so.