Data Movement · Topic 14 of 17

Views and Row-Level Filtering

Choose between standard, secure and materialized views and filter rows per user with row access policies, mapping tables and secure views.

  • 5 min
In a nutshell
  • Standard, secure, materialized and recursive views exist. Secure views hide the definition and are the safe choice for sharing.
  • Materialized views store results, need Enterprise Edition and cost serverless maintenance credits.
  • Views run with the owner's rights, so consumers need privileges on the view only.

What it is

A view is a stored query that consumers read like a table. Row-level filtering limits which rows each user or role can see, either inside a view or with a row access policy attached to a table or view.

Base tableall rowsview or policy filtersSecure viewor row access policyreturnsConsumeronly allowed rows

How it works

View types

TypeStores dataKey points
Standard viewNoDefinition is visible to roles with access to the view; always reads current data
Secure view (CREATE SECURE VIEW)NoHides the definition from non owners and prevents optimizer shortcuts that could leak data; recommended for sharing logic
Materialized viewYes, maintained by SnowflakePrecomputes results of a query on one table; Enterprise Edition; serverless maintenance cost
Recursive viewNoUses a recursive CTE
CREATE SECURE VIEW eu_orders AS
  SELECT order_id, customer_id, amount
  FROM orders
  WHERE region = 'EU';
  • Secure views trade some performance for protection. Use them for shares, sensitive filters and cases where the definition is itself confidential.
  • Materialized views speed up repeated queries on a single table and are kept current in the background. A view with joins is not eligible. Pipelines and performance chapters cover them further.
  • CREATE OR REPLACE VIEW ... COPY GRANTS keeps existing grants on the replaced view.
  • ALTER VIEW ... SET SECURE and UNSET SECURE change the type. Use GET_DDL('VIEW', 'name') to read a definition, and SHOW VIEWS to list them.
  • A view reads the base table with the owner's privileges, so consumers need a grant on the view but not the table.
Exam tip

When the exam asks how to give a consumer a filtered slice without giving the base table, the answer is a view, and a secure view if the logic or data must not leak.

Row-level filtering with row access policies

A row access policy is a schema level object with a Boolean expression. A row is returned only when the expression is TRUE for the querying user.

CREATE ROW ACCESS POLICY region_policy AS (row_region STRING) RETURNS BOOLEAN ->
  CURRENT_ROLE() = 'ADMIN_ROLE'
  OR EXISTS (SELECT 1 FROM entitlements e
             WHERE e.role_name = CURRENT_ROLE() AND e.region = row_region);

ALTER TABLE orders ADD ROW ACCESS POLICY region_policy ON (region);
Userruns a queryrole and columnsRow access policyreturns TRUE or FALSEper rowRowsonly TRUE rows
  • The policy is evaluated at query time, and the filter applies to every query, including those through views built on the table.
  • A mapping table (the entitlements table above) lets you change who sees what by editing rows, without rewriting the policy.
  • A table or view can have only one row access policy at a time. Row access policies are an Enterprise Edition feature.
  • Useful context functions are CURRENT_ROLE(), IS_ROLE_IN_SESSION() and CURRENT_USER(). IS_ROLE_IN_SESSION() respects role hierarchy, which CURRENT_ROLE() does not.
  • If a table has both a row access policy and masking policies, the row access policy is evaluated first.

Filtering in views and shares

The older pattern is a secure view with a filter on a context function and a mapping table.

CREATE SECURE VIEW my_orders AS
  SELECT o.* FROM orders o
  JOIN account_map m ON o.partner_id = m.partner_id
  WHERE m.account_name = CURRENT_ACCOUNT();

In a data share, CURRENT_ROLE() and CURRENT_USER() do not describe the provider's intent, because the consumer's roles are in the consumer account. The standard pattern is a secure view that uses CURRENT_ACCOUNT() with a mapping table that says which consumer account may see which rows. Share the secure view and not the base table.

NeedUse
Different roles in the same account see different rowsRow access policy with a mapping table
Hide the query logic, share a filtered sliceSecure view
Each consumer account sees only its rows through a shareSecure view with CURRENT_ACCOUNT() and a mapping table
Watch out

Do not rely on a standard view to hide data from a consumer who can read its definition. Use a secure view or a policy.

Common mistakes

  • Sharing a base table when consumers should see only a slice. Share a secure view.
  • Using CURRENT_USER() in a view that is shared with other accounts. Use CURRENT_ACCOUNT().
  • Using CURRENT_ROLE() where IS_ROLE_IN_SESSION() is needed, which misses roles granted through the hierarchy.
  • Hard coding role names in a policy. Use a mapping table.
  • Adding a second row access policy to a table that already has one. Only one is allowed.
  • Expecting a materialized view over joins. It supports a single table.

What to remember for the exam

  • Standard, secure, materialized and recursive views exist. Secure views hide the definition and are the safe choice for sharing.
  • Materialized views store results, need Enterprise Edition and cost serverless maintenance credits.
  • Views run with the owner's rights, so consumers need privileges on the view only.
  • Row access policy: schema level object, Boolean result, ALTER TABLE ... ADD ROW ACCESS POLICY ... ON (cols), one per table or view.
  • Mapping tables keep entitlements in data, not in code.
  • For shares use a secure view with CURRENT_ACCOUNT().

Check yourself

  • Each consumer account of a share must see only its own partner's rows. How?

    Share a secure view that joins a mapping table and filters with CURRENT_ACCOUNT().

  • Which function respects role hierarchy inside a row access policy?

    IS_ROLE_IN_SESSION().

  • Why use a secure view instead of a standard view for sensitive logic?

    It hides the definition from non owners and blocks optimizer behavior that could leak filtered data.

Topic 14 of 17 in Data Movement

Test yourself on Data Movement

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.