Snowflake Projection Policies: Protect Data with Column Projection Control

A Projection Policy is a Snowflake data governance feature that controls whether a specific column of a table can be projected (output) in a SELECT list at all. While a Masking Policy transforms the data value and returns it, a Projection Policy allows or forbids the output of the column itself. Trying to SELECT a forbidden column causes the query to fail with an error, so you can completely remove even the existence of a sensitive column from query results. It is available in Enterprise Edition or higher.

Masking Policy vs. Projection Policy

Creating and Applying a Projection Policy

-- Create a Projection Policy
CREATE OR REPLACE PROJECTION POLICY pp_hide_ssn
  AS () RETURNS PROJECTION_CONSTRAINT ->
  CASE
    WHEN IS_ROLE_IN_SESSION('HR_ADMIN')
      THEN PROJECTION_CONSTRAINT(ALLOW => TRUE)
    ELSE PROJECTION_CONSTRAINT(ALLOW => FALSE)
  END;

-- Attach to a table column
ALTER TABLE hr.employees
  ALTER COLUMN ssn
  SET PROJECTION POLICY pp_hide_ssn;

-- Detach the policy
ALTER TABLE hr.employees
  ALTER COLUMN ssn
  UNSET PROJECTION POLICY;

Behavior Verification

-- Access with the HR_ADMIN role (projection allowed)
USE ROLE HR_ADMIN;
SELECT employee_id, name, ssn FROM hr.employees;
-- Result: employee_id, name, ssn are all returned

-- Access with the ANALYST role (projection forbidden)
USE ROLE ANALYST;
SELECT employee_id, name, ssn FROM hr.employees;
-- ERROR: Projection policy violation

-- Excluding the SSN column makes the query succeed
SELECT employee_id, name FROM hr.employees;
-- Result: employee_id, name are returned

-- SELECT * also fails because it includes the SSN column
SELECT * FROM hr.employees;
-- ERROR: Projection policy violation

-- Referencing the SSN column in a WHERE clause also fails
SELECT employee_id, name FROM hr.employees
WHERE ssn = '123-45-6789';
-- ERROR: Projection policy violation

Secure View Alternative Pattern

Historically, hiding columns was typically done with a Secure View. With a Projection Policy you can control access directly at the table level without creating separate views.

-- Traditional Secure View approach: column exclusion depends on the view definition
CREATE OR REPLACE SECURE VIEW hr.employees_analyst AS
SELECT employee_id, name, department, hire_date
FROM hr.employees;
-- The SSN column is excluded from the view definition → ANALYST uses this view

-- Projection Policy approach: apply control directly to the table
-- No view needed; the column is controlled even when the table is queried directly
ALTER TABLE hr.employees
  ALTER COLUMN ssn
  SET PROJECTION POLICY pp_hide_ssn;
ALTER TABLE hr.employees
  ALTER COLUMN salary
  SET PROJECTION POLICY pp_hide_salary;
-- ANALYST can query the table directly; only the restricted columns are off-limits

Approach-by-Approach Comparison

Required Privileges

-- Privilege to create Projection Policies
GRANT CREATE PROJECTION POLICY ON SCHEMA security
  TO ROLE policy_admin;

-- Privilege to attach Projection Policies
GRANT APPLY PROJECTION POLICY ON ACCOUNT
  TO ROLE policy_admin;

-- List policies
SHOW PROJECTION POLICIES IN SCHEMA security;

-- Inspect policy details
DESCRIBE PROJECTION POLICY pp_hide_ssn;

Tag-based Projection Policy

Combined with Object Tagging, a Projection Policy can be applied automatically to any column carrying a specific tag. Tagging a new column is enough to apply the policy, which streamlines governance management at scale.

-- Create a tag
CREATE TAG governance.pii_level
  ALLOWED_VALUES 'HIGH', 'MEDIUM', 'LOW';

-- Bind a Projection Policy to the tag
ALTER TAG governance.pii_level
  SET PROJECTION POLICY pp_hide_ssn;

-- Set the tag on a column (the Projection Policy is applied automatically)
ALTER TABLE hr.employees
  ALTER COLUMN ssn
  SET TAG governance.pii_level = 'HIGH';

Check Your Understanding

Frequently Asked Questions