DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workkloads.

Secure your stack and shape the future! Help dev teams across the globe navigate their software supply chain security challenges.

Releasing software shouldn't be stressful or risky. Learn how to leverage progressive delivery techniques to ensure safer deployments.

Avoid machine learning mistakes and boost model performance! Discover key ML patterns, anti-patterns, data strategies, and more.

Related

  • Data Privacy and Security: A Developer's Guide to Handling Sensitive Data With DuckDB
  • Using AUTHID Parameter in Oracle PL/SQL
  • Microsoft Azure Backup Service
  • How Trustworthy Is Big Data?

Trending

  • Testing SingleStore's MCP Server
  • Unlocking the Potential of Apache Iceberg: A Comprehensive Analysis
  • Beyond ChatGPT, AI Reasoning 2.0: Engineering AI Models With Human-Like Reasoning
  • Measuring the Impact of AI on Software Engineering Productivity
  1. DZone
  2. Data Engineering
  3. Databases
  4. Snowflake Data Encryption and Data Masking Policies

Snowflake Data Encryption and Data Masking Policies

This post will describe data encryption and data masking functionalities.

By 
Istvan Szegedi user avatar
Istvan Szegedi
·
Dec. 04, 20 · Tutorial
Likes (2)
Comment
Save
Tweet
Share
10.5K Views

Join the DZone community and get the full member experience.

Join For Free

Introduction

Snowflake architecture has been built with security in mind from the very beginning. A wide range of security features are made available from data encryption to advanced key management to versatile security policies to role-based data access and many more, at no additional cost. This post will describe data encryption and data masking functionalities.

Snowflake Security Reference Architecture

Snowflake Security Reference Architecture includes various state-of-the-art security techniques that offer multiple outstanding cloud security capabilities. It includes data encryption while data at rest, secure data transfers while data in transit, role-based table access, column and row-level access to a particular table, network access/IP range filtering, multi-factor authentication, Federated Single Single On, etc.

Figure 1: Snowflake Security Reference Architecture


In this article, we are going to illustrate the data encryption and data masking solutions available in Snowflake.

Data encryption and data masking are two distinct functionalities; data encryption is essentially a cryptography process to transform the data into unreadable cypher text, whereas data masking is meant to anonymize the data and prevent sensitive personally identifiable information (PII) to be accessible and identifiable for unwanted users. As such, data masking does not involve encryption.

Data Encryption in Snowflake

Snowflake stores all data automatically encrypted using AES-256 strong encryption. In addition, all files stored in stages for data loading and unloading automatically encrypted (using either AES 128 standard or 256 strong encryption). 

For data loading, there are two types of supported staging areas:

1. Snowflake-provided staging area

Figure 2: Snowflake-provided staging area


In case of Snowflake-provided staging area (a.k.a. internal Snowflake staging)  the uploaded files are automatically encrypted with 128-bit or 256-bit keys, depending on the CLIENT_ENCRYPTION_KEY_SIZE account parameter.  See Snowflake documentation for reference and more details about PUT command to upload files from local computer to an internal staging area.


2. Customer-provided staging area


Figure 3: Customer-provided staging area


With a customer-provided staging area, encryption is optional but highly recommended. Nevertheless, if the customer does not encrypt the data in the staging area, Snowflake will still immediately encrypt the data when it is loaded into a table.  The customer can choose her preference to encrypt staged files, for instance, in the AWS environment, it is fairly common to use AWS S3 client-side encryption.

For the customer-provided staging area, the customer must provide a master key in the CREATE SIZE command that can be used by Snowflake to process the uploaded files.

SQL
 




x


 
1
create stage encrypted_staging_are
2
url='s3://mybucket/datafiles/'
3
credentials=(aws _key_id='ABCDEFGHIJKL' aws_secret_key='xxxxxxxxxxxxxxxxxxxxxxxxx')
4
encryption=(master_key='asiIOIsaskjh%$asjlk+gsTHhs8197=');



On top of the encryption capability, Snowflake provides encryption key rotation and automatic re-keying of the files. Keys are automatically rotated on a regular basis, the entire process - data encryption and key management - is transparent and no configuration is needed.

Figure-4: Snowflake Key Rotation Process


For Enterprise Edition Snowflake supports automatic rekeying. When this feature is enabled, and the retired encryption key for a table is older than one year, Snowflake will automatically re-encrypt the data files using the newly generated key.

Data Masking - Column Level Security

This year Snowflake announced support for column-level security/data masking policy. Column-level security allows to protect sensitive data from unauthorized access whereas the authorized users can still access sensitive data at runtime. This is a dynamic feature, i.e. the fields are not masked in the table in a static way as the records are stored, instead, Snowflake policy conditions will determine on the fly whether users will see masked or obfuscated data when they execute their query. As a result, authorized users can see the content of a particular data field (e.g. email or social security number), while unauthorized users will have no visibility of these fields.

Figure 5: Column level security


The database  administrator can easily define the data masking policy using SQL statements from Snowflake Worksheet (or from any supported client, like SnowSQL):

SQL
 




xxxxxxxxxx
1


 
1
create masking policy employee_ssn_data_mask as (val string) returns string ->
2
  case
3
    when current_role() in ('HR') then val
4
    else '********'
5
  end;



Once the masking policy is defined, you can apply it to a particular table or view using the following SQL statements:

SQL
 




x


 
1
-- table
2
alter table if exists employee modify column ssn_number set masking policy employee_ssn_data_mask;
3

          
4
-- view
5
alter view employee_view modify column ssn_number set masking employee_ssn_data_mask_view;



The data masking policy can be applied to internal and external tables as well as to views and also to COPY INTO command while unloading tables into the staging area. Thus, unauthorized users see masked data after executing the COPY INTO command.

Data Masking Policy for Row Level Security

During the latest Data Cloud Summit in November 2020, Snowflake announced support for row-level security policy as well. This is not publicly available yet but will come soon. The functionality assumes that the database administrator defines a lookup table to determine which role can see what rows within a table and then enforce it using row-level masking policy:

SQL
 




x


 
1
-- Create a SalesRegion lookup table for geographical regions
2
-- Then create a row access policy 
3
create or replace row access policy row_level_access_policy as (sales_region varchar) returns boolean ->
4
'sales_executive_role' = current_role()
5
or exists (
6
   select 1 from SalesRegion 
7
   where sales_role = current_role()
8
   and region = sales_region
9
  )
10
;
11

          
12
-- Assign row level access policy to a table
13
alter table sales add row access policy row_level_access_policy on(region);



I cannot wait to start testing this new row-level access functionality.

Data masking Database sql security Staging (data)

Opinions expressed by DZone contributors are their own.

Related

  • Data Privacy and Security: A Developer's Guide to Handling Sensitive Data With DuckDB
  • Using AUTHID Parameter in Oracle PL/SQL
  • Microsoft Azure Backup Service
  • How Trustworthy Is Big Data?

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!