Row access policies in Snowflake - Step-by-Step Execution

Process Flow - Row access policies

User Query Executed

↓

Check Row Access Policy

↓

Evaluate Policy Condition

↓

Return Row

↓

Return Filtered Result

When a user queries a table, Snowflake checks the row access policy condition for each row. Rows passing the condition are returned; others are hidden.

Execution Sample

Snowflake

CREATE ROW ACCESS POLICY secure_policy ON employees AS
  (user_role STRING) RETURNS BOOLEAN ->
  user_role = CURRENT_ROLE();

SELECT * FROM employees
  WHERE secure_policy(role);

Defines a row access policy that allows rows only if the user's role matches the row's role, then queries the table applying this policy.

Process Table

Step	Row Data (role)	Current Role	Policy Condition	Result	Action
1	role = 'HR'	HR	HR = HR	True	Return Row
2	role = 'Finance'	HR	Finance = HR	False	Hide Row
3	role = 'IT'	HR	IT = HR	False	Hide Row
4	role = 'HR'	HR	HR = HR	True	Return Row
5	role = 'Finance'	HR	Finance = HR	False	Hide Row
Exit	-	-	-	-	All rows evaluated; only matching roles returned

💡 All rows checked; only rows where role matches current user role are returned.

Status Tracker

Variable	Start	After 1	After 2	After 3	After 4	After 5	Final
Row role	-	HR	Finance	IT	HR	Finance	-
Current Role	HR	HR	HR	HR	HR	HR	HR
Policy Condition	-	True	False	False	True	False	-
Row Returned	-	Yes	No	No	Yes	No	-

Key Moments - 3 Insights

Why are some rows hidden even though they exist in the table?

Rows are hidden because the row access policy condition evaluated to False for those rows, as shown in execution_table rows 2, 3, and 5.

Does the policy change the data in the table?

No, the policy only filters rows at query time; the underlying data remains unchanged, as seen in variable_tracker where all roles remain the same.

What happens if the current role does not match any row's role?

No rows will be returned because the policy condition will be False for all rows, similar to the False results in execution_table.

Visual Quiz - 3 Questions

Look at the execution_table at Step 3, what is the policy condition result for the row with role 'IT'?

A False

B Unknown

C True

D Not evaluated

💡 Hint

Refer to execution_table row with Step 3 under 'Policy Condition' column.

At which step does the policy allow the row to be returned when the role is 'HR'?

A Step 2

B Step 4

C Step 3

D Step 5

💡 Hint

Check execution_table rows where 'Row Data (role)' is 'HR' and 'Result' is True.

If the current role changed to 'Finance', how would the policy condition result change for the row with role 'HR' at Step 1?

A It would be True

B It would be Unknown

C It would be False

D It would not be evaluated

💡 Hint

Look at how policy condition compares row role and current role in execution_table.

Concept Snapshot

Row Access Policies in Snowflake:
- Define conditions to filter rows per user role or attribute.
- Applied automatically during SELECT queries.
- Rows failing condition are hidden, not deleted.
- Helps enforce data security at row level.
- Syntax: CREATE ROW ACCESS POLICY name AS (args) RETURNS BOOLEAN -> condition;

Full Transcript

Row access policies in Snowflake control which rows a user can see when querying a table. When a query runs, Snowflake checks each row against the policy condition. If the condition is true, the row is returned; if false, the row is hidden. This filtering happens dynamically without changing the stored data. For example, a policy can allow users to see only rows matching their role. The execution table shows each row's role compared to the current user's role, the condition result, and whether the row is returned or hidden. This ensures users only access data they are authorized to see.