Row access policies in Snowflake - Deep Dive

Overview - Row access policies

What is it?

Row access policies in Snowflake control which rows of data a user can see or query. They act like filters that apply automatically when someone accesses a table. This helps keep sensitive data safe by showing only the rows a user is allowed to see. It works behind the scenes without changing the original data.

Why it matters

Without row access policies, users might see data they shouldn't, risking privacy and security breaches. These policies solve the problem of controlling data visibility at a very detailed level. This is important for companies that handle sensitive information like personal details or financial records. Without them, managing who sees what would be much harder and error-prone.

Where it fits

Before learning row access policies, you should understand basic Snowflake concepts like tables, users, and roles. After mastering row access policies, you can explore column-level security and dynamic data masking for even finer control. This topic fits into the broader journey of data security and governance in cloud data platforms.

Mental Model

Core Idea

Row access policies automatically filter table rows based on who is querying, ensuring users see only allowed data.

Think of it like...

It's like a library where each visitor has a special card that only lets them open certain bookshelves. The shelves are always there, but the card controls what you can actually read.

┌─────────────────────────────┐
│        Table Data           │
│ ┌───────────────┐           │
│ │ All Rows      │           │
│ │ ┌───────────┐ │           │
│ │ │ Row Access│ │           │
│ │ │ Policy    │ │           │
│ │ └───────────┘ │           │
│ └───────────────┘           │
│           ↓                 │
│ ┌───────────────┐           │
│ │ Filtered Rows │           │
│ │ (User View)   │           │
│ └───────────────┘           │
└─────────────────────────────┘

Build-Up - 7 Steps

1

Foundation

Understanding basic data visibility

Concept: Data visibility means what data a user can see when they query a table.

Imagine a big spreadsheet with many rows. Without any restrictions, anyone who can open it sees all rows. But sometimes, you want to hide some rows from certain people. This is the basic idea behind controlling data visibility.

Result

You realize that controlling which rows are visible is important for privacy and security.

Understanding that data visibility is about controlling access to rows sets the stage for why row access policies exist.

2

Foundation

Snowflake roles and permissions basics

Concept: Snowflake uses roles to control what users can do and see.

In Snowflake, users get roles that define their permissions. Roles control access to databases, schemas, tables, and more. But by default, roles control access at the table level, not individual rows.

Result

You know that roles manage access but don't filter rows by default.

Knowing that roles alone don't filter rows explains why row access policies are needed for finer control.

3

Intermediate

Introducing row access policies

🤔Before reading on: do you think row access policies change the data stored in tables or just control what users see? Commit to your answer.

Concept: Row access policies filter rows dynamically without changing the stored data.

A row access policy is a rule attached to a table that checks each row when a user queries it. The policy returns TRUE or FALSE for each row based on conditions like user role or attributes. Only rows where the policy returns TRUE are shown to the user.

Result

Queries automatically return only allowed rows, keeping data secure without altering the table.

Understanding that policies filter data on-the-fly helps grasp how security is enforced without data duplication or modification.

4

Intermediate

Creating and applying row access policies

🤔Before reading on: do you think a row access policy is applied by modifying the query or by attaching it to the table? Commit to your answer.

Concept: Row access policies are created as separate objects and then attached to tables or views.

You write a policy using SQL that defines the filtering logic. Then you attach it to a table's column(s). Snowflake applies this policy automatically whenever the table is queried. This means you don't have to change queries manually.

Result

The table enforces row-level filtering transparently for all users.

Knowing that policies are attached to tables and work automatically reduces the risk of human error in query writing.

5

Intermediate

Using session context in policies

🤔Before reading on: can row access policies use information about the current user or session? Commit to your answer.

Concept: Row access policies can use session variables like current user or role to decide row visibility.

Policies can reference built-in functions like CURRENT_USER() or CURRENT_ROLE() to customize filtering. For example, a policy might show only rows where the owner matches the current user. This makes policies dynamic and personalized.

Result

Users see only rows relevant to their identity or role without extra query effort.

Understanding session context use reveals how policies provide flexible, user-specific data access.

6

Advanced

Combining multiple row access policies

🤔Before reading on: do you think multiple row access policies on the same table combine with AND or OR logic? Commit to your answer.

Concept: Multiple row access policies combine with AND logic, restricting rows more strictly.

If a table has several policies attached, a row must pass all policies to be visible. This means policies stack to tighten access. You must design policies carefully to avoid unintentionally hiding all rows.

Result

Row visibility becomes more restrictive as policies combine, enforcing layered security.

Knowing how policies combine prevents accidental data hiding and helps design effective layered controls.

7

Expert

Performance and caching considerations

🤔Before reading on: do you think row access policies affect query performance or caching in Snowflake? Commit to your answer.

Concept: Row access policies add filtering logic that can impact query performance and cache effectiveness.

Because policies run on every query, complex logic can slow queries. Also, caching is less effective because results depend on the user context. Experts optimize policies for simplicity and test performance impact carefully.

Result

Well-designed policies balance security and performance, avoiding slow queries or cache misses.

Understanding performance tradeoffs helps experts write policies that secure data without hurting user experience.

Under the Hood

When a query runs on a table with a row access policy, Snowflake evaluates the policy's SQL expression for each row using the current session context. It returns TRUE or FALSE to decide if the row is visible. This filtering happens inside Snowflake's query engine before results are returned. The original data remains unchanged, and policies are enforced transparently.

