Snowflake Projection Policies

Protecting sensitive information within your data warehouse is a critical aspect of data governance. While access controls and masking policies provide essential safeguards, they don't always offer the fine-grained control you need to restrict specific columns from appearing in query results. That's where Snowflake Projection Policies come in.

Understanding Projection Policies

Projection policies are a powerful tool in Snowflake that allows you to define rules determining whether a column can be included in the output of a query. This enables you to:

Prevent Accidental Exposure: Avoid unintended disclosure of sensitive data by restricting certain columns from being displayed.
Comply with Regulations: Implement data minimization principles by showing only necessary columns for specific use cases.
Customize Data Access: Tailor what data users see based on their roles or responsibilities.

How Projection Policies Work

Create a Policy: Define a policy in SQL that specifies the conditions under which a column can be projected (included in query results).
Assign the Policy: Apply the policy to the specific column(s) you want to restrict.
Enforcement: Snowflake automatically enforces the policy, blocking queries that attempt to project restricted columns unless the conditions are met.

A Practical Example: Sales Data Analysis

Let's explore how to use Snowflake Projection Policies in a practical scenario.

⛁ Sample Data Model: salesdb-data-model

Setup the SalesDB sample data model and proceed with the rest of the exercises. This model provides a robust foundation for tracking customers, buyers, clients, and sales opportunities.

>_ SQL

// Assign the database schema
  use schema

Let's delve into how this Snowflake feature can supercharge your data workflow:

Protecting Customer PII With Projections

Let's use your sales data model (SalesDB.Custs) to illustrate how projection policies work.

Scenario:

You want to restrict access to certain personally identifiable information (PII) such as email addresses (Email) and phone numbers (Phone) in your Customer and Buyer tables. Only users with the SalesManager role should be able to see this information.

1. Creating Projection Policies:

>_ SQL

USE ROLE ACCOUNTADMIN;

-- Restrict Email Projection
CREATE OR REPLACE PROJECTION POLICY custs.restrict_email_projection
AS () RETURNS PROJECTION_CONSTRAINT ->
CASE
  WHEN SYSTEM$GET_TAG_ON_CURRENT_COLUMN('custs.pii') = 'Email' AND current_role() NOT IN ('SALESMANAGER')
    THEN PROJECTION_CONSTRAINT(ALLOW => false)
  ELSE PROJECTION_CONSTRAINT(ALLOW => true)
END;

-- Restrict Address Projection
CREATE OR REPLACE PROJECTION POLICY custs.restrict_address_projection
AS () RETURNS PROJECTION_CONSTRAINT ->
CASE
  WHEN SYSTEM$GET_TAG_ON_CURRENT_COLUMN('custs.pii') = 'Address' AND current_role() NOT IN ('SALESMANAGER')
    THEN PROJECTION_CONSTRAINT(ALLOW => false)
  ELSE PROJECTION_CONSTRAINT(ALLOW => true)
END

This will create 2 projection policies to restrict Email and Address visibility. It will only allow those with the SalesManager role to view it.

2. Assigning the Policies:

>_ SQL

-- Customer Table
ALTER TABLE custs.Customer MODIFY COLUMN Email SET PROJECTION POLICY restrict_email_projection;

--Buyer Table
ALTER TABLE custs.Buyer MODIFY COLUMN Email SET PROJECTION POLICY restrict_email_projection;
ALTER TABLE custs.Buyer MODIFY COLUMN Address SET

3. Testing the Policies:

>_ SQL

USE ROLE AccountAdmin;

-- Will be Hidden
SELECT * FROM custs.Customer; -- Email will be hidden
SELECT * FROM custs.Buyer; -- Email and Address will be hidden

--Will be Visible due to Exclusion of projection sensitive columns
SELECT * EXCLUDE (EMAIL) FROM custs.Customer; 
SELECT * EXCLUDE (EMAIL, ADDRESS) FROM

Testing now with a role that has privileges for the projections.

>_ SQL

USE ROLE SalesManager;

SELECT * FROM custs.Customer; -- Email will be visible
SELECT * FROM custs.Buyer; -- Email and Address will be visible

As you can see above, the columns will only show if the role running the query has permission to see it.

Important Note: It's important to note that projection policies limit access also through the snowsight schema and preview views.

Object browser Preview is limited

Schema Preview is limited due to projection

Removing Projection Policies

>_ SQL

USE ROLE AccountAdmin;
-- Unset the projection policy for a specific column
ALTER TABLE custs.Customer MODIFY COLUMN Email UNSET PROJECTION POLICY;

-- Unset the projection policy for Buyer
ALTER TABLE custs.buyer MODIFY COLUMN Email UNSET PROJECTION POLICY; 
ALTER TABLE custs.buyer MODIFY COLUMN

Discovering Projection Policies

>_ SQL

-- Show aggregation policies in your account
SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.PROJECTION_POLICIES
ORDER BY

Beyond Basic Restrictions

Projection policies can be more sophisticated than just role-based restrictions:

Conditional Logic: You can use complex SQL expressions to define the policy conditions. For example, a policy could allow access to a column only if the user belongs to a specific department and the current date is within a certain range.
Data Sharing: Projection policies are invaluable when sharing data with external parties. You can ensure that only specific columns are shared, protecting sensitive information.

Conclusion

Snowflake Projection Policies provide a powerful and granular way to control data visibility within your Snowflake environment. By defining and assigning appropriate policies, you can protect sensitive information, adhere to data privacy regulations, and empower different users with tailored access to the data they need.

Resource:

For detailed documentation and more advanced use cases, refer to Snowflake's official documentation on projection policies:

https://docs.snowflake.com/en/user-guide/projection-policies