Purpose of the article: The purpose of the article is to demonstrate how to secure sensitive data in Snowflake by applying data masking techniques to hide PII and using row-level security to limit access to authorized users, ensuring both data privacy and regulatory compliance.
Intended Audience: Snowflake
Tools and Technology: Snowflake
Keywords: Snowflake, Database, Table, Schema, Data Masking
Introduction:
Data masking is a method to protect data that is recommended by top industry experts. It hides sensitive information by replacing it with data that looks similar. This masked data can be used in place of the original sensitive data.
High Level Architecture:
Architecture Components:
- Data Sources
- Snowflake Data Warehouse
- Masking Policies
- Roles and Access Controls
- Data Consumers
- Data Sources
Ingest the data into multiple sources into Snowflake Data Warehouse which includes internal and external databases and Third-party applications.
- Snowflake Data Warehouse
It provides a scalable and secure environment for managing large amounts of data serving as the place where all data is to be stored.
- Masking Policies
In Snowflake, masking policies define how to protect Personally Identifiable Information (PII) by transforming data to conceal sensitive details.
- Roles and Access Controls
Snowflake uses roles and access controls to decide who can view masked and unmasked data.
- Data Consumers
It includes internal people like developers and analysts, as well as external ones like partners and clients, who will see either the masked or unmasked data based on their role.
Create and Populate the Table:
Let us start by creating a table and populating it with some sample data.
Use a CREATE statement to create a table with the respective columns.
Insert Sample Data:
Populate the respective table with records.
Choose the Right Data Masking Tools:
This guide will demonstrate how to use Snowflake’s built-in capabilities for dynamic data masking, providing an overview of Snowflake’s Dynamic Data Masking.
Define Masking Policies:
Snowflake uses masking policies to dynamically mask sensitive data based on the roles accessing the data, defining how the data should be masked through these policies.
Create Masking Policies:
A masking policy will be created using the create statement, as demonstrated below.
The masked data will be able to be seen only using dedicated role for masked data.
Apply Masking Policies to Columns:
The masking policy is assigned to the column via alter command.
Check Data as an Authorized User:
We are masking the information in the required column because we are assuming it contains PII data, thus preventing us from seeing the complete information.
Conclusion:
Data masking is crucial for keeping sensitive information safe in databases. It provides a way to secure data while still allowing it to be used effectively.
Row-Level Security
Row-Level Security restricts access to specific rows in database table, ensure that only specific users can only view the data.
Prerequisite:
use role SYSADMIN
create or replace database <database_name>
create or replace schema <schema_name>
Create and Populate the Table:
First, let us create a table and fill it with some sample data.
Create a table with the necessary columns using a CREATE statement.
Fill with Sample Data:
Insert records into the table as follows We have a column called country and we need to separate the records based on country.
Create Role Mapping Table:
Create another table named ‘role mapping’ which will be structured on country and role name. It will be needed to assign appropriate roles to each country in this table.
Populate with Sample Data:
Insert records into role mapping table as shown below.
Create Row Access Policy:
Configure a Row Access Policy to retrieve details by role.
Implement the row access policy:
We will apply the row access policy to the relevant column using the ALTER
command:
Syntax:
Alter table analytics.hr.employees
add row access policy analytics.hr.country_role_policy on (country)
Build a custom role:
Create custom roles and establish their hierarchy according to the roles:
Roles:
Using SECURITYADMIN role, we need to create below roles.
DATA_ANALYST_ROLE_US
DATA_ANALYST_ROLE_UK
DATA_ANALYST_ROLE_CA
DATA_ANALYST_ROLE_IND
Assign table privileges to custom roles:
Apply the Grant command to allow usage permissions for all tables to the respective database and role.
Allocate warehouse access to custom roles:
The final output result is as follows:
We are going to use the role called DATA_ANALYST_ROLE_US , so we will need to find the records who are going to aligned to US country.
We will use the role DATA_ANALYST_ROLE_IND to find the records that are aligned with the IND country.
Conclusion:
Row-Level Security enhances data security by dynamically filtering rows based on user roles or attributes, ensuring users only access the data they are authorized to see this approach helps maintain data privacy and compliance while simplifying access management in complex environments.
Author Bio:
Roopsai Kumar Reddy Kothapalli
Analyst - Advanced Analytics
I’m Roopsai Kumar Reddy, an Analyst in the Data Engineering Analytics Team at MOURI Tech, where I’ve worked for the past 3.9 years. My main tool is Snowflake, and I’ve written a blog to share my experiences and insights on combining the strengths of SQL and Snowflake for efficient data engineering.
