Aggregation policies¶

Limitations¶

For this preview:

• If the query uses an explicit grouping construct, it must be a GROUP BY clause. The query cannot use related constructs like GROUP BY ROLLUP, GROUP BY CUBE, or GROUP BY GROUPING SETS.

• Most set operators are not allowed when one of the queries acts on an aggregation-constrained table. As an exception, UNION ALL is supported, but each result group must satisfy the minimum group size of the aggregation-constrained tables being queried (see Query requirements for details).

• If a column of an aggregation-constrained table is protected by a projection policy, a query against that table cannot use the column as an argument of the COUNT function.

• Recursive CTEs are not allowed in queries against an aggregation-constrained table or view.

• Window functions are not allowed in queries against an aggregation-constrained table or view.

• A query against an aggregation-constrained table cannot use a correlated subquery or lateral join when there are references to or from the portion of the query that meets the requirements of the aggregation policy. The following examples illustrate the types of queries that are prohibited.

  Example 1

  Assuming protected_table is aggregation-constrained, the following query is not allowed because the portion of the query that aggregates data references another part of the query outside of the subquery:

  SELECT c1, c2
  FROM open_table
  WHERE c1 = (SELECT x FROM protected_table WHERE y = open_table.c2);
  

  Copy

  Example 2

  Assuming protected_table is aggregation-constrained, the following query is not allowed because the subquery references the part of the query that aggregates data, which is outside of the subquery:

  SELECT
    SUM(SELECT COUNT(*) FROM open_table ot WHERE pt.id = ot.id)
  FROM protected_table pt;
  

  Copy

Considerations¶

Consider the following when using aggregation policies to protect sensitive data:

• Aggregation policies protect data for an individual record, not an entity. If a data set contains multiple records belonging to the same entity, an aggregation policy only protects the privacy of a specific record pertaining to that entity, not the entire entity.

• While aggregation policies limit access to individual records, they do not guarantee a malicious actor could not use deliberate queries to obtain potentially sensitive data from an aggregation-constrained table. With enough query attempts, a malicious actor could potentially work around the aggregation requirements to ascertain a value from an individual row. Aggregation 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 (for example, reviewing the access history for their listings).

Calling internal functions from the body¶

The body of an aggregation policy uses two internal functions to define the constraints of the policy: NO_AGGREGATION_CONSTRAINT and AGGREGATION_CONSTRAINT. When the conditions of the body call one of these functions, the return value from the function determines how queries against the aggregation-constrained table or view must be formulated to return results.

NO_AGGREGATION_CONSTRAINT

When the policy body returns a value from this function, queries can return data from an aggregation-constrained table or view without restriction. For example, the body of the policy could call this function when an administrator needs to obtain unaggregated results from the aggregation-constrained table or view.

Call NO_AGGREGATION_CONSTRAINT without an argument.

AGGREGATION_CONSTRAINT

When the policy body returns a value from this function, queries must aggregate data in order to return results. Use the MIN_GROUP_SIZE argument to specify how many records must be included in each aggregation group.

The syntax of the AGGREGATION_CONSTRAINT function is:

AGGREGATION_CONSTRAINT ( MIN_GROUP_SIZE => <integer_expression> )

Copy

Where integer_expression resolves to the minimum group size of the policy.

There is a difference between passing a 1 and a 0 as the argument to the function. Both require results to be aggregated.

• Passing a 1 also requires that each aggregation group contain at least one record from the aggregation-constrained table. So for outer joins, at least one record from the aggregation-constrained table must match a record from an unprotected table.

• Passing a 0 allows the query to return groups that consist entirely of records from another table. So for outer joins between an aggregation-constrained table and an unprotected table, a group could consist of records from the unprotected table that do not match any records in the aggregation-constrained table.

Example policies¶

Fixed minimum group size

The simplest aggregation policy calls the AGGREGATION_CONSTRAINT function directly and defines a constant minimum group size that is applied to all queries against the table. For example, the following command creates an aggregation policy with a minimum group size of 5:

CREATE AGGREGATION POLICY my_agg_policy
  AS () RETURNS AGGREGATION_CONSTRAINT -> AGGREGATION_CONSTRAINT(MIN_GROUP_SIZE => 5);

Copy

Conditional policy

Policy administrators can define the SQL expression of an aggregation policy so different queries have different restrictions based on factors such as the role of the user executing the query. This strategy can allow one user to query a table without restriction while requiring others to aggregate results.

For example, the following aggregation policy gives users with the role ADMIN unrestricted access to a table while requiring all other queries to aggregate data into groups of at least 5 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 => 5)
    END;

Copy

Tip

You can use the following strategies when using context functions like CURRENT_ROLE 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.

Replace an aggregation policy¶

The recommended method of replacing an aggregation policy is to use the FORCE parameter to detach the existing aggregation 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 aggregation policy to a table that is already aggregation-constrained:

ALTER TABLE privacy SET AGGREGATION POLICY agg_policy_2 FORCE;

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

Discover aggregation policies¶

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

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

Copy

Identify aggregation policy references¶

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

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

   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(policy_name => 'my_db.my_schema.aggpolicy'));
   

   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

Other policies¶

This section describes how an aggregation policy interacts with other policies, including masking policies, row access policies, and projection policies.

You can attach other policies to an aggregation-constrained table. A successful query against the table must meet the requirements of all policies.

If a row access policy is assigned to an aggregation-constrained table, a row excluded from the query results based on the row access policy is not included when calculating the aggregated results.

The body of a masking policy, row access policy, or projection policy cannot reference an aggregation-constrained table, including its columns. Similarly, the body of the other policy cannot include a UDF that references the aggregation-constrained table.

Views and materialized views¶

You can assign an aggregation policy to both views and materialized views. When an aggregation policy is applied to a view, the underlying table does not become aggregation-constrained. This base table can still be queried without restriction.

To avoid the possibility of exposing sensitive data, all aggregation-constrained views are treated as if they are secure views even if they are not.

Whether you can create a view from an aggregation-constrained table depends on the type of view:

• You can create a regular view from one or more aggregation-constrained tables, however queries against that view must aggregate data in a way that meets the restrictions of those base tables.

• You cannot create a materialized view based on an aggregation-constrained table or view, nor can you assign an aggregation policy to a table or view upon which a materialized view is based.

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 aggregation policy object is not supported.

• Cloning a database results in the cloning of all aggregation policies within the database.

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

• A cloned table maps to the same aggregation 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 an aggregation policy in the same parent schema (i.e. a local reference), the cloned table will have a reference to the cloned aggregation policy.

  • If the source table refers to an aggregation 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¶

Aggregation 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 an aggregation policy in another database.

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

Aggregation policy privileges¶

Snowflake supports the following privileges on the aggregation policy object.

Note that operating on any object in a schema also requires the USAGE privilege on the parent database and schema.

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

Aggregation policy DDL reference¶

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

• CREATE AGGREGATION POLICY

• ALTER AGGREGATION POLICY

• DESCRIBE AGGREGATION POLICY

• DROP AGGREGATION POLICY

• SHOW AGGREGATION POLICIES

Summary of DDL commands, operations, and privileges¶

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

Note that operating on any object in a schema also requires the USAGE privilege on the parent database and schema.

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

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

   USE ROLE securityadmin;
   GRANT USAGE ON DATABASE mydb TO ROLE agg_policy_admin;
   GRANT USAGE ON SCHEMA mydb.schema TO ROLE proj_policy_admin;
   GRANT CREATE AGGREGATION POLICY ON SCHEMA mydb.schema TO ROLE aggregation_policy_admin;
   GRANT APPLY ON AGGREGATION POLICY ON ACCOUNT TO ROLE aggregation_policy_admin;
   

   Copy

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

   For example, the custom role finance_role role can be granted the permission to set the aggregation 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 AGGREGATION POLICY ON SCHEMA mydb.schema TO ROLE aggregation_policy_admin;
   GRANT APPLY ON AGGREGATION POLICY cost_center TO ROLE finance_role;
   

   Copy