Accounts and Security · Topic 7 of 17

Row-Level Security, Aggregation and Projection Policies

Restrict rows with row access policies and protect privacy with aggregation and projection policies: syntax, mapping tables, evaluation order, exam traps.

  • 5 min
  • Includes a diagram
In a nutshell
  • Row access policy returns a boolean per row; one per table; evaluated before masking.
  • Mapping tables keep row access rules flexible; use IS_ROLE_IN_SESSION or CURRENT_ROLE for the lookup key.
  • Aggregation policy sets MIN_GROUP_SIZE; small groups go to a remainder group; entity keys protect multi row entities.

What it is

Row-level security decides which rows a role can see. In Snowflake it is done with a row access policy attached to a table or view. Two further policies protect privacy by limiting how data can be used: an aggregation policy forces results to be aggregated over a minimum group size, and a projection policy stops a column from appearing in query output while still allowing it in filters and joins.

All three are schema level policy objects that need Enterprise Edition or higher. They apply to every query, including those through views that you did not write.

QueryfirstRow access policywhich rowsthenColumn maskingwhich valuesthenAggregation policygroup sizethenProjection policycolumn in outputreturnsResult

How it works

Row access policies

Snowflake applies the row access policy first to filter rows, then the masking policy masks column values in the remaining rows before the result is returned. A row access policy takes one or more column values from each row and returns TRUE (row visible) or FALSE (row hidden).

CREATE ROW ACCESS POLICY region_rap AS (row_region STRING) RETURNS BOOLEAN ->
  IS_ROLE_IN_SESSION('GLOBAL_ANALYST')
  OR EXISTS (
    SELECT 1 FROM security.maps.region_access m
    WHERE m.role_name = CURRENT_ROLE() AND m.region = row_region
  );

ALTER TABLE sales.public.orders ADD ROW ACCESS POLICY region_rap ON (region);
ALTER TABLE sales.public.orders DROP ROW ACCESS POLICY region_rap;

The mapping table pattern in the example is the usual design: a small table lists which role or user may see which values, and the policy looks it up. Changing access then means editing a row, not rewriting the policy.

Rules to remember:

  • A table or view can have one row access policy at a time.
  • The policy is evaluated before column masking, so masking sees only rows the user may see.
  • Rows are filtered for every operation that reads the table, which means queries, joins and views built on it.
  • Adding or dropping a policy needs APPLY ROW ACCESS POLICY on the account, or APPLY on the policy together with ownership of the table. Creating one needs CREATE ROW ACCESS POLICY on the schema.
  • Keep the policy body simple. A heavy lookup in the policy runs for query work and slows it.
Orders tableall rowspolicy asksMapping tablerole to regionreturns TRUE or FALSEVisible rowsper role
Exam tip

"Each sales manager sees only their region and the rules change often" points to a row access policy with a mapping table. "Hide the view logic and share rows with a consumer" points to a secure view.

Aggregation policies

An aggregation policy protects individuals by requiring that results are aggregated. Queries must group enough rows together; groups smaller than the minimum are not shown on their own.

CREATE AGGREGATION POLICY min_five AS () RETURNS AGGREGATION_CONSTRAINT ->
  CASE
    WHEN IS_ROLE_IN_SESSION('PRIVACY_OFFICER') THEN NO_AGGREGATION_CONSTRAINT()
    ELSE AGGREGATION_CONSTRAINT(MIN_GROUP_SIZE => 5)
  END;

ALTER TABLE patients SET AGGREGATION POLICY min_five;
ALTER TABLE patients UNSET AGGREGATION POLICY;
  • A query on the protected table must aggregate (COUNT, AVG and so on). Selecting raw rows is blocked.
  • Groups below MIN_GROUP_SIZE are merged into a remainder group where the group by key is NULL.
  • A table or view has only one aggregation policy. SET ... FORCE replaces the existing one in one step.
  • Entity level protection uses an entity key, so that an entity spread over many rows (a patient with many visits) counts as one.
  • Rows removed by a row access policy are excluded before aggregation and do not count toward group size.
Raw rowspatientsaggregation policyGroups of 5 or moreshownsmall groupsRemainder groupkey is NULL

Projection policies

A projection policy controls whether a column may be projected, meaning returned in the output of a query. A column that cannot be projected can still be used in WHERE and JOIN conditions, so analysts can filter on it without seeing it.

CREATE PROJECTION POLICY no_project AS () RETURNS PROJECTION_CONSTRAINT ->
  PROJECTION_CONSTRAINT(ALLOW => FALSE);

ALTER TABLE customers MODIFY COLUMN ssn SET PROJECTION POLICY no_project;

A conditional body can allow specific roles, for example CASE WHEN IS_ROLE_IN_SESSION('COMPLIANCE') THEN PROJECTION_CONSTRAINT(ALLOW => TRUE) ELSE PROJECTION_CONSTRAINT(ALLOW => FALSE) END.

PolicyControlsTypical use
Row accessWhich rows are visibleRegional or tenant isolation
MaskingWhat a column value looks likeHide or partially show PII
AggregationMinimum group size in resultsPrivacy for small groups, clean rooms
ProjectionWhether a column can be returnedJoin on an identifier without exposing it
Watch out

A projection policy hides a column from output but does not stop inference. A user can filter with repeated WHERE conditions to find a value. Combine it with other policies where this matters.

Finding where policies are used

POLICY_REFERENCES (in INFORMATION_SCHEMA and SNOWFLAKE.ACCOUNT_USAGE) lists which objects use which policies. Use it to audit coverage.

Common mistakes

Trying to attach two row access policies. Only one is allowed per table or view. Combine the logic into one policy.

Embedding the access list in the policy. It makes each change a DDL change. Use a mapping table.

Expecting aggregation policies to block all inference. They set minimum group sizes, nothing more.

Using a projection policy and expecting values to be unusable. Joins and filters still work on the column.

What to remember for the exam

  • Row access policy returns a boolean per row; one per table; evaluated before masking.
  • Mapping tables keep row access rules flexible; use IS_ROLE_IN_SESSION or CURRENT_ROLE for the lookup key.
  • Aggregation policy sets MIN_GROUP_SIZE; small groups go to a remainder group; entity keys protect multi row entities.
  • Projection policy sets ALLOW => TRUE or FALSE; column stays usable in filters and joins.
  • All three need Enterprise Edition or higher and apply to views built on the object.

Check yourself

  • Which policy lets analysts join on a customer ID without ever returning it?

    A projection policy with ALLOW => FALSE on that column.

  • What happens to a group of three rows when MIN_GROUP_SIZE is 5?

    It is merged into the remainder group, where the group by key is NULL.

  • In what order are row access policies and masking policies applied?

    The row access policy is evaluated first, then masking applies to the rows that remain.

Topic 7 of 17 in Accounts and Security

Test yourself on Accounts and Security

The full Snowflake ARA-C01 question bank has 687 questions, with timed practice exams and an explanation for every answer.

This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.