Row access policy

CREATE OR REPLACE ROW ACCESS POLICY security.sales_policy
AS (sales_region varchar) RETURNS BOOLEAN ->
  'sales_executive_role' = CURRENT_ROLE()
    OR EXISTS (
      SELECT 1 FROM salesmanagerregions
        WHERE sales_manager = CURRENT_ROLE()
        AND region = sales_region
    )
;

Snowflake determines whether a row access policy is set on a database object.

  • SQL is parsed into AST
  • At each FROM node with table or view, look up the policy with the (role, table name) tuple

Snowflake creates a dynamic secure view (i.e. a secure inline view) of the database object.

  • Replace the from table table with a select all + from + selection subtree

The values of the columns specified in the ALTER TABLE or ALTER VIEW command (i.e when adding a row access policy to a table or view) are bound to the corresponding parameters in the policy, and the policy expression is evaluated.

  • Execute the sql

Aggregation policies

Aggregation policies can be used with or without an entity key. When aggregation policies are used without an entity key, they protect the privacy of individual rows in the data set (that is, row-level privacy). If you use an aggregation policy with an entity key, it protects the privacy of an entity, even if information about that entity appears in multiple rows (that is, entity-level privacy).

Entity-level privacy strengthens the privacy protections provided by aggregation policies. With entity-level privacy, Snowflake can ensure that an aggregation group contains a certain number of entities, not just a certain number of rows.

CREATE AGGREGATION POLICY my_agg_policy
  AS () RETURNS AGGREGATION_CONSTRAINT ->
    CASE
      WHEN CURRENT_ROLE() = 'ADMIN'
        THEN NO_AGGREGATION_CONSTRAINT()
      ELSE AGGREGATION_CONSTRAINT(MIN_GROUP_SIZE => 3)
    END;


ALTER TABLE t1 SET AGGREGATION POLICY my_agg_policy;
  • SQL to AST, and then when discovered an aggregation node replace this aggregation node with conditional grouping

SELECT state, AVG(elevation) AS avg_elevation
FROM t1
GROUP BY state;

becomes

--this query is not valid for Postgres
SELECT 
    CASE 
        WHEN COUNT(*) >= 3 THEN state
        ELSE 'Other'
    END AS state_group, 
    AVG(elevation) AS avg_elevation
FROM t1
GROUP BY 
    CASE 
        WHEN COUNT(*) >= 3 THEN state
        ELSE 'Other'
    END;

-- one Postgres version

SELECT
  state,
  AVG(elevation) AS avg_elevation
FROM
  (
    SELECT CASE
        WHEN policy.state IS NULL THEN NULL --filtered out
        ELSE policy.state
      END AS state,
      elevation
    FROM t1
      LEFT JOIN (
        SELECT state,
          COUNT(*) as count
        FROM t1
        GROUP BY state
        HAVING COUNT(*) >= 3
      ) policy ON t1.state = policy.state
  ) t1
GROUP BY
  state;