Column-level security with masking policies in Snowflake - Step-by-Step Execution

Process Flow - Column-level security with masking policies

User Queries Data

↓

Check Masking Policy on Column

↓

Evaluate User Role

↓

Apply Masking Logic

↓

Return Masked or Original Data

↓

User Sees Data Based on Permissions

When a user queries a column with a masking policy, Snowflake checks the user's role, applies the masking logic, and returns either masked or original data accordingly.

Execution Sample

Snowflake

CREATE MASKING POLICY ssn_mask AS
  (val STRING) RETURNS STRING ->
  CASE
    WHEN CURRENT_ROLE() IN ('FULL_ACCESS_ROLE') THEN val
    ELSE 'XXX-XX-XXXX'
  END;

ALTER TABLE employees MODIFY COLUMN ssn SET MASKING POLICY ssn_mask;

This code creates a masking policy for the SSN column that shows the real value only to users with FULL_ACCESS_ROLE; others see a masked value.

Process Table

Step	User Role	Column Value	Masking Policy Applied	Returned Value
1	FULL_ACCESS_ROLE	123-45-6789	Check role in policy	123-45-6789
2	READ_ONLY_ROLE	123-45-6789	Check role in policy	XXX-XX-XXXX
3	ANALYST_ROLE	123-45-6789	Check role in policy	XXX-XX-XXXX
4	FULL_ACCESS_ROLE	987-65-4321	Check role in policy	987-65-4321
5	READ_ONLY_ROLE	987-65-4321	Check role in policy	XXX-XX-XXXX
6	ANALYST_ROLE	987-65-4321	Check role in policy	XXX-XX-XXXX
7	No role or unknown	123-45-6789	Check role in policy	XXX-XX-XXXX

💡 Execution stops after returning masked or original data based on user role.

Status Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	After Step 5	After Step 6	After Step 7
User Role	N/A	FULL_ACCESS_ROLE	READ_ONLY_ROLE	ANALYST_ROLE	FULL_ACCESS_ROLE	READ_ONLY_ROLE	ANALYST_ROLE	No role
Column Value	N/A	123-45-6789	123-45-6789	123-45-6789	987-65-4321	987-65-4321	987-65-4321	123-45-6789
Returned Value	N/A	123-45-6789	XXX-XX-XXXX	XXX-XX-XXXX	987-65-4321	XXX-XX-XXXX	XXX-XX-XXXX	XXX-XX-XXXX

Key Moments - 3 Insights

Why does the returned value change based on user role?

Because the masking policy checks the user's role (see execution_table rows 1 and 2). If the role is allowed, the real data is returned; otherwise, a masked value is returned.

What happens if a user has no role or an unknown role?

The masking policy treats unknown or missing roles as unauthorized and returns the masked value (see execution_table row 7).

Is the original data changed in the database when masking is applied?

No, the original data stays the same. Masking only changes what the user sees at query time (refer to variable_tracker 'Column Value' vs 'Returned Value').

Visual Quiz - 3 Questions

Look at the execution_table, what value does a user with FULL_ACCESS_ROLE see for the SSN '987-65-4321'?

A XXX-XX-XXXX

B NULL

C 987-65-4321

D Access Denied

💡 Hint

Check execution_table row 4 under 'Returned Value'.

At which step does the masking policy return a masked value for the SSN '123-45-6789'?

A Step 2

B Step 1

C Step 4

D Step 7

💡 Hint

Look at execution_table rows with 'READ_ONLY_ROLE' and 'Returned Value'.

If a new role 'HR_ROLE' is added to FULL_ACCESS_ROLE list, how would the returned value change for that role querying '123-45-6789'?

A It would return 'XXX-XX-XXXX'

B It would return '123-45-6789'

C It would return NULL

D It would cause an error

💡 Hint

Refer to the masking policy logic in execution_sample and how roles affect returned value.

Concept Snapshot

Column-level security with masking policies:
- Define a masking policy with CASE logic based on user roles.
- Attach the policy to sensitive columns.
- When queried, Snowflake checks user role.
- Returns original data if authorized, else masked data.
- Original data remains unchanged in storage.

Full Transcript

Column-level security with masking policies in Snowflake works by defining a masking policy that controls what data users see based on their roles. When a user queries a column with a masking policy, Snowflake evaluates the user's role. If the role is authorized, the user sees the original data. Otherwise, the data is masked, for example, replaced with 'XXX-XX-XXXX' for social security numbers. The original data in the database is never changed; masking only affects query results. This ensures sensitive data is protected while allowing authorized users full access.

Practice

1. What is the main purpose of a masking policy in Snowflake's column-level security?

easy

A. To create backups of sensitive columns

B. To hide or mask sensitive column data based on user roles

C. To encrypt the entire database automatically

D. To delete sensitive data permanently from a column

Solution

1. Step 1: Understand masking policy purpose

   Masking policies control how sensitive data is shown without changing the original data.

2. Step 2: Identify role-based data visibility

   They mask or reveal data depending on the user's role or permissions.

3. Final Answer:

   To hide or mask sensitive column data based on user roles -> Option B

4. Quick Check:

   Masking policy = role-based data masking [OK]

Hint: Masking policies control data visibility by user roles [OK]

Common Mistakes:

• Thinking masking deletes data
• Confusing masking with encryption
• Assuming masking backs up data

2. Which of the following is the correct syntax to create a masking policy in Snowflake?

easy

A. CREATE MASKING POLICY policy_name AS CASE WHEN CURRENT_ROLE() IN ('ANALYST') THEN val ELSE '****' END RETURNS STRING;

B. CREATE MASKING POLICY policy_name (val STRING) RETURNS STRING AS CASE WHEN CURRENT_ROLE() IN ('ANALYST') THEN val ELSE '****' END;

C. CREATE MASKING POLICY policy_name AS (val STRING) RETURNS STRING AS (CASE WHEN CURRENT_ROLE() IN ('ANALYST') THEN val ELSE '****' END);

D. CREATE MASKING POLICY policy_name RETURNS STRING AS (val STRING) CASE WHEN CURRENT_ROLE() IN ('ANALYST') THEN val ELSE '****' END;

Solution

1. Step 1: Recall correct syntax structure

   Snowflake masking policy syntax starts with CREATE MASKING POLICY name AS (input parameter) RETURNS type.

2. Step 2: Match syntax with options

   CREATE MASKING POLICY policy_name AS (val STRING) RETURNS STRING AS (CASE WHEN CURRENT_ROLE() IN ('ANALYST') THEN val ELSE '****' END); correctly uses AS (val STRING) RETURNS STRING followed by the CASE expression.

3. Final Answer:

   CREATE MASKING POLICY policy_name AS (val STRING) RETURNS STRING AS (CASE WHEN CURRENT_ROLE() IN ('ANALYST') THEN val ELSE '****' END); -> Option C

4. Quick Check:

   Correct syntax = CREATE MASKING POLICY policy_name AS (val STRING) RETURNS STRING AS (CASE WHEN CURRENT_ROLE() IN ('ANALYST') THEN val ELSE '****' END); [OK]

Hint: Syntax: CREATE MASKING POLICY name AS (param) RETURNS type [OK]

Common Mistakes:

• Placing RETURNS before AS
• Missing parentheses around input parameter
• Incorrect order of clauses

3. Given this masking policy:

CREATE MASKING POLICY mask_ssn AS (val STRING) RETURNS STRING AS (CASE WHEN CURRENT_ROLE() IN ('HR') THEN val ELSE 'XXX-XX-XXXX' END);

What will a user with role 'SALES' see when querying the masked SSN column?

medium

A. 'XXX-XX-XXXX'

B. NULL

C. The actual SSN value

D. An error message

Solution

1. Step 1: Analyze masking policy logic

   The policy shows the real value only if CURRENT_ROLE() is 'HR'. Otherwise, it masks with 'XXX-XX-XXXX'.

2. Step 2: Determine output for 'SALES' role

   Since 'SALES' is not 'HR', the user sees the masked string 'XXX-XX-XXXX'.

3. Final Answer:

   'XXX-XX-XXXX' -> Option A

4. Quick Check:

   Role not HR = masked value [OK]

Hint: If role not allowed, masking policy returns mask string [OK]

Common Mistakes:

• Assuming user sees actual data
• Expecting NULL instead of mask
• Thinking it causes an error

4. You created a masking policy but users still see unmasked data. What is the most likely cause?

medium

A. The masking policy was not attached to the column

B. The masking policy syntax has a typo

C. The user has the role that bypasses masking

D. The table is missing a primary key

Solution

1. Step 1: Check masking policy attachment

   Masking policies must be assigned to columns to take effect.

2. Step 2: Consider user roles and syntax

   While roles can bypass masking, if all users see unmasked data, likely the policy is not attached.

3. Final Answer:

   The masking policy was not attached to the column -> Option A

4. Quick Check:

   Policy attached = masking works [OK]

Hint: Attach masking policy to column to activate it [OK]

Common Mistakes:

• Ignoring policy attachment step
• Blaming user roles without checking
• Confusing table keys with masking

5. You want to create a masking policy that shows full email addresses to 'ADMIN' role, partially masks emails for 'SUPPORT' role (showing only domain), and masks completely for others. Which policy logic correctly implements this?

hard

A. CASE WHEN CURRENT_ROLE() = 'SUPPORT' THEN val WHEN CURRENT_ROLE() = 'ADMIN' THEN CONCAT('***@', SPLIT_PART(val, '@', 2)) ELSE '****' END

B. CASE WHEN CURRENT_ROLE() = 'ADMIN' THEN '****' WHEN CURRENT_ROLE() = 'SUPPORT' THEN val ELSE CONCAT('***@', SPLIT_PART(val, '@', 2)) END

C. CASE WHEN CURRENT_ROLE() IN ('ADMIN', 'SUPPORT') THEN val ELSE '****' END

D. CASE WHEN CURRENT_ROLE() = 'ADMIN' THEN val WHEN CURRENT_ROLE() = 'SUPPORT' THEN CONCAT('***@', SPLIT_PART(val, '@', 2)) ELSE '****' END

Solution

1. Step 1: Identify role conditions and outputs

   'ADMIN' sees full email (val), 'SUPPORT' sees masked local part but full domain, others see full mask.

2. Step 2: Match logic to requirements

   CASE WHEN CURRENT_ROLE() = 'ADMIN' THEN val WHEN CURRENT_ROLE() = 'SUPPORT' THEN CONCAT('***@', SPLIT_PART(val, '@', 2)) ELSE '****' END shows full val for ADMIN, partial mask for SUPPORT using CONCAT and SPLIT_PART, else full mask.

3. Final Answer:

   CASE WHEN CURRENT_ROLE() = 'ADMIN' THEN val WHEN CURRENT_ROLE() = 'SUPPORT' THEN CONCAT('***@', SPLIT_PART(val, '@', 2)) ELSE '****' END -> Option D

4. Quick Check:

   Role-based partial/full masking = CASE WHEN CURRENT_ROLE() = 'ADMIN' THEN val WHEN CURRENT_ROLE() = 'SUPPORT' THEN CONCAT('***@', SPLIT_PART(val, '@', 2)) ELSE '****' END [OK]

Hint: Order CASE by roles from most to less privileged [OK]

Common Mistakes:

• Swapping ADMIN and SUPPORT logic
• Showing full email to all roles
• Incorrect string functions usage