Column-level security with masking policies in Snowflake - Mini Project: Build & Apply

Column-level security with masking policies

📖 Scenario: You work for a company that stores sensitive customer data in Snowflake. You want to protect the email column so that only authorized users can see the full email addresses. Others should see masked values.

🎯 Goal: Create a masking policy in Snowflake that hides the email column for unauthorized users by replacing it with a masked string.

📋 What You'll Learn

Create a table called customers with columns id, name, and email

Create a masking policy called email_masking_policy that masks the email column

Apply the masking policy to the email column in the customers table

Use a condition in the masking policy to show full email only to users with role FULL_ACCESS

💡 Why This Matters

🌍 Real World

Companies use masking policies to protect sensitive data like emails, phone numbers, or social security numbers in cloud data warehouses.

💼 Career

Understanding column-level security and masking policies is essential for data engineers and cloud security professionals working with Snowflake or similar platforms.

1

Create the customers table

Create a table called customers with columns id as integer, name as string, and email as string.

Snowflake

# Create the customers table with id, name, and email columns
# Your code here

Need a hint?

Use CREATE OR REPLACE TABLE customers (id INT, name STRING, email STRING);

2

Create the masking policy email_masking_policy

Create a masking policy called email_masking_policy that takes a string input email and returns the original email if the current role is FULL_ACCESS. Otherwise, return the string '***masked***'.

Snowflake

CREATE OR REPLACE TABLE customers (
  id INT,
  name STRING,
  email STRING
);

# Create the masking policy email_masking_policy
# Your code here

Need a hint?

Use CREATE OR REPLACE MASKING POLICY email_masking_policy AS (email STRING) RETURNS STRING -> CASE WHEN CURRENT_ROLE() = 'FULL_ACCESS' THEN email ELSE '***masked***' END;

3

Apply the masking policy to the email column

Alter the customers table to set the masking policy email_masking_policy on the email column.

Snowflake

CREATE OR REPLACE TABLE customers (
  id INT,
  name STRING,
  email STRING
);

CREATE OR REPLACE MASKING POLICY email_masking_policy AS (email STRING) RETURNS STRING ->
  CASE
    WHEN CURRENT_ROLE() = 'FULL_ACCESS' THEN email
    ELSE '***masked***'
  END;

# Apply the masking policy email_masking_policy to the email column
# Your code here

Need a hint?

Use ALTER TABLE customers ALTER COLUMN email SET MASKING POLICY email_masking_policy;

4

Verify the masking policy is applied

Write a query to select id, name, and email from the customers table to verify the masking policy is applied.

Snowflake

CREATE OR REPLACE TABLE customers (
  id INT,
  name STRING,
  email STRING
);

CREATE OR REPLACE MASKING POLICY email_masking_policy AS (email STRING) RETURNS STRING ->
  CASE
    WHEN CURRENT_ROLE() = 'FULL_ACCESS' THEN email
    ELSE '***masked***'
  END;

ALTER TABLE customers ALTER COLUMN email SET MASKING POLICY email_masking_policy;

# Write a SELECT query to get id, name, and email from customers
# Your code here

Need a hint?

Use SELECT id, name, email FROM customers; to verify the masking policy