Data privacy templates

Data privacy templates help you write DENY type grants, which are notoriously difficult in Snowflake.

Data privacy templates enable you define a custom list of tables or views across many databases and schemas. These tables will become accessible to a single role only. All other roles are denied access to those tables.

Getting started

This page covers how to walk through the following steps to set up your first data privacy rule:

1. Create a data privacy rule (e.g. FINANCE_VIEWER)

2. Decide which tables are included in the rule (e.g. ACME.PROD.SENSITIVE_REVENUE)

3. Grant the FINANCE_VIEWER privacy rule to a functional role (e.g. FINANCE_TEAM)

The end result:

1. Only members of the FINANCE_TEAM role can SELECT on ACME.PROD.SENSITIVE_REVENUE

2. Even if another role has SELECT privileges on this table (via future grants or otherwise), they will be denied access to the table (they will see empty results when querying).

For example:

The FINANCE_VIEWER role is granted access to the SENSITIVE_REVENUE table, and the SALES role is denied access, despite having ALL/FUTURE GRANTS on the schema.

How it works

You can think of data privacy rules as masking policy templates, with some added quality of life improvements.

Data privacy templates use tag-based masking policies at the table level to ensure that the entire table must be accessed by a specific role, which implicitly denies access to all other roles.

This is accomplished by creating a few objects:

1. A role that will become the low-level access role for the defined tables.

2. A tag to contain the masking policies. This tag will be applied to tables.

3. Masking policies that mask every data type, based on whether the new role is present.

And then by creating references that link these objects together:

1. Apply the masking policies to the tag.

2. Apply the tag to the defined tables.

3. Grant the necessary usage/select to the access role.

In the example of creating the FINANCE_VIEWER data privacy rule, the following commands become staged and ready to be applied:

CREATE ROLE IF NOT EXISTS SPYGLASS_FINANCE_VIEWER_VIEWER;
CREATE TAG IF NOT EXISTS SPYGLASS.GOVERNANCE.DATA_PRIVACY_FINANCE_VIEWER ALLOWED_VALUES 'active';
CREATE OR REPLACE MASKING POLICY SPYGLASS.GOVERNANCE.FINANCE_VIEWER_STRING_MASK AS (val string) RETURNS string -> CASE WHEN IS_ROLE_IN_SESSION('SPYGLASS_FINANCE_VIEWER_VIEWER') THEN val ELSE '***MASKED***' END;
CREATE OR REPLACE MASKING POLICY SPYGLASS.GOVERNANCE.FINANCE_VIEWER_NUMBER_MASK AS (val number) RETURNS number -> CASE WHEN IS_ROLE_IN_SESSION('SPYGLASS_FINANCE_VIEWER_VIEWER') THEN val ELSE -1 END;
CREATE OR REPLACE MASKING POLICY SPYGLASS.GOVERNANCE.FINANCE_VIEWER_DATE_MASK AS (val date) RETURNS date -> CASE WHEN IS_ROLE_IN_SESSION('SPYGLASS_FINANCE_VIEWER_VIEWER') THEN val ELSE '0000-01-01' END;
CREATE OR REPLACE MASKING POLICY SPYGLASS.GOVERNANCE.FINANCE_VIEWER_TIME_MASK AS (val time) RETURNS time -> CASE WHEN IS_ROLE_IN_SESSION('SPYGLASS_FINANCE_VIEWER_VIEWER') THEN val ELSE '00:00:00' END;
CREATE OR REPLACE MASKING POLICY SPYGLASS.GOVERNANCE.FINANCE_VIEWER_TIMESTAMP_MASK AS (val timestamp) RETURNS timestamp -> CASE WHEN IS_ROLE_IN_SESSION('SPYGLASS_FINANCE_VIEWER_VIEWER') THEN val ELSE '0000-01-01 00:00:00 +00:00' END;
CREATE OR REPLACE MASKING POLICY SPYGLASS.GOVERNANCE.FINANCE_VIEWER_VARIANT_MASK AS (val variant) RETURNS variant -> CASE WHEN IS_ROLE_IN_SESSION('SPYGLASS_FINANCE_VIEWER_VIEWER') THEN val ELSE '***MASKED***'::VARIANT END;
CREATE OR REPLACE MASKING POLICY SPYGLASS.GOVERNANCE.FINANCE_VIEWER_ARRAY_MASK AS (val array) RETURNS array -> CASE WHEN IS_ROLE_IN_SESSION('SPYGLASS_FINANCE_VIEWER_VIEWER') THEN val ELSE ['***MASKED***'] END;
CREATE OR REPLACE MASKING POLICY SPYGLASS.GOVERNANCE.FINANCE_VIEWER_OBJECT_MASK AS (val object) RETURNS object -> CASE WHEN IS_ROLE_IN_SESSION('SPYGLASS_FINANCE_VIEWER_VIEWER') THEN val ELSE {'***MASKED***': '***MASKED***'} END;
CREATE OR REPLACE MASKING POLICY SPYGLASS.GOVERNANCE.FINANCE_VIEWER_GEOMETRY_MASK AS (val geometry) RETURNS geometry -> CASE WHEN IS_ROLE_IN_SESSION('SPYGLASS_FINANCE_VIEWER_VIEWER') THEN val ELSE TO_GEOMETRY('POINT(0 0)') END;
GRANT USAGE ON DATABASE FINANCE TO ROLE SPYGLASS_FINANCE_VIEWER_VIEWER;
GRANT USAGE ON DATABASE SALES TO ROLE SPYGLASS_FINANCE_VIEWER_VIEWER;
GRANT USAGE ON SCHEMA FINANCE.REPORTS TO ROLE SPYGLASS_FINANCE_VIEWER_VIEWER;
GRANT USAGE ON SCHEMA SALES.REPORTS TO ROLE SPYGLASS_FINANCE_VIEWER_VIEWER;
GRANT SELECT ON TABLE FINANCE.REPORTS.DATA TO ROLE SPYGLASS_FINANCE_VIEWER_VIEWER;
GRANT SELECT ON TABLE SALES.REPORTS.DATA TO ROLE SPYGLASS_FINANCE_VIEWER_VIEWER;
ALTER TAG SPYGLASS.GOVERNANCE.DATA_PRIVACY_FINANCE_VIEWER SET MASKING POLICY SPYGLASS.GOVERNANCE.FINANCE_VIEWER_STRING_MASK, MASKING POLICY SPYGLASS.GOVERNANCE.FINANCE_VIEWER_NUMBER_MASK, MASKING POLICY SPYGLASS.GOVERNANCE.FINANCE_VIEWER_DATE_MASK, MASKING POLICY SPYGLASS.GOVERNANCE.FINANCE_VIEWER_TIME_MASK, MASKING POLICY SPYGLASS.GOVERNANCE.FINANCE_VIEWER_TIMESTAMP_MASK, MASKING POLICY SPYGLASS.GOVERNANCE.FINANCE_VIEWER_VARIANT_MASK, MASKING POLICY SPYGLASS.GOVERNANCE.FINANCE_VIEWER_ARRAY_MASK, MASKING POLICY SPYGLASS.GOVERNANCE.FINANCE_VIEWER_OBJECT_MASK, MASKING POLICY SPYGLASS.GOVERNANCE.FINANCE_VIEWER_GEOMETRY_MASK;
ALTER TABLE FINANCE.REPORTS.DATA SET TAG SPYGLASS.GOVERNANCE.DATA_PRIVACY_FINANCE_VIEWER = 'active';
ALTER TABLE SALES.REPORTS.DATA SET TAG SPYGLASS.GOVERNANCE.DATA_PRIVACY_FINANCE_VIEWER = 'active';

After the data privacy rule is defined in Spyglass, you can simply apply the DATA_PRIVACY_FINANCE_VIEWER to any Snowflake table or view and all of the necessary masking policies will be applied.

Tutorial

You can achieve the results above in a few steps in the Spyglass Cloud UI.

1 - On the Functional roles page, click on the Data privacy setting

2 - Create a new policy and select tables and views

3 - Open the uncommitted changes and create an access request

Approve and apply the access request and you're all set. From here, you can continue to manage the list of tables/views in the Spyglass Cloud UI. Alternatively, you can simply apply the DATA_PRIVACY_FINANCE_VIEWER to any Snowflake table or view and all of the necessary masking policies will be applied.