KPI Partners - Blogs

Data Security in Snowflake

Written by KPI Partners News Team | Aug 9, 2021 5:00:00 AM

 

by Ramana Kumar Gunti

Data Security in Snowflake

Data security is probably the number one topic on everyone's head when it comes to moving your data into the cloud. This is especially true if you are new to the cloud and don't have a seasoned technical team that understands cloud security. In this article, we will talk about how does Snowflake help? So let's get started.

While the world is moving towards Cloud Computing, Cloud Storage, and other Cloud Technologies which run on Public networks, it is important to secure and safeguard the data.

To achieve data security, Snowflake provides a handful of methodologies.

  • Data Encryption
  • RLS Row Level Security
  • CLS Column Level Security- Data Masking

Data Encryption:

  • Snowflake Supports End to End Data Encryption with which only the user who has authorization on data can see it. Snowflake encrypts the data automatically when customer stage the files into Snowflake Internal Stage.
  • Snowflake Supports AES 256 bit Encryption with a hierarchical key model. Snowflake's Hierarchical Key model consists of below four-level keys:
    1. Root Key
    2. Account Master Keys
    3. Table Master Keys
    4. File Keys
  • Web UI, command-line clients, and drivers communicate solely over HTTPS
  • Connections encrypted using TLS 1.2 from the client through to Snowflake Service
  • Data encrypted at rest

Account and Table Master Keys are automatically rotated when they are 30 days old

Periodic Rekeying: when Periodic Rekeying is enabled, Snowflake automatically creates a new encryption key and re-encrypts all the data using a new key when the existing key is one year old.

ALTER ACCOUNT SET PERIODIC_DATA_REKEYING = TRUE;

Row Level Security (RLS):

Snowflake supports Row Level Security by using Views or Secure Views. We can also manage the data Security with Access Privileged roles assigned to users on the database objects.

Views/Secure Views are the best way to share/hide the data to a user. We can hide, filter the data by using SQL statements used in the View definition.

 

However, if we use standard or materialized Views, we can still expose the view definition used to create that view to a user. With normal Views, users can still be able to understand the base tables used, filters used to filter the data, and other methodologies.

CREATE OR REPLACE VIEW SALES_DEPT AS SELECT EMP.* FROM EMPLOYEE WHERE DEPT_NAME = 'SALES';

Secure Views are the alternative to Normal Views which gives us the ability to hide the SQL Statement used to create a View. We can create a Secure View with the help of SECURE keyword before the view name. Secure Views allows to see and modify the View definition to only the users who have ownership of it.

CREATE OR REPLACE SECURE VIEW SALES_DEPT AS SELECT EMP.* FROM EMPLOYEE WHERE DEPT_NAME = 'SALES';

 

Column Level Security (CLS) � Data Masking:

Column Level Security in Snowflake allows to Mask the columns which contain Sensitive Data such as SSN number, Bank Account Number, etc.

Snowflake supports 2 types of Data Masking.

  1. Dynamic Data Masking
  2. External Tokenization

Dynamic Data Masking uses the masking policies to mask the data in the selective columns in a table or view.

Masking policies are nothing but the SQL Statements or conditions to mask the data at query time. This means the actual data in the table is not masked (no static masking) but while querying the table, based on user role or user group we apply masking policies to show either the masked data for the unauthorized user or unmasked/actual data for an authorized user.

CREATE MASKING POLICY Customer_PAN_Mask AS (mask string)

RETURNS STRING ->

CASE

   WHEN current_role() IN ('INVOICETEAM') THEN mask

   ELSE '******'

END;

 

We can apply this masking policy on a column like below.

ALTER TABLE CUSTOMER MODIFY COLUMN CUST_PAN SET MASKING POLICY Customer_PAN_Mask;

 

External Tokenization is nothing but Mask the data using external functions before loading it into the Snowflake table.

This means, Tokenization will mask the data permanently in the table and we have to unmask the data while querying the table for an authorized user.

CREATE MASKING POLICY Customer_PAN_UnMask AS (unmask string)

RETURNS STRING ->

CASE

WHEN current_role() IN('INVOICETEAM') THEN pan_unmask(unmask)

ELSE unmask

END;

We can apply the above unmask policy to the column below.

ALTER TABLE CUSTOMER MODIFY COLUMN CUST_PAN SET MASKING POLICY Customer_PAN_UnMask;

In high-level Snowflake supports/perform the security checks on a periodic basis.

  • More than 10 3rd party tests conducted on a yearly basis
  1. Network Penetration tests
  2. Application Penetration tests
  3. Functionality Penetration tests.
  • Vulnerability scans on its Infrastructure on weekly basis.
  • Following activities are monitored using automatic tools
  1. Threats
  2. Behavioral of Infrastructure like Failed Logins
  3. Unauthorized System modifications
  4. Network Traffic
  5. User Activity

Well, that's it, folks. Please provide your experience and issues in the section below and based on the criticality of the issue. Please watch this section for more exciting posts on Snowflake.

As a Snowflake partner, KPI Partners can help you with Snowflake migration assessment, among others, to achieve your performance and cost-benefit objectives. Let us contact you.

 

 

Ramana Kumar Gunti is a Manager at KPI Partners and has 15 Years of experience in Big Data echo systems, Modelling, and Cloud Analytics. He has extensively worked on Big Data, Cloud data warehouse, and cloud analytics projects using Hadoop, Snowflake, python, and Airflow. He is very enthusiastic to learn and explore new tools and technologies and be updated with all the features of the existing products.
View full post