Data Governance · Topic 5 of 7

Row Access and Aggregation Policies

Secure Snowflake rows with row access policies, mapping tables and secure views, and limit small group results with aggregation policies and group size.

  • 4 min
  • Includes a diagram
In a nutshell
  • Row-level options: row access policy (preferred), secure view with CURRENT_ROLE(), secure view with a mapping table.
  • CREATE ROW ACCESS POLICY ... RETURNS BOOLEAN, attached with ADD ROW ACCESS POLICY ... ON (col); one per table or view.
  • Row access policies run before masking policies.

What it is

Row-level security decides which rows a user can see. Two users run the same query on the same table, and each gets only their permitted rows. An aggregation policy goes a different way. It allows queries only if they combine enough rows, so no single person can be singled out.

Same querySELECT from salesrow access policyRole EMEAsees EMEA rowsSame querySELECT from salesrow access policyRole APACsees APAC rows

How it works

Options for row-level security

OptionHow it worksFit
Row access policyA policy attached to a table or view returns TRUE for rows a user may seeThe standard answer, centrally managed
Secure view with a role filterA secure view uses WHERE region = ... driven by CURRENT_ROLE()Older approach, more objects to manage
Secure view with a mapping tableThe view joins a table that maps roles or users to allowed valuesFlexible, but access must be through the view
Secure share viewsViews for consumers that filter by the consumer accountData sharing

A row access policy is preferred because it protects the table itself, so there is no unprotected path around it, and one policy can be reused.

Row access policies

A query first passes the row access policy, which can look up a mapping table and returns true or false per row, then masking policies mask column values in the remaining rows.

CREATE ROW ACCESS POLICY region_policy AS (region_val VARCHAR)
RETURNS BOOLEAN ->
  CURRENT_ROLE() = 'ADMIN_ROLE'
  OR EXISTS (
    SELECT 1 FROM governance.maps.role_region m
    WHERE m.role_name = CURRENT_ROLE()
      AND m.region    = region_val
  );

ALTER TABLE sales ADD ROW ACCESS POLICY region_policy ON (region);
  • The policy takes one or more columns as input, listed in the ON (...) clause.
  • It returns TRUE to allow the row and FALSE to hide it.
  • The policy runs at query time, and a user who cannot see rows gets no error. The rows simply are not there.
  • A table or view can have only one row access policy at a time.
  • The policy applies to SELECT and also limits which rows UPDATE, DELETE and MERGE can touch.
  • Row access policies are evaluated before column masking policies.
  • It needs Enterprise edition or higher.

Use a mapping table so access changes by inserting a row, not by rewriting the policy.

role_nameregion
EMEA_ANALYSTEMEA
APAC_ANALYSTAPAC
GLOBAL_ANALYSTEMEA
GLOBAL_ANALYSTAPAC
Exam tip

Row access policies work on views and tables, and a secure view can still sit on top for extra column control. Policy evaluation is part of the query, so keep the body simple and the mapping table small to avoid slow queries.

Aggregation policies

An aggregation policy requires that a query returns groups of at least N rows. Rows in groups that are too small are not shown individually.

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

ALTER TABLE patients SET AGGREGATION POLICY min_five;
  • Users with the constraint must aggregate, for example with COUNT, SUM or AVG and GROUP BY. A query that selects raw rows is blocked.
  • Groups smaller than MIN_GROUP_SIZE are combined into a leftover group, so individuals cannot be picked out.
  • NO_AGGREGATION_CONSTRAINT() lets chosen roles run unrestricted queries.
  • The policy sits on a table or view, not on a column.
  • An optional entity key, ENTITY KEY (patient_id), tells Snowflake what counts as an entity, so a group size means distinct people and not rows.
Analyst queryGROUP BY regionpolicy checksGroup sizeat least MIN_GROUP_SIZEpassesAggregated resultsmall groups combined
Row access policyAggregation policy
Question answeredWhich rows can this user seeCan this user see only summaries
Applied toTable or viewTable or view
ResultFewer rows, still row levelAggregated rows with groups of at least N
Typical caseRegional sales teamsHealthcare or clean room analytics
Watch out

An aggregation policy limits how data can be queried. It does not hide columns. Pair it with masking or projection policies if some columns must never appear.

Common mistakes

Adding two row access policies to one table. Only one is allowed. Combine the logic in one policy.

Expecting an error when rows are hidden. The query succeeds with fewer rows.

Using an aggregation policy as column protection. It controls group size, not columns.

Forgetting views. A row access policy on a base table also applies to views built on it.

What to remember for the exam

  • Row-level options: row access policy (preferred), secure view with CURRENT_ROLE(), secure view with a mapping table.
  • CREATE ROW ACCESS POLICY ... RETURNS BOOLEAN, attached with ADD ROW ACCESS POLICY ... ON (col); one per table or view.
  • Row access policies run before masking policies.
  • AGGREGATION_CONSTRAINT(MIN_GROUP_SIZE => n) forces summary queries; NO_AGGREGATION_CONSTRAINT() exempts a role.

Check yourself

  • How many row access policies can a table have?

    One at a time, so combine rules in a single policy.

  • What does a row access policy return for rows a user must not see?

    FALSE, so those rows are silently left out of the results.

  • What does MIN_GROUP_SIZE control?

    The minimum number of rows in a group before results can be shown, which stops queries from identifying individuals.

Topic 5 of 7 in Data Governance

Test yourself on Data Governance

The full Snowflake DEA-C02 question bank has 709 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.