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
- 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_SESSIONorCURRENT_ROLEfor 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.
How it works
Row access policies
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 POLICYon the account, orAPPLYon the policy together with ownership of the table. Creating one needsCREATE ROW ACCESS POLICYon the schema. - Keep the policy body simple. A heavy lookup in the policy runs for query work and slows it.
"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,AVGand so on). Selecting raw rows is blocked. - Groups below
MIN_GROUP_SIZEare merged into a remainder group where the group by key isNULL. - A table or view has only one aggregation policy.
SET ... FORCEreplaces 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.
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.
| Policy | Controls | Typical use |
|---|---|---|
| Row access | Which rows are visible | Regional or tenant isolation |
| Masking | What a column value looks like | Hide or partially show PII |
| Aggregation | Minimum group size in results | Privacy for small groups, clean rooms |
| Projection | Whether a column can be returned | Join on an identifier without exposing it |
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_SESSIONorCURRENT_ROLEfor 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 => TRUEorFALSE; 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 => FALSEon that column.What happens to a group of three rows when
MIN_GROUP_SIZEis 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.