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
- Row access policy: Enterprise Edition or higher; returns boolean per row; filters
SELECT,UPDATE,DELETEandMERGE. - Needs
CREATE ROW ACCESS POLICY, andAPPLY ROW ACCESS POLICYor ownership plusAPPLYon 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.
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:
| Action | Needs |
|---|---|
| Create the policy | CREATE ROW ACCESS POLICY on the schema |
| Add or drop it on a table or view | Global APPLY ROW ACCESS POLICY, or OWNERSHIP of the object plus APPLY on the policy |
| Alter the policy | OWNERSHIP of the policy |
| Show or describe it | APPLY 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.
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.
| Question | Row access policy | Secure view |
|---|---|---|
| Where is the rule? | On the table or view itself | In the view's query |
| Can users bypass it? | No; applies to every access path | Yes, if they hold privileges on the base table |
| Reuse | One policy across many objects | One view per audience, often many |
| Change management | Edit the policy once | Change each view |
| Segregation of duties | Policy admin separate from table owner | Owner of view controls it |
| Role based logic | Built in, with mapping tables | Written 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.
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(*)orMAX()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
| Feature | Effect |
|---|---|
| Cloning | Cloning 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 |
| Replication | Policies replicate with databases and replication groups; replication fails if target accounts are on a lower edition or if a policy reference is missing |
| Time Travel | Mapping tables are read at query time, so Time Travel does not change them |
| Materialized views | A 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 tables | A policy can apply to the VALUE column; external tables cannot be mapping tables |
| Streams | The policy applies to the table data when a stream reads it; a policy cannot be attached to a stream directly |
| Views | The 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,DELETEandMERGE. - Needs
CREATE ROW ACCESS POLICY, andAPPLY ROW ACCESS POLICYor ownership plusAPPLYon 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(*)andMAX(), 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 SELECTfrom 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.