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.