Column-Level Security
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.
COLUMNEMPLOYEES.EMAILstored once:karim.haddad@
read at query time
POLICYEMAIL_MASKif HR_ADMIN is in the session: real valueotherwise: hide the part before @
real value
HR_ADMIN · SEESSafiakarim.haddad@
masked
DATA_
Owning the table gives no exemption. Only the policy body decides.
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 ->CASEWHEN 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 Masking | External Tokenization | |
|---|---|---|
| Stored in Snowflake | The real value | A token. The real value is replaced before loading, by a third-party tokenization provider |
| At query time | The policy hides the value from unauthorized roles | The policy calls an external function, code that runs at the tokenization provider, to detokenize for authorized roles |
| Use it when | The clear value may be stored in Snowflake | Regulation forbids storing the clear value in Snowflake |
A scenario asking for protection "without altering stored data" points to masking.
- 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.