Why designed this way?

Row access policies were designed to provide fine-grained, dynamic data security without duplicating or modifying data. Earlier methods required views or manual query filters, which were error-prone and hard to maintain. Snowflake chose a declarative, attachable policy model to simplify management and ensure consistent enforcement across all queries.

┌───────────────┐       ┌─────────────────────┐       ┌───────────────┐
│ User Query    │──────▶│ Snowflake Query     │──────▶│ Query Engine  │
│ (SELECT *     │       │ Processor applies    │       │ evaluates     │
│  FROM table)  │       │ Row Access Policies  │       │ policy per    │
└───────────────┘       └─────────────────────┘       │ row with      │
                                                      │ session info  │
                                                      └──────┬────────┘
                                                             │
                                                      ┌──────▼────────┐
                                                      │ Filtered Rows │
                                                      │ returned to   │
                                                      │ user         │
                                                      └──────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Do row access policies modify the data stored in tables? Commit to yes or no.

Common Belief: Row access policies change the data by deleting or hiding rows permanently.

Reality: Row access policies do not modify stored data; they only filter rows dynamically when queried.

Why it matters: Believing policies change data can lead to unnecessary data duplication or complex workarounds.

Quick: Can you bypass row access policies by writing custom queries? Commit to yes or no.

Common Belief: Users can bypass row access policies by crafting special queries or using views.

Reality: Row access policies are enforced by Snowflake's engine on all queries, so bypassing them is not possible through normal querying.

Why it matters: Thinking policies can be bypassed may cause distrust in security and lead to redundant controls.

Quick: Do multiple row access policies on a table combine with OR logic? Commit to OR or AND.

Common Belief: Multiple policies combine with OR, so passing one policy shows the row.

Reality: Policies combine with AND logic; a row must pass all policies to be visible.

Why it matters: Misunderstanding this can cause accidental data hiding or exposure.

Quick: Do row access policies improve query performance by reducing data scanned? Commit to yes or no.

Common Belief: Row access policies always make queries faster by filtering data early.

Reality: Policies add filtering logic that can slow queries and reduce cache efficiency.

Why it matters: Assuming performance always improves may lead to unoptimized policies and slow systems.

Expert Zone

1

Row access policies can reference session variables and user-defined functions for highly dynamic filtering.

2

Policies apply even to metadata queries, so careful design is needed to avoid exposing sensitive info indirectly.

3

Combining row access policies with masking policies provides layered security but requires understanding their interaction.

When NOT to use

Row access policies are not suitable when you need to restrict access based on complex joins or external data sources; in such cases, consider using secure views or external access controls. Also, for very high-performance needs, pre-filtered tables or materialized views might be better.

Production Patterns

In production, row access policies are often used to enforce multi-tenant data isolation, restrict access by geography or department, and comply with regulations like GDPR by limiting personal data exposure. They are combined with role-based access control and auditing for full security.

Connections

Role-Based Access Control (RBAC)

Row access policies build on RBAC by adding row-level filtering to role permissions.

Understanding RBAC helps grasp how row access policies extend access control from objects to individual data rows.

Database Views

Row access policies provide a dynamic alternative to views that filter rows manually.

Knowing views shows why row access policies simplify security by avoiding duplicated filtered tables.

Privacy Law Compliance

Row access policies help enforce data privacy laws by restricting access to sensitive data.

Understanding legal requirements clarifies why fine-grained data control is critical in cloud data platforms.

Common Pitfalls

#1 Attaching row access policy to wrong column

Wrong approach:

CREATE ROW ACCESS POLICY policy_name AS (user STRING) RETURNS BOOLEAN -> user = CURRENT_USER();
ALTER TABLE my_table ADD ROW ACCESS POLICY policy_name ON column_that_does_not_exist;

Correct approach:

CREATE ROW ACCESS POLICY policy_name AS (user STRING) RETURNS BOOLEAN -> user = CURRENT_USER();
ALTER TABLE my_table ADD ROW ACCESS POLICY policy_name ON correct_column_name;

Root cause:

Confusing which column the policy should apply to causes the policy not to work or errors.

#2 Writing overly complex policy logic

Wrong approach:

CREATE ROW ACCESS POLICY complex_policy AS (user STRING) RETURNS BOOLEAN -> (user = CURRENT_USER() AND some_complex_subquery > 0 OR other_condition);

Correct approach:

CREATE ROW ACCESS POLICY simple_policy AS (user STRING) RETURNS BOOLEAN -> user = CURRENT_USER();

Root cause:

Trying to do too much in one policy harms performance and maintainability.

#3 Assuming policies apply to all queries including administrative ones

Wrong approach:

Expecting row access policies to restrict data in SHOW or DESCRIBE commands.

Correct approach:

Understanding that row access policies apply only to SELECT queries on tables, not metadata commands.

Root cause:

Misunderstanding scope of policy enforcement leads to false security assumptions.

Key Takeaways

Row access policies in Snowflake filter table rows dynamically based on user context without changing stored data.

They provide fine-grained security by controlling which rows each user can see automatically on every query.

Policies combine with AND logic when multiple are attached, making access more restrictive.

Using session context inside policies enables personalized data access tailored to each user or role.

Well-designed row access policies balance security needs with query performance and maintainability.