Week 106 – Intermediate

Challenge: Implementing Dynamic Data Masking in Snowflake

This week’s focus centers around leveraging Snowflake’s Dynamic Data Masking feature to protect sensitive information based on user roles. Imagine you are a data engineer at a company where data privacy and role-based access control are crucial for compliance and security. Your mission is to implement dynamic data masking to ensure that sensitive data is properly masked based on the user’s role while allowing necessary data access for different business functions.

Story Scenario:

Your company, SecureDataCorp, recently underwent a security audit that highlighted the need for better data masking practices to protect sensitive customer information. The audit revealed that sensitive data such as credit card numbers, email addresses, and account balances were accessible to users without appropriate clearance. Your task is to implement Snowflake’s Dynamic Data Masking to ensure that only authorized users can view sensitive information while others see masked data.

As you engage with this challenge, consider the following tasks:

Detailed Tasks:

Initial Setup:

Task: Create a sample database and a table named customer_data containing columns for customer ID, name, email, phone, address, credit card number, and account balance. Populate this table with sample data.

CREATE DATABASE dynamic_data_masking_db;
USE DATABASE dynamic_data_masking_db;

CREATE TABLE customer_data (
    customer_id INTEGER,
    name STRING,
    email STRING,
    phone STRING,
    address STRING,
    credit_card_number STRING,
    account_balance FLOAT
);

INSERT INTO customer_data (customer_id, name, email, phone, address, credit_card_number, account_balance) VALUES
(1, 'John Doe', 'john.doe@example.com', '123-456-7890', '123 Main St', '4111111111111111', 15000.00),
(2, 'Jane Smith', 'jane.smith@example.com', '234-567-8901', '456 Elm St', '4222222222222222', 8500.00),
(3, 'Alice Johnson', 'alice.johnson@example.com', '345-678-9012', '789 Oak St', '4333333333333333', 3000.00),
(4, 'Bob Brown', 'bob.brown@example.com', '456-789-0123', '101 Pine St', '4444444444444444', 500.00),
(5, 'Charlie Davis', 'charlie.davis@example.com', '567-890-1234', '202 Maple St', '4555555555555555', 12000.00),
(6, 'Diana Evans', 'diana.evans@example.com', '678-901-2345', '303 Cedar St', '4666666666666666', 2000.00),
(7, 'Frank Green', 'frank.green@example.com', '789-012-3456', '404 Birch St', '4777777777777777', 30000.00),
(8, 'Hannah White', 'hannah.white@example.com', '890-123-4567', '505 Willow St', '4888888888888888', 4500.00),
(9, 'Ian Black', 'ian.black@example.com', '901-234-5678', '606 Aspen St', '4999999999999999', 7500.00),
(10, 'Jill Blue', 'jill.blue@example.com', '012-345-6789', '707 Cherry St', '4000000000000000', 500.00);

Define Roles and Users:

Task: Create three roles: admin, manager, and analyst. Assign them to these roles to simulate different levels of data access within the organization (You don’t have to create new users as long as you don’t have secondary_roles enabled).

Implement Masking Policies:

Task: Implement masking policies for the credit_card_number, email, and account_balance columns with the following logic:

• For analyst role: Fully mask the credit card number, mask the domain part of the email, and mask the account balance.
• For manager role: Partially mask the credit card number (showing only the last 4 digits), mask the domain part of the email, and partially mask the account balance by rounding it.
• For admin role: No masking is applied; the admin should see the full data.

Apply and Test Masking Policies:

Task: Apply the masking policies to the customer_data table. Test the implementation by querying the table as each role (admin, manager, analyst) and verify that the masking policies are applied correctly.

Steps to Complete the Challenge:

1. Initial Setup:
   • Use the provided start-up code to create the customer_data table and insert sample data.
2. Define Roles and Users:
   • Create roles: admin, manager, analyst.
   • Create users (optional) and assign them to respective roles.
3. Implement Masking Policies:
   • Create and apply masking policies for the credit_card_number, email, and account_balance columns as per the specified logic.
4. Apply and Test Masking Policies:
   • Verify that the masking policies are correctly applied by querying the customer_data table as different users with admin, manager, and analyst roles.

By the end of this challenge, you will have implemented role-based dynamic data masking in Snowflake, ensuring that sensitive data is protected and only visible to users with appropriate access.