Account Management and Data Governance · Topic 6 of 9

Row Access Policies and Secure Views

Configure a row access policy, compare it with secure views, and predict its effect on queries, masking, cloning, replication and performance.

  • 5 min
In a nutshell
  • Row access policy: Enterprise Edition or higher; returns boolean per row; filters SELECT, UPDATE, DELETE and MERGE.
  • Needs CREATE ROW ACCESS POLICY, and APPLY ROW ACCESS POLICY or ownership plus APPLY on the policy.
  • Evaluated before masking policies; mapping tables enable role to value logic.

What it is

A row access policy is a schema-level object that decides, row by row, which rows a query may see. It returns TRUE for rows a user is allowed to see and FALSE for the rest. The policy is attached to a table or view, so the rule follows the data no matter how it is queried.

It requires Enterprise Edition or higher and filters rows for SELECT, UPDATE, DELETE and MERGE. It does not stop inserts, and it does not block updates or deletes of rows the user can already see.

Query on tableevaluated byRow access policyreturns TRUE or FALSE per rowkeepsOnly allowed rowsthen masking policies apply

How it works

Configure a policy

The policy has a signature (the columns it reads) and a boolean body. A common design uses a mapping table that links roles to the values they may see.

CREATE ROW ACCESS POLICY sec.rap_region AS (region STRING) RETURNS BOOLEAN ->
  IS_ROLE_IN_SESSION('SALES_ADMIN')
  OR EXISTS (
    SELECT 1 FROM sec.region_map m
    WHERE m.role_name = CURRENT_ROLE() AND m.region = region
  );

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

Privileges:

ActionNeeds
Create the policyCREATE ROW ACCESS POLICY on the schema
Add or drop it on a table or viewGlobal APPLY ROW ACCESS POLICY, or OWNERSHIP of the object plus APPLY on the policy
Alter the policyOWNERSHIP of the policy
Show or describe itAPPLY or OWNERSHIP of the policy

A policy can be changed with ALTER ROW ACCESS POLICY without removing it from the tables that use it, and one policy can serve many tables and views. Future grants on row access policies are not supported; grant APPLY ROW ACCESS POLICY to a custom role instead. Snowflake evaluates the policy under the policy owner's role, which is why the owner can read the mapping table even if the querying user cannot.

Exam tip

Keep the mapping table in the same database as the protected table, especially if you use IS_DATABASE_ROLE_IN_SESSION.

Row access policies versus secure views

A secure view hides its definition and optimizations that might leak data, and shows only the rows its query returns. It is a way to filter, but the filter lives in the view, and the base table must be locked down separately.

QuestionRow access policySecure view
Where is the rule?On the table or view itselfIn the view's query
Can users bypass it?No; applies to every access pathYes, if they hold privileges on the base table
ReuseOne policy across many objectsOne view per audience, often many
Change managementEdit the policy onceChange each view
Segregation of dutiesPolicy admin separate from table ownerOwner of view controls it
Role based logicBuilt in, with mapping tablesWritten in each view

Choose a row access policy when many objects need the same rule, when the rule must apply everywhere including through other views, or when policy administration must be separate from data ownership. A secure view still fits when you want a different shape of data (columns, joins, aggregates) for a group, or when you expose data to a share.

Many tables need one ruleuseRow access policyOne audience needs a custom shapeuseSecure view

Impact of attaching a policy

On queries and masking

  • Row access policies are evaluated before masking policies. Nested objects run the table policy first, then the view policy.
  • A column cannot appear in both a row access policy and a masking policy signature.
  • Queries such as COUNT(*) or MAX() lose the metadata shortcuts and can be much slower, so test with realistic workloads. Simple policies are cheaper than mapping table lookups.

On related features

FeatureEffect
CloningCloning a schema clones its policies; CREATE TABLE ... LIKE gives an empty table without the policy; CREATE TABLE ... AS SELECT keeps only visible rows and not the policy
ReplicationPolicies replicate with databases and replication groups; replication fails if target accounts are on a lower edition or if a policy reference is missing
Time TravelMapping tables are read at query time, so Time Travel does not change them
Materialized viewsA policy can attach to a materialized view, but a materialized view cannot be built on a table that has a policy, and a policy cannot be added to a table that already has a materialized view built on it
External tablesA policy can apply to the VALUE column; external tables cannot be mapping tables
StreamsThe policy applies to the table data when a stream reads it; a policy cannot be attached to a stream directly
ViewsThe CHANGES clause is not supported on protected views

Common mistakes

Expecting the policy to stop inserts. It filters what is visible; it does not block inserts.

Putting a masking policy and a row access policy on the same column. The documentation lists this as a restriction.

Testing with the wrong role. A role your policy lets through shows every row; always test with the roles users really have.

Using a heavy mapping table lookup on a huge table. It slows every query; prefer role checks or memoizable functions.

Assuming a clone keeps the rule everywhere. CREATE TABLE ... AS SELECT copies only the rows the creator could see, with no policy.

What to remember for the exam

  • Row access policy: Enterprise Edition or higher; returns boolean per row; filters SELECT, UPDATE, DELETE and MERGE.
  • Needs CREATE ROW ACCESS POLICY, and APPLY ROW ACCESS POLICY or ownership plus APPLY on the policy.
  • Evaluated before masking policies; mapping tables enable role to value logic.
  • It beats secure views when one rule must cover many objects and cannot be bypassed through the base table.
  • Impact: slower COUNT(*) and MAX(), replication edition checks, materialized view limits, clone rules.

Check yourself

  • Do row access policies run before or after masking policies?

    Before; rows are filtered first, then column masking applies.

  • Why might a secure view be bypassed where a policy would not?

    Users with privileges on the base table can query it directly, but a policy is attached to the table itself.

  • What happens to CREATE TABLE ... AS SELECT from a protected table?

    It copies only the rows visible to the creator and does not carry the policy.

Topic 6 of 9 in Account Management and Data Governance

Test yourself on Account Management and Data Governance

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