Projection policies¶

Column usage¶

Snowflake tracks column usage. Indirect references to a column, such as a view definition, UDF (in this topic), and common table expression, impact column projection when a projection policy is set on a column.

When a projection policy is set on the column and the column cannot be projected, the column:

• Is not included in the output of a query result.

• Cannot be inserted into another table.

• Cannot be an argument for an external function or stored procedure.

Limitations¶

• For limitations regarding user-defined functions (UDFs), see User-defined functions (UDFs) (in this topic).

• A projection policy cannot be applied to:

  • A tag assigned to a table or column (that is, a tag-based projection policy).

  • A virtual column or to the VALUE column in an external table. As a workaround, create a view and assign a projection policy to each column that should not be projected.

  • The value_column in a PIVOT construct. For related details, see UNPIVOT (in this topic).

• A projection policy body cannot reference a column protected by a masking policy or a table protected by a row access policy. For additional details, see Masking & row access policies (in this topic).

Considerations¶

Use projection policies when the use case calls for querying a sensitive column without directly exposing the column value to an analyst or similar role. The column value within a projection constrained column can be analyzed with greater flexibility than a masked or tokenized value. However, consider the following prior to setting a projection policy on a column:

• A projection policy does not prevent the targeting of an individual.

  For example, a user can filter rows where the name column corresponds to a particular individual, even if the column is projection constrained. However, the user cannot run a SELECT statement to view names of the individuals in the table.

• When a projection constrained column is the join key for a query that combines data from the protected table with data from an unprotected table, nothing prevents the user from projecting values from the column in the unprotected table. As a result, if a value in the unprotected table matches a value in the protected column, the user can obtain that value by projecting it from the unprotected table.

  For example, suppose a projection policy was assigned to the email column of the t_protected table. A user can still ascertain values in the t_protected.email column by executing:

  SELECT t_unprotected.email
    FROM t_unprotected JOIN t_protected ON t_unprotected.email = t_protected.email;
  

  Copy

• A projection constraint does not guarantee that a malicious actor could not use deliberate queries to obtain potentially sensitive data from a projection-constrained column. Projection policies are best suited for use with partners and customers with whom you have an existing level of trust. In addition, providers should be vigilant about potential misuses of their data (e.g. reviewing the access history for their listings).

• For all of these reasons, if you need to prevent leakage about a specific column or entity, you should omit the column entirely from your data, or employ differential privacy.

Example policies¶

The simplest projection policies call the PROJECTION_CONSTRAINT function directly:

Allow column projection

CREATE OR REPLACE PROJECTION POLICY mypolicy
AS () RETURNS PROJECTION_CONSTRAINT ->
PROJECTION_CONSTRAINT(ALLOW => true)

Copy

Prevent column projection

CREATE OR REPLACE PROJECTION POLICY mypolicy
AS () RETURNS PROJECTION_CONSTRAINT ->
PROJECTION_CONSTRAINT(ALLOW => false)

Copy

More complicated SQL expressions can be written to call the PROJECTION_CONSTRAINT function. The expression can use Conditional expression functions and Context functions to introduce logic to allow certain users with a particular role to project a column and prevent all other users from projecting a column.

Tip

You can use the following strategies when using context functions in a conditional policy:

• Context functions return strings, so comparisons using them are case-sensitive. You can use LOWER to convert strings to all lowercase if you’d like to do a case-insensitive comparison.

• The POLICY_CONTEXT function helps you evaluate whether a policy body is returning the correct value when a context function returns a certain value. The POLICY_CONTEXT function simulates query results based upon a specified value of one or more context functions.

The following example includes a CASE expression and CURRENT_ROLE context function to create a conditional policy that only allows users with the analyst custom role to project a column:

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

Copy

The following example uses the SYSTEM$GET_TAG_ON_CURRENT_COLUMN function so that a tag that is assigned to a column determines whether the column can be projected. In this case, when the policy is assigned to a column, the value of the tags.accounting_col tag on that column must be public in order to project the column.

CREATE PROJECTION POLICY mypolicy
AS () RETURNS PROJECTION_CONSTRAINT ->
CASE
  WHEN SYSTEM$GET_TAG_ON_CURRENT_COLUMN('tags.accounting_col') = 'public'
    THEN PROJECTION_CONSTRAINT(ALLOW => true)
  ELSE PROJECTION_CONSTRAINT(ALLOW => false)
END;

For data sharing use cases, the provider can write a projection policy to constrain column projection for all consumer accounts using the CURRENT_ACCOUNT context function, or selectively restrict column projection in specific shares using the INVOKER_SHARE context function. For example:

Restrict all consumer accounts

In this example, provider.account is the account identifier in the account name format:

CREATE OR REPLACE PROJECTION POLICY restrict_consumer_accounts
AS () RETURNS PROJECTION_CONSTRAINT ->
CASE
  WHEN CURRENT_ACCOUNT() = 'provider.account'
    THEN PROJECTION_CONSTRAINT(ALLOW => true)
  ELSE PROJECTION_CONSTRAINT(ALLOW => false)
END;

Copy

Restrict to specific shares

Consider a data sharing provider account that has a projection policy set on a column of a secure view. There are two different shares (SHARE1 and SHARE2) that can access the secure view to support two different data sharing consumers.

If a user in the data sharing consumer account attempts to project the column through either share they can project the column, otherwise the column cannot be projected:

CREATE OR REPLACE PROJECTION POLICY projection_share
AS () RETURNS PROJECTION_CONSTRAINT ->
CASE
  WHEN INVOKER_SHARE() IN ('SHARE1', 'SHARE2')
    THEN PROJECTION_CONSTRAINT(ALLOW => true)
  ELSE PROJECTION_CONSTRAINT(ALLOW => false)
END;

Copy

Query a separate table to determine the projection policy

You can use a SELECT query in your policy logic to help determine whether to allow or block projection. If you query a table (a mapping table) in this way, we recommend puting the mapping table in the same database as the protected table. This is particularly important if the body section calls IS_DATABASE_ROLE_IN_SESSION.

Here is an extended example of creating and populating a simple mapping table of role names and projection permission, and then querying that table to determine whether a column can be projected to the current user according to their role.

-- Create mapping table with two columns: role name, whether that role can project the column
CREATE OR REPLACE TABLE roles_with_access(role string, allowed boolean)
AS SELECT * FROM VALUES ('ACCOUNTADMIN', true), ('RANDOM_ROLE', false);

-- Create a policy that queries the mapping table, and allows projection when current
-- user role has an `allowed` value of TRUE.
-- Note that the logic is written to default to FALSE in all other cases, including the
-- current role not being in the queried table.
CREATE OR REPLACE PROJECTION POLICY pp AS () RETURNS projection_constraint ->
  CASE WHEN
    exists(
      SELECT 1 FROM roles_with_access WHERE role = current_role() AND allowed = true
    ) THEN projection_constraint(ALLOW=>true)
  ELSE projection_constraint(ALLOW=>false) END;

-- Create a new table with the policy and query it in one step.
CREATE OR REPLACE TABLE t(user string, address string WITH PROJECTION POLICY pp)
  AS SELECT * FROM VALUES ('Carson', 'CA'), ('Emily', 'NY'), ('John', 'NV');

-- Succeeds
USE ROLE ACCOUNTADMIN;
SELECT * FROM t;

-- Fails with projection policy error on column ADDRESS
USE ROLE any_other_role;
SELECT * FROM t;

Copy

Replace a projection policy¶

The recommended method of replacing a projection policy is to use the FORCE parameter to detach the existing projection policy and assign the new one in a single command. This allows you to atomically replace the old policy, leaving no gap in protection.

For example, to assign a new projection policy to a column that is already projection-constrained:

ALTER TABLE finance.accounting.customers
  MODIFY COLUMN account_number
  SET PROJECTION POLICY proj_policy2 FORCE;

You can also detach the projection policy from a column in one statement (… UNSET PROJECTION POLICY) and then set a new policy on the column in a different statement (… SET PROJECTION POLICY <name>). If you choose this method, the column is not protected by a projection policy in between detaching one policy and assigning another. A query could potentially access sensitive data during this time.

Discover projection policies¶

You can use the PROJECTION_POLICIES view in the Account Usage schema of the shared SNOWFLAKE database. This view is a catalog for all projection policies in your Snowflake account. For example:

SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.PROJECTION_POLICIES
ORDER BY POLICY_NAME;

Copy

Identify projection policy references¶

The POLICY_REFERENCES Information Schema table function can identify projection policy references. There are two different syntax options:

1. Return a row for each object (i.e. table or view) that has the specified projection policy set on a column:

   USE DATABASE my_db;
   SELECT policy_name,
          policy_kind,
          ref_entity_name,
          ref_entity_domain,
          ref_column_name,
          ref_arg_column_names,
          policy_status
   FROM TABLE(information_schema.policy_references(policy_name => 'my_db.my_schema.projpolicy'));
   

   Copy

2. Return a row for each policy assigned to the table named my_table:

   USE DATABASE my_db;
   USE SCHEMA information_schema;
   SELECT policy_name,
          policy_kind,
          ref_entity_name,
          ref_entity_domain,
          ref_column_name,
          ref_arg_column_names,
          policy_status
   FROM TABLE(information_schema.policy_references(ref_entity_name => 'my_db.my_schema.my_table', ref_entity_domain => 'table'));
   

   Copy

Masking & row access policies¶

This section describes how a projection policy interacts with a masking policy and a row access policy.

Multiple policies:

A column can have a masking policy and a projection policy at the same time, and the table containing this column can be protected by a row access policy. If all three policies are present, Snowflake processes the table and policies as follows:

1. Apply row filters according to the row access policy.

2. Determine if the query is attempting to project any columns that are restricted by the projection policy, and if so, reject the query.

3. Apply column masks according to the masking policy.

A column protected by a masking policy can also be projection constrained. For example, a masking policy set on a column containing account numbers can have a condition that allows users with the finance_admin custom role to see the account numbers and another condition to replace the account numbers with a hash for all other roles.

A projection policy can further restrict the column such that users with the analyst custom role cannot project the column. Note that users with the analyst custom role can still analyze the column by grouping hashes or joining on these hashes.

Snowflake recommends that policy administrators work with internal compliance and regulatory officers to determine the columns that should be projection constrained.

Policy evaluation:

A projection constrained column cannot be referenced by a masking policy or a row access policy when:

• Assigning a row access policy to a table.

• Enumerating one or more columns in a conditional masking policy.

• Performing a mapping table lookup.

As mentioned in the Limitations (in this topic), a projection policy body cannot reference a column protected by a masking policy or a table protected by a row access policy.

Dependent objects with other projection policies¶

Consider the following series of objects:

base_table » v1 » v2

Where:

• v1 is a view built from the table named base_table.

• v2 is a view built from v1.

If there is a query on a column in a view that is projection-constrained and that column depends on a projection constrained column in base_table, the view column will be projected only if both projection policies allow the column to be projected.

Snowflake checks the column lineage chain all the way to the base table to ensure that any references to the column are not projection constrained. If any column in the lineage chain is projection constrained and the column is not allowed to be projected, Snowflake blocks the query.

Views & materialized views¶

A projection policy on a view column constrains the view column and not the underlying base table column.

Regarding references, a projection policy that constrains a table column carries over to a view that references the constrained table column.

Streams & tasks¶

Projection policies on columns in a table carry over to a stream on the same table. Note that a projection policy cannot be set on a stream.

Similarly, a projection constrained column remains constrained when a task references the constrained column.

UNPIVOT¶

The result of an UNPIVOT construct depends on whether a column was initially constrained by a projection policy. Note:

• Constrained columns prior to and after executing UNPIVOT remain projection constrained.

• The name_column always appears in the query result.

• If any columns in the column_list are projection constrained, the value_column is also projection constrained.

Cloned objects¶

The following approach helps to safeguard data from users with the SELECT privilege on a cloned table or view that is stored in the cloned database or schema:

• Cloning an individual projection policy object is not supported.

• Cloning a schema results in the cloning of all projection policies within the schema.

