Column-level security with masking policies in Snowflake - Commands & Configuration

Introduction

Sometimes sensitive data in a database column should be hidden or changed for certain users. Masking policies let you show a safe version of that data instead of the real values, protecting privacy and security.

When you want to hide credit card numbers except for authorized users

When you need to show only partial email addresses to some users

When you want to mask salary information for most employees but show full data to HR

When you want to protect personal identification numbers in reports

When you want to comply with data privacy rules by limiting sensitive data exposure

Config File - masking_policy.sql

masking_policy.sql

CREATE OR REPLACE MASKING POLICY mask_sensitive_data AS
  (val STRING) RETURNS STRING ->
  CASE
    WHEN CURRENT_ROLE() IN ('FULL_ACCESS_ROLE') THEN val
    ELSE '****MASKED****'
  END;

CREATE OR REPLACE TABLE employees (
  id INT,
  name STRING,
  email STRING,
  salary NUMBER
);

ALTER TABLE employees ALTER COLUMN email SET MASKING POLICY mask_sensitive_data;
ALTER TABLE employees ALTER COLUMN salary SET MASKING POLICY mask_sensitive_data;

This file creates a masking policy named mask_sensitive_data that shows the real value only if the user has the FULL_ACCESS_ROLE. Otherwise, it shows ****MASKED****.

It then creates an employees table with columns including email and salary.

Finally, it applies the masking policy to the email and salary columns to protect sensitive data.

Commands

This command creates the masking policy that controls who can see the real data and who sees masked data.

Terminal

snowsql -q "CREATE OR REPLACE MASKING POLICY mask_sensitive_data AS (val STRING) RETURNS STRING -> CASE WHEN CURRENT_ROLE() IN ('FULL_ACCESS_ROLE') THEN val ELSE '****MASKED****' END;"

Expected Output

Done.

This command creates a sample employees table with sensitive columns to protect.

Terminal

snowsql -q "CREATE OR REPLACE TABLE employees (id INT, name STRING, email STRING, salary NUMBER);"

Expected Output

Done.

This command applies the masking policy to the email column to protect email addresses.

Terminal

snowsql -q "ALTER TABLE employees ALTER COLUMN email SET MASKING POLICY mask_sensitive_data;"

Expected Output

Done.

This command applies the masking policy to the salary column to protect salary information.

Terminal

snowsql -q "ALTER TABLE employees ALTER COLUMN salary SET MASKING POLICY mask_sensitive_data;"

Expected Output

Done.

This command shows the data in the employees table. Users without FULL_ACCESS_ROLE will see masked values for email and salary.

Terminal

snowsql -q "SELECT id, name, email, salary FROM employees;"

Expected Output

ID | NAME  | EMAIL          | SALARY

1  | Alice | ****MASKED**** | ****MASKED****

2  | Bob   | ****MASKED**** | ****MASKED****

Key Concept

If you remember nothing else from this pattern, remember: masking policies let you safely hide or change sensitive column data based on user roles.

Common Mistakes

Not assigning the masking policy to the specific columns after creating it

The masking policy won't protect any data unless applied to columns, so sensitive data remains visible.

Always run ALTER TABLE commands to set the masking policy on each sensitive column.

Using roles in the masking policy that users do not have

Users will never meet the condition to see real data, so they always see masked data, even if they should have access.

Verify user roles and include the correct role names in the masking policy condition.

Expecting masking policies to encrypt data permanently

Masking policies only change what users see; the real data is still stored in the database.

Use masking policies for display control, not for data encryption or permanent data hiding.

Summary

Create a masking policy that defines who can see real data and who sees masked data.

Apply the masking policy to sensitive columns using ALTER TABLE commands.

Querying the table shows masked or real data depending on the user's role.

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