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.

Practice

1. What is the main purpose of a row access policy in Snowflake?

easy

A. To encrypt data at rest

B. To control which rows a user can see in a table

C. To manage user login credentials

D. To optimize query performance automatically

Solution

1. Step 1: Understand the function of row access policies

   Row access policies are designed to filter rows based on conditions, controlling visibility.

2. Step 2: Compare with other options

   Encryption, login management, and query optimization are unrelated to row access policies.

3. Final Answer:

   To control which rows a user can see in a table -> Option B

4. Quick Check:

   Row access policy = row visibility control [OK]

Hint: Row access policies filter rows users can see [OK]

Common Mistakes:

• Confusing row access policies with encryption
• Thinking it manages user credentials
• Assuming it improves query speed automatically

2. Which of the following is the correct way to create a row access policy in Snowflake?

easy

A. CREATE ROW ACCESS POLICY policy_name AS (user_role STRING) RETURNS BOOLEAN { user_role = 'ADMIN' };

B. CREATE ROW ACCESS POLICY policy_name AS (user_role STRING) RETURNS BOOLEAN -> user_role == 'ADMIN';

C. CREATE ROW ACCESS POLICY policy_name AS (user_role STRING) RETURNS BOOLEAN -> user_role = 'ADMIN';

D. CREATE ROW ACCESS POLICY policy_name AS (user_role STRING) RETURNS BOOLEAN BEGIN RETURN user_role = 'ADMIN'; END;

Solution

1. Step 1: Recall Snowflake row access policy syntax

   The correct syntax uses 'AS (parameters) RETURNS BOOLEAN -> condition;' with a single equals sign for comparison.

2. Step 2: Analyze each option

   CREATE ROW ACCESS POLICY policy_name AS (user_role STRING) RETURNS BOOLEAN -> user_role = 'ADMIN'; matches the correct syntax. CREATE ROW ACCESS POLICY policy_name AS (user_role STRING) RETURNS BOOLEAN -> user_role == 'ADMIN'; uses '==' which is invalid in Snowflake SQL. CREATE ROW ACCESS POLICY policy_name AS (user_role STRING) RETURNS BOOLEAN { user_role = 'ADMIN' }; uses braces which are not valid. CREATE ROW ACCESS POLICY policy_name AS (user_role STRING) RETURNS BOOLEAN BEGIN RETURN user_role = 'ADMIN'; END; uses BEGIN...END block which is not used here.

3. Final Answer:

   CREATE ROW ACCESS POLICY policy_name AS (user_role STRING) RETURNS BOOLEAN -> user_role = 'ADMIN'; -> Option C

4. Quick Check:

   Correct syntax uses single '=' and '->' arrow [OK]

Hint: Use single '=' and '->' for policy condition [OK]

Common Mistakes:

• Using '==' instead of '=' for comparison
• Adding braces {} around condition
• Using BEGIN...END block in policy definition

3. Given the row access policy:

CREATE ROW ACCESS POLICY filter_policy AS (region STRING) RETURNS BOOLEAN -> region = CURRENT_REGION();

and a table with rows having regions 'US', 'EU', 'APAC', what rows will a user in region 'EU' see?

medium

A. Only rows where region = 'EU'

B. All rows regardless of region

C. Only rows where region = 'US'

D. No rows at all

Solution

1. Step 1: Understand the policy condition

   The policy returns TRUE only if the row's region matches the user's current region.

2. Step 2: Apply the policy for user in 'EU'

   Only rows with region = 'EU' will satisfy the condition and be visible.

3. Final Answer:

   Only rows where region = 'EU' -> Option A

4. Quick Check:

   Policy filters rows by matching region = CURRENT_REGION() [OK]

Hint: Policy shows rows matching user's current region [OK]

Common Mistakes:

• Assuming all rows are visible
• Confusing CURRENT_REGION() with a fixed value
• Thinking policy hides all rows for unmatched regions

4. You created a row access policy:

CREATE ROW ACCESS POLICY sales_policy AS (sales_amount NUMBER) RETURNS BOOLEAN -> sales_amount > 1000;

But after applying it, users see all rows including those with sales_amount = 500. What is the likely issue?

medium

A. The policy was not applied to the table column

B. The comparison operator should be '<' instead of '>'

C. The policy syntax is incorrect and causes errors

D. The sales_amount column has NULL values causing policy to fail

Solution

1. Step 1: Check policy syntax and logic

   The syntax and logic (sales_amount > 1000) are correct to filter rows.

2. Step 2: Verify policy application

   If users still see all rows, the policy was likely not applied to the table or column properly.

3. Final Answer:

   The policy was not applied to the table column -> Option A

4. Quick Check:

   Policy must be applied to enforce row filtering [OK]

Hint: Apply policy to table column to enforce filtering [OK]

Common Mistakes:

• Assuming wrong comparison operator
• Ignoring policy application step
• Blaming NULL values without checking application

5. You want to restrict access so that users can only see rows where department matches their assigned department stored in a session variable USER_DEPT. Which row access policy correctly enforces this?

hard

A. CREATE ROW ACCESS POLICY dept_policy AS (department STRING) RETURNS BOOLEAN -> department = CURRENT_ROLE();

B. CREATE ROW ACCESS POLICY dept_policy AS (department STRING) RETURNS BOOLEAN -> department = CURRENT_SESSION();

C. CREATE ROW ACCESS POLICY dept_policy AS (department STRING) RETURNS BOOLEAN -> department = CURRENT_USER();

D. CREATE ROW ACCESS POLICY dept_policy AS (department STRING) RETURNS BOOLEAN -> department = $USER_DEPT;

Solution

1. Step 1: Understand the requirement

   The policy must compare the row's department to the user's assigned department stored in a session variable USER_DEPT.

2. Step 2: Analyze options

   Options A, B, and D use CURRENT_ROLE(), CURRENT_SESSION(), and CURRENT_USER(), which do not directly access USER_DEPT variable. CREATE ROW ACCESS POLICY dept_policy AS (department STRING) RETURNS BOOLEAN -> department = $USER_DEPT; correctly accesses the session variable with $USER_DEPT.

3. Step 3: Confirm correct syntax

   CREATE ROW ACCESS POLICY dept_policy AS (department STRING) RETURNS BOOLEAN -> department = $USER_DEPT; shows the correct approach to access and compare the session variable USER_DEPT with the department column.

4. Final Answer:

   CREATE ROW ACCESS POLICY dept_policy AS (department STRING) RETURNS BOOLEAN -> department = $USER_DEPT; -> Option D

5. Quick Check:

   Use session variable USER_DEPT to filter department rows [OK]

Hint: Use session variable USER_DEPT to filter rows by department [OK]

Common Mistakes:

• Using CURRENT_ROLE() or CURRENT_USER() instead of session variable
• Not casting session variable properly
• Confusing CURRENT_SESSION() with user attributes