• A cloned table maps to the same projection policies as the source table.

  • When a table is cloned in the context of its parent schema cloning, if the source table has a reference to a projection policy in the same parent schema (i.e. a local reference), the cloned table will have a reference to the cloned projection policy.

  • If the source table refers to a projection policy in a different schema (i.e. a foreign reference), then the cloned table retains the foreign reference.

For more information, see CREATE <object> … CLONE.

Replication¶

Projection policies and their assignments can be replicated using database replication and replication groups.

For database replication, the replication operation fails if either of the following conditions is true:

• The primary database is in an Enterprise (or higher) account and contains a policy but one or more of the accounts approved for replication are on lower editions.

• A table or view contained in the primary database has a dangling reference to a projection policy in another database.

The dangling reference behavior for database replication can be avoided when replicating multiple databases in a replication group.

User-defined functions (UDFs)¶

Note the following regarding projection constraints and UDFs:

Scalar SQL UDFs:

Snowflake evaluates the UDF and then applies the projection policy to the projection constrained column.

If a column in a SELECT statement is transitively derived from a UDF, which is also derived from a projection constrained column, Snowflake blocks the query. In other words:

pc_column » UDF » column (in SELECT statement)

Where:

• pc_column refers to a projection constrained column.

Because the column in the SELECT statement can be traced to a projection constrained column, Snowflake blocks the query.

SQL UDTFs:

SQL user-defined table functions (UDTF) follow the same behavior as SQL UDFs, except that because rows are returned in the function output, Snowflake evaluates each table column independently to determine whether to project the column in the function output.

Other UDFs:

The following applies to Introduction to Java UDFs, Introduction to JavaScript UDFs, Introduction to Python UDFs:

• A projection constrained column is constrained in the UDTF output.

Logging & Event Tables:

When a UDF, UDTF, or JavaScript UDF has a projection-constrained argument, Snowflake does not capture log and event details in the corresponding event table. However, Snowflake allows the UDF/UDTF to execute and does not fail the statement calling the UDF/UDTF due to logging reasons.

Projection policy privileges¶

Snowflake supports the following privileges on the projection policy object.

The USAGE privilege on the parent database and schema are required to perform operations on any object in a schema.

For details, see Summary of DDL commands, operations, and privileges (in this topic).

Projection policy DDL reference¶

Snowflake supports the following DDL to create and manage projection policies.

• CREATE PROJECTION POLICY

• ALTER PROJECTION POLICY

• DESCRIBE PROJECTION POLICY

• DROP PROJECTION POLICY

• SHOW PROJECTION POLICIES

Summary of DDL commands, operations, and privileges¶

The following table summarizes the relationship between projection policy privileges and DDL operations.

The USAGE privilege on the parent database and schema are required to perform operations on any object in a schema.

Snowflake supports different permissions to create and set a projection policy on an object.

1. For a centralized projection policy management approach in which the projection_policy_admin custom role creates and sets projection policies on all columns, the following permissions are necessary:

   USE ROLE securityadmin;
   GRANT USAGE ON DATABASE mydb TO ROLE projection_policy_admin;
   GRANT USAGE ON SCHEMA mydb.schema TO ROLE projection_policy_admin;
   
   GRANT CREATE PROJECTION POLICY ON SCHEMA mydb.schema TO ROLE projection_policy_admin;
   GRANT APPLY ON PROJECTION POLICY ON ACCOUNT TO ROLE projection_policy_admin;
   

   Copy

2. In a hybrid management approach, a single role has the CREATE PROJECTION POLICY privilege to ensure projection policies are named consistently and individual teams or roles have the APPLY privilege for a specific projection policy.

   For example, the custom role finance_role role can be granted the permission to set the projection policy cost_center on tables and views the role owns (i.e. the role has the OWNERSHIP privilege on the table or view):

   USE ROLE securityadmin;
   GRANT CREATE PROJECTION POLICY ON SCHEMA mydb.schema TO ROLE projection_policy_admin;
   GRANT APPLY ON PROJECTION POLICY cost_center TO ROLE finance_role;
   

   Copy