Snowflake Data Access Policies

Snowflake’s features and capabilities enable you to manage, protect, and share your data securely and compliantly. This is called Data Governance. You can implement data governance in Snowflake by using Snowflake Data Access Policies, which are rules that let you control the access and usage of your data. These features are available in Snowflake Enterprise Edition.

You can use two types of policies to manage Data Access in Snowflake: Row-Level security and Column-Level security.

Column-Level Security

Column-level Security in Snowflake allows the application of a masking policy to a column within a table or view. Masking policies are schema-level objects that prevents unauthorized users to access sensitive data at query runtime. However, Snowflake does not change the sensitive data in the table (i.e. no static masking). Instead, when a user runs a query that involves a masking policy, the policy conditions decide whether the user sees the data as masked, partially masked, obfuscated, or tokenized.

Column-Level security includes two features: Dynamic Data Masking and External Tokenization.

Dynamic Data Masking vs. External Tokenization

Dynamic Data Masking is a Column-level Security feature that uses masking policies to selectively mask plain-text data in table and view columns at query time.

On the other hand, External Tokenization enables accounts to tokenize data before loading it into Snowflake and detokenize the data at query runtime. Tokenization is the process of removing sensitive data by replacing it with an undecipherable token. External Tokenization makes use of masking policies with external functions.

External Tokenization requires a third-party tokenization provider to tokenize data before loading data into Snowflake. At query runtime, Snowflake uses the external function to make a REST API call to the tokenization provider. Then it evaluates a tokenization policy (outside of Snowflake) to return either tokenized or detokenized data.

Enabling Column-Level Security

To enable Column-Level security, the first thing you need to do is to create a masking policy.

Note that masking policies for Dynamic Data Masking and External Tokenization adopt the same structure and format with one notable exception: masking policies for External Tokenization require using External Functions in the masking policy body.

In this example, only the PAYROLL role will be able to see the actual salary of employees; other roles will se blurred data.

-- Dynamic Data Masking

CREATE MASKING POLICY employee_ssn_mask AS (val string) RETURNS string ->
  CASE
    WHEN CURRENT_ROLE() IN ('PAYROLL') THEN val
    ELSE '******' -- obfuscated data
  END;

-- External Tokenization

  CREATE MASKING POLICY employee_ssn_detokenize AS (val string) RETURNS string ->
  CASE
    WHEN CURRENT_ROLE() IN ('PAYROLL') THEN ssn_unprotect(VAL)
    ELSE val -- sees tokenized data
  END;

As a result, you can execute the following statements to apply the policy to a table column or a view column.

-- apply masking policy to a table column

ALTER TABLE IF EXISTS employees MODIFY COLUMN salary SET MASKING POLICY employee_ssn_mask;

-- apply the masking policy to a view column

ALTER VIEW employees_v MODIFY COLUMN salary SET MASKING POLICY employee_ssn_mask;

Row-Level Security

Snowflake supports Row-Level Security through the use of row access policies to determine which rows to return in the query result. The row access policy can be relatively simple to allow one particular role to view rows, or be more complex. Complex policies include a mapping table in their definition to determine access to rows in the query result.

A row access policy is a schema-level object that determines whether a given row in a table or view can be viewed from the following types of statements:

SELECT statements
Rows selected by UPDATE, DELETE, and MERGE statements.

Row Access Policies at Query Runtime

At query runtime, Snowflake goes through the following process:

Snowflake determines whether a row access policy is set on a database object. If a policy is added to the database object, all rows are protected by the policy.
Snowflake creates a dynamic secure view (i.e. a secure inline view) of the database object.
The values of the columns specified in the ALTER TABLE or ALTER VIEW command (i.e when adding a row access policy to a table or view) are bound to the corresponding parameters in the policy, and the policy expression is evaluated.
Snowflake generates the query output for the user, and the query output only contains rows based on the policy definition evaluating to TRUE.

Enabling Row-Level Security

To enable Row-Level Security, the first thing you need to do is to create a row access policy.

In this example, non-IT administrators won’t be able to see all US-region related rows of the sales table.

CREATE OR REPLACE ROW ACCESS POLICY it_admin AS (allowed_region text) RETURNS BOOLEAN ->
  'it_admin' = current_role() AND allowed_regions = 'US';

Next, you can apply the policy to a table column or a view column.

-- apply row access policy to a table

ALTER TABLE sales ADD ROW ACCESS POLICY it_admin ON (allowed_regions);

-- apply row access policy to a view

ALTER VIEW sales_v ADD ROW ACCESS POLICY it_admin ON (allowed_regions)