Row Access Policies, Aggregation Policies, Clean Rooms and Horizon Catalog

1.How row access policies filter rows

Snowflake implements row-level security with row access policies. A row access policy is a schema-level object that returns a BOOLEAN for each row. It controls which rows SELECT statements return and which rows UPDATE, DELETE and MERGE statements can select. One policy can protect several tables and views. Like masking policies, row access policies let governance teams protect data even from the object owner.

CREATE OR REPLACE ROW ACCESS POLICY rap_it
AS (empl_id varchar) RETURNS BOOLEAN ->
  'it_admin' = current_role()
;

A policy can be as simple as a check on the current role. To take the role hierarchy into account, use IS_ROLE_IN_SESSION. For richer rules, the policy can look up a mapping table, for example sales managers mapped to regions. Keep that mapping table in the same database as the protected table. This matters especially if the policy calls the IS_DATABASE_ROLE_IN_SESSION function.

At runtime, Snowflake first checks whether a policy is attached to the object. If one is, it wraps the object in a dynamic secure view. It then binds the policy's columns to the policy parameters and evaluates the expression. Only rows where the expression is TRUE are returned. When policies are nested, the table's policy runs first, then each view's policy runs in order along the chain.

A common misreading: a row access policy filters what can be read. It does not stop inserts, and it does not stop updates or deletes on rows the caller can already see.

Sources1

2.Row access policy DDL, limitations and performance

You can attach a row access policy when you create a table or view, or later. Masking policies use SET on a column. Row access policies are attached to the table, using ALTER TABLE clauses that list the columns to bind:

To change a policy's logic, use ALTER ROW ACCESS POLICY. The table stays protected while you edit. If the policy uses a mapping table, you can change entitlements by updating the table, with no DDL at all.

Limitations to know:

- An external table cannot be the mapping table. - You cannot attach a policy to a stream object. Streams still respect the policy on their base table. - The CHANGES clause is not supported on a protected view. - Future grants on row access policies are not supported. Grant APPLY ROW ACCESS POLICY to a custom role instead. - Attaching row access policies to tables that are already protected by other row access policies or masking policies may cause errors.

Performance: use few arguments, because every bound column is scanned. Prefer simple CASE logic to table lookups. Where a lookup is needed, put it in a memoizable function. On very large tables, cluster by the columns the policy filters on. Expect COUNT(*) to be slower, because Snowflake has to scan for the rows the caller can see.

Swapping a masking policy. A column can be attached to only one masking policy, so setting a second one on a column that already has one fails with an error. You can unset the first policy and then set the new one. Alternatively, add the FORCE keyword to ALTER TABLE ... MODIFY COLUMN ... SET MASKING POLICY to replace the current masking (or projection) policy with a different one in a single statement. The data type of the new policy must match the data type of the policy currently set on the column. If no masking policy is set on the column, FORCE has no effect. FORCE also appears on SET AGGREGATION POLICY and SET JOIN POLICY, where it atomically replaces the existing policy.

Sources2134

3.Aggregation policies: answers only in groups

Row access policies decide which rows a query can see. An aggregation policy decides what kind of query is allowed. A table with an aggregation policy is called aggregation-constrained. Queries against it must aggregate rows into groups of at least a minimum size before they return anything, so no query can return a single record. The provider's policy administrator sets the minimum group size.

Aggregation policies are designed for sharing. They let a provider keep control after the data reaches a consumer: the consumer can aggregate the data but cannot retrieve individual records. Without an entity key, the policy protects individual rows. If you set ENTITY KEY when assigning the policy, it protects an entity, such as a person, whose data is spread across many rows.

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

Every aggregation policy has the same signature, AS () RETURNS AGGREGATION_CONSTRAINT, with no arguments. The body calls AGGREGATION_CONSTRAINT(MIN_GROUP_SIZE => n) to require aggregation, or NO_AGGREGATION_CONSTRAINT() to allow unrestricted access, for example for an admin role in a CASE expression. The body cannot reference a user-defined function, table or view. After creating the policy, assign it to a table with ALTER TABLE or to a view with ALTER VIEW. SET AGGREGATION POLICY ... FORCE atomically replaces an existing aggregation policy.

What queries can do against an aggregation-constrained table:

- They must aggregate, and any aggregation function used must be one of the allowed aggregation functions. - Groups smaller than the minimum are combined into a remainder group. The value of the GROUP BY key column in that group is NULL. If there are not enough rows even for a remainder group, the query still works but returns NULL in every field. - An explicit grouping construct must be a plain GROUP BY. ROLLUP, CUBE and GROUPING SETS are not allowed. - You cannot protect an external table with an aggregation policy.

Sources526

4.Snowflake Data Clean Rooms: the UI and the developer APIs

Sometimes two parties need to analyse each other's data together, and neither wants to give the other its raw data. Snowflake Data Clean Rooms handle this case. A clean room is a secure environment where several collaborators combine and analyse their data. A collaboration owner assigns roles to each participant. For example, an Analysis Runner can run templates that other collaborators provide. Any collaborator can submit templates and decide who may use each one.

Roles. A collaborator can be an Owner, who creates the collaboration and assigns roles. It can be a Data Provider, who provides data and specifies the Snowflake policies that apply to it within the collaboration. Or it can be an Analysis Runner, who runs templates against the data. A collaborator can hold more than one role.

Clean room versus share. With a share or a listing, the consumer can directly query the shared data. In a clean room, collaborators can return aggregated results and insights but cannot directly query the raw data. The data provider controls which analyses can be run. Results can be exposed directly or activated to a collaborator's Snowflake account.

Editions. Data providers must use Enterprise Edition. Owners and analysis runners can use Standard Edition. Activating results to another Snowflake account also requires Enterprise Edition.

Sources7

5.Horizon Catalog: policies that apply outside Snowflake

Data protection also has to hold when engines outside Snowflake read the data. Horizon Catalog exposes the Apache Iceberg REST API, with Apache Polaris integrated into it. This lets any engine that speaks the Iceberg REST protocol, such as Apache Spark, read and write Snowflake-managed Iceberg tables through a single endpoint, using vended credentials. Externally managed Iceberg tables in a catalog-linked database are available through the same endpoint.

For governance, what matters is that external access still goes through Snowflake's existing users, roles, policies and authentication. Snowflake enforces dynamic data masking policies on Iceberg tables queried from Spark through Horizon Catalog, so a column you masked for Snowflake users stays masked for Spark users. This matches the sharing story elsewhere in this subdomain. Masking makes it easy to mask data before sharing, and INVOKER_SHARE lets a policy react to the share that is accessing the data.

Federation specifics. To reach externally managed Iceberg tables, you first set up a catalog-linked database. External engines then use the Horizon Iceberg REST Catalog endpoint, passing the catalog-linked database name as the warehouse property. Access to externally managed tables outside a catalog-linked database is not supported through Horizon IRC. To use vended credentials with these tables, the catalog-linked database needs an EXTERNAL_VOLUME, because credential vending for externally managed tables is disabled by default. The goal is to enforce Snowflake RBAC policies consistently, wherever the table is managed.

Row access as well as masking. Through the Scan Plan API, Horizon Catalog evaluates both row access and masking policies. It generates a filtered scan plan so the external engine only sees the authorized subset of data, with no policy logic in the engine itself.

Auditing. Successful Horizon IRC operations are recorded in ACCESS_HISTORY, where you can filter on event_source = 'horizon_irc'.

Sources8491011

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.A row access policy stops users from inserting, updating or deleting rows they should not touch.Why is that wrong?

   Row access policies control which rows are visible to SELECT and to the row selection of UPDATE, DELETE and MERGE. They do not block inserts, and they do not stop changes to rows the caller can already see.

   Covered in How row access policies filter rows

2. 2.Any table can be the mapping table for a row access policy, including an external table over files in cloud storage.Why is that wrong?

   External tables are explicitly not supported as mapping tables. Keep the mapping table as a regular table in the same database as the protected table.

   Covered in Row access policy DDL, limitations and performance

3. 3.To replace a masking policy on a column you must always unset the old policy first, because a column can hold only one.Why is that wrong?

   The FORCE keyword replaces the masking policy currently set on a column in a single statement, as long as the new policy has a matching data type.

   Covered in Row access policy DDL, limitations and performance