Dynamically Masking Data and PII in Snowflake Database

As organizations process increasing volumes of data, protecting Personal Identifiable Information (PII) has become paramount. Snowflake Database provides sophisticated data masking features to ensure privacy. This article explains how to implement Snowflake Database's data masking policies and manage them efficiently using a metadata table.

Step 1: Understanding Data Masking

Data masking is a technique that conceals the original data with modified content. Snowflake Database provides real-time data masking, ensuring the original data remains unaltered while being masked at the query level.

Here's an example of a Snowflake masking policy:

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

To create a masking policy, a user must possess the "CREATE MASKING POLICY" privilege on the schema where the masking policy will be created. Typically, this privilege is held by security administrators or higher role levels.

It's also essential to note that the user must have sufficient privileges to execute the ALTER TABLE statement when applying a masking policy to a column in a table. Again, this is usually a privilege granted to roles such as a security administrator or a system administrator.

Always consult your Snowflake Database Administrator or refer to Snowflake’s official documentation for accurate, up-to-date information about roles and privileges. Please remember, granting and revoking privileges should be handled carefully to maintain data security.

Step 2: Storing Masking Policies in a Metadata Table

For better manageability, particularly when dealing with numerous tables and masking policies, you can utilize a metadata table. This table can contain the mapping of each table-column to its respective masking policy.

First, create a metadata table:

CREATE TABLE metadata_table (
  table_name STRING,
  column_name STRING,
  masking_policy STRING
);

Then, insert the table-column to masking policy mappings:

INSERT INTO metadata_table (table_name, column_name, masking_policy) VALUES ('customer', 'ssn', 'pii_mask');

Step 3: Apply the Masking Policy to a Column

Extract the masking policy from the metadata table and apply it to the respective columns containing PII data.

DECLARE
  @masking_policy STRING;
BEGIN
  SELECT masking_policy INTO @masking_policy
  FROM metadata_table
  WHERE table_name='customer' AND column_name='ssn';

  EXECUTE IMMEDIATE 'ALTER TABLE customer ADD MASKING POLICY ' || @masking_policy || ' FOR (ssn)';
END;

Step 4: Test the Masking Policy

To verify whether the policy is working as expected, execute a SELECT statement to retrieve data. If you have the necessary permissions (are in the `FULL_ACCESS_ROLE`), you should see the actual data; otherwise, you will see the masked data.

SELECT ssn FROM customer;
-- ssn will show as '*****' if user does not have access

Step 5: Grant and Revoke Access

Snowflake enables you to grant and revoke roles for specific users. To allow a user to see unmasked data, you need to grant them the `FULL_ACCESS_ROLE`.

GRANT ROLE FULL_ACCESS_ROLE TO USER your_username;

To revoke this access:

REVOKE ROLE FULL_ACCESS_ROLE FROM USER your_username;

Step 6: Dynamically Assigning Masking Policies

In scenarios where new tables get added to the database frequently, and column names match those present in the metadata table, it's essential to dynamically apply the masking policies.

Here is an example of how to accomplish this. This script will look at each row in the metadata table, identify new tables in the database that have the same column names, and apply the masking policy.

If you prefer SQL...

-- Declare the variables to hold the table, column, and masking policy
DECLARE
  @table_name STRING,
  @column_name STRING,
  @masking_policy STRING;

-- Define a cursor to go over each row of the metadata table
DECLARE metadata_cursor CURSOR FOR
  SELECT table_name, column_name, masking_policy
  FROM metadata_table;

-- Open the cursor

OPEN metadata_cursor;

-- Loop over each row of the metadata table
LOOP
  FETCH NEXT FROM metadata_cursor
  INTO @table_name, @column_name, @masking_policy;

  -- If no more rows, exit the loop
  IF NOTFOUND THEN
    LEAVE;
  END IF;

  -- Dynamically generate the SQL command to apply the masking policy
  SET @sql_command = 'ALTER TABLE ' || @table_name || ' ADD MASKING POLICY ' || @masking_policy || ' FOR (' || @column_name || ')';

  -- Apply the masking policy
  EXECUTE IMMEDIATE @sql_command;
END LOOP;
-- Close the cursor
CLOSE metadata_cursor;

If you prefer python...

pip install snowflake-connector-python

# Import the required modules
import snowflake.connector

# Establish a connection to Snowflake
con = snowflake.connector.connect(
    user='USERNAME',


    password='PASSWORD',
    account='ACCOUNT_URL',
    warehouse='WAREHOUSE',
    database='DATABASE',
    schema='SCHEMA'
)

# Create a cursor object
cur = con.cursor()

# Query to fetch all rows from the metadata table
metadata_query = "SELECT table_name, column_name, masking_policy FROM metadata_table;"
cur.execute(metadata_query)

# Fetch all rows from the metadata table
metadata_rows = cur.fetchall()

# Iterate over each row of the metadata table
for row in metadata_rows:
    table_name, column_name, masking_policy = row

    # Dynamically generate the SQL command to apply the masking policy
    alter_table_command = f"ALTER TABLE {table_name} ADD MASKING POLICY {masking_policy} FOR ({column_name});"

    # Execute the command to apply the masking policy
    cur.execute(alter_table_command)

# Close the cursor and the connection
cur.close()
con.close()

Conclusion:

Snowflake Database's data masking policies provide a robust mechanism for ensuring data privacy and regulatory compliance for PII. By effectively leveraging metadata tables, organizations can dynamically manage these policies in a structured manner. Remember, data masking should form part of a larger data governance and security strategy. Always consult the official Snowflake documentation or your database administrator for accurate information.

**Snowflake’s capabilities can vary based on your account edition and user privileges.