Securing Columns in Snowflake Using Projection Policies

Projection Policies are an interesting Snowflake feature that can be very helpful in securing data, but also maintaining the data’s usefulness. They can make it possible for you to share data with other parties, and allow them to perform meaningful analysis on the data, without allowing them to actually see details you want to remain hidden. Here’s a quick example to explain how they work.

(“Relational algebra”, which is basically the study of the theory of behind relational databases introduces the concept of a database “projection”. In practical terms, the “selection” is the set of rows that result for a SELECT statement, and the “projection” is the set of columns that result.)

“Projection Policies” are security policies that restrict the columns that can be returned from a query (i.e. the “projection”) but do not restrict those columns when they are used as part of the “selection” i.e. in the definition of the rows. This means I can be allowed to use a column like employee_id in a join (which results in the “selection”) still but be prevented from seeing this column as part of the set of columns that are returned from my query.

In practical terms, let’s consider two tables I want to make available to my end users:

• One is a list of customers, CUSTOMER_LIST, which contains the CUSTOMER_ID, the STATE, and the geographic REGION of the customer.
• Another is a list of customers and their sales called CUSTOMER_SALES.
• I want certain users to be able to accurately aggregate the customer sales by geography, for which they need the customer id to join the tables together…
• …but for governance reasons I don’t want these users to actually see the distinct customer ids themselves.

To accomplish this, I will create a PROJECTION POLICY called customer_id_policy. This policy allows members of role UNRESTRICTED_ANALYST to see whatever column we apply this policy to, but does not allow anyone else to see it:

CREATE OR REPLACE PROJECTION POLICY customer_id_policy
AS () RETURNS PROJECTION_CONSTRAINT ->
CASE
  WHEN CURRENT_ROLE() = 'UNRESTRICTED_ANALYST'
    THEN PROJECTION_CONSTRAINT(ALLOW => true)
  ELSE PROJECTION_CONSTRAINT(ALLOW => false)
END;

Now I attach the policy to both tables, specifically to the CUSTOMER_ID column:

ALTER TABLE customer_list MODIFY COLUMN customer_id
SET PROJECTION POLICY customer_id_policy;

ALTER TABLE customer_sales MODIFY COLUMN customer_id
SET PROJECTION POLICY customer_id_policy;

If I run the following query as a member of role UNRESTRICTED_ANALYST, I get the expected results — because UNRESTRICTED_ANALYST can see CUSTOMER_ID, and retrieve it in a projection:

SELECT lst.CUSTOMER_ID
,lst.STATE
,lst.REGION
,SUM(SALE) AS SALES_BY_CUSTOMER
FROM CUSTOMER_LIST lst
INNER JOIN CUSTOMER_SALES sales
ON lst.customer_id=sales.customer_id
GROUP BY
lst.CUSTOMER_ID
,lst.STATE
,lst.REGION

But if I run the same query as a member of plain ANALYST, I get the following error:

Now if I then change the granularity of my query to only aggregate by. for example, REGION, I can run the query successfully:

SELECT 
lst.REGION
,SUM(SALE) AS SALES_BY_CUSTOMER
FROM CUSTOMER_LIST lst
INNER JOIN CUSTOMER_SALES sales
ON lst.customer_id=sales.customer_id
GROUP BY
lst.REGION

I now get the results I expect:

So if I do not include CUSTOMER_ID in the projection (the list of SELECTed columns) I can perform analysis to the level of detail I am permitted.

Note that I applied the PROJECTION POLICY to the CUSTOMER_ID in both tables. If I only apply it to one (eg CUSTOMER_LIST), I will be able to see customer ID if I qualify it with the un-protected table.

To demonstrate this, I remove the policy from CUSTOMER_SALES, and then run the query as plain ANALYST, qualifying the CUSTOMER_ID to come from CUSTOMER_SALES:

ALTER  TABLE customer_sales
ALTER  COLUMN customer_id
UNSET PROJECTION POLICY

SELECT sales.CUSTOMER_ID
,lst.STATE
,lst.REGION
,SUM(SALE) AS SALES_BY_CUSTOMER
FROM CUSTOMER_LIST lst
INNER JOIN CUSTOMER_SALES sales
ON lst.customer_id=sales.customer_id
GROUP BY
sales.CUSTOMER_ID
,lst.STATE
,lst.REGION

This is a potential security “gotcha”, in the event that the policy is inadvertently applied only to the table on one side of the join. Any use of Projection Policies should be thoroughly tested

Use of these Projection Policies needs to be carefully planned beforehand, but are a very useful addition to Snowflake’s governance and security toolkit.