Snowflake Dynamic Data Masking Examples

By: Sergey Gigoyan   |   Updated: 2024-02-21   |   Comments   |   Related: More > Snowflake

Problem

In a previous article, we discussed dynamic data masking in SQL Server. This article will explore Snowflake's dynamic data masking feature in practice. Furthermore, we plan to draw parallels with the analog feature of SQL Server.

Solution

Dynamic Data Masking is a column-level security feature that enables the hiding of sensitive data by masking actual values in tables or views for users who are not authorized to access the information. As discussed in the previous article, it is implemented in SQL Server by applying masking functions to columns and granting the appropriate permissions to users. Let's see how it is designed in Snowflake.

In Snowflake, Dynamic Data Masking is applied by creating and assigning a masking policy to a column. Let's create a test environment and illustrate the sample dynamic data masking:

-- Sample Database
CREATE DATABASE IF NOT EXISTS TestDB;
 
USE DATABASE TestDB;
 
-- Sample table
CREATE TABLE Employee (
    ID INT AUTOINCREMENT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Email VARCHAR(50),
    Salary NUMBER(10, 2)
);
 
-- Inserting sample data
INSERT INTO Employee (FirstName, LastName, Email, Salary)
VALUES
    ('John', 'Doe', '[email protected]', 55000.00),
    ('Jane', 'Smith', '[email protected]', 65000.00),
    ('Michael', 'Johnson', '[email protected]', 48000.00),
    ('Aram', 'Melikyan', '[email protected]', 60000.00),
    ('Sos', 'Sargsyan', '[email protected]', 75000.00),
    ('Ann', 'Petrosyan', '[email protected]', 55000.00);

To make it simple, suppose we want only managers to see the employees' salaries and emails. The code below creates sample roles with appropriate permissions:

--Creating roles
CREATE ROLE MANAGERS;
CREATE ROLE DB_USERS;
 
--Granting permissions to roles
GRANT USAGE ON DATABASE TestDB TO MANAGERS;
GRANT USAGE ON SCHEMA TestDB.public TO MANAGERS;
GRANT SELECT ON ALL TABLES IN SCHEMA TestDB.public TO ROLE MANAGERS;
 
GRANT USAGE ON DATABASE TestDB TO DB_USERS;
GRANT USAGE ON SCHEMA TestDB.public TO DB_USERS;
GRANT SELECT ON ALL TABLES IN SCHEMA TestDB.public TO ROLE DB_USERS;
 
GRANT USAGE ON WAREHOUSE COMPUTE_WH TO ROLE MANAGERS;
GRANT USAGE ON WAREHOUSE COMPUTE_WH TO ROLE DB_USERS;
 
--Creating users
CREATE USER test_manager
PASSWORD = 'your_password'
DEFAULT_ROLE = MANAGERS;
 
CREATE USER test_user
PASSWORD = 'your_password'
DEFAULT_ROLE = DB_USERS;
 
--Granting roles to users
GRANT ROLE MANAGERS TO USER test_manager;
GRANT ROLE DB_USERS TO USER test_user;

We have created two roles ('MANAGERS' and 'DB_USERS') and two users ('test_manager' and 'test_user') with the corresponding roles. Let's assume only the users with the 'MANAGERS' role are allowed to see employees' salaries and emails. Therefore, we need to create masking policies to mask salaries and emails for all users except the users with the 'MANAGERS' role:

--Masking policy for emails
CREATE OR REPLACE MASKING POLICY masking_emails AS (val string) RETURNS string ->
  CASE
    WHEN CURRENT_ROLE() IN ('MANAGERS') THEN val
    ELSE '*********'
  END;
 
--Masking policy for salaries
CREATE OR REPLACE MASKING POLICY masking_salaries AS (val NUMBER(10, 2)) RETURNS number ->
  CASE
    WHEN CURRENT_ROLE() IN ('MANAGERS') THEN val
    ELSE 00000
  END;
  
--Setting masking policies
ALTER TABLE IF EXISTS EMPLOYEE MODIFY COLUMN email SET MASKING POLICY masking_emails;
ALTER TABLE IF EXISTS EMPLOYEE MODIFY COLUMN salary SET MASKING POLICY masking_salaries;

According to the code above, the first masking policy replaces the actual values of a column with '********', and the second one replaces the actual values in a column with 0000 for all roles except 'MANAGERS'. Then, the first policy is applied to the 'email' column of the 'TestTable', and the second policy is applied to the 'salary' column of the same table. Please note that for simplicity, we did all these actions under 'ACCOUNTADMIN'. However, it is recommended to have a special role for creating masking policies and special roles for applying masking policies to the corresponding tables or views. Additionally, it is worth mentioning that this is a simple example of defining masking policies. It is possible to define the logic of the masking policies in different ways, such as applying them to several roles or using different symbols or numbers for masking.

Now, after running the above code, let's query our table:

SELECT *
FROM EMPLOYEE

As we can see, masked data appears in the 'EMAIL' and 'SALARY' columns, even though we ran the code under 'ACCOUNTADMIN'. We will see the same result if we log in using test_user user. If we log in using a user that has the 'MANAGERS' role (test_manager, in our case), the result will be different:

So, we can see that if we query the same table using the 'MANAGERS' role, the actual values of both 'EMAIL' and 'SALARY' are available.

Unlike SQL Server, where special masking functions are used to mask a column's data, in Snowflake, we define masking policies and then apply these policies to columns. We manage permissions on sensitive data through masking policies in Snowflake, while in SQL Server, we achieve this by granting special permissions to users.

To clean up the environment after these tests, you can use the following code to drop the created users, roles, policies, etc.:

------Cleanup
--Dropping users
DROP USER test_manager;
DROP USER test_user;
 
--Dropping roles
DROP ROLE MANAGERS;
DROP ROLE DB_USERS;
 
--Drop masking policy
ALTER TABLE IF EXISTS EMPLOYEE MODIFY COLUMN email UNSET MASKING POLICY;
ALTER TABLE IF EXISTS EMPLOYEE MODIFY COLUMN salary UNSET MASKING POLICY;
 
DROP MASKING POLICY masking_emails;
DROP MASKING POLICY masking_salaries;

Conclusion

We saw how Dynamic Data Masking works in Snowflake. Unlike SQL Server, which uses built-in functions to mask columns' data and permissions to manage sensitive data access, Snowflake uses masking policies to grant privileges to the corresponding roles to access sensitive data.

Next Steps

For additional information, please see the links below:

• Understanding Dynamic Data Masking | Snowflake Documentation
• Using Dynamic Data Masking | Snowflake Documentation
• Understanding Column-level Security | Snowflake Documentation
• Dynamic data masking - SQL Server | Microsoft Learn

About the author

Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips