Row access policies in Snowflake - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding Row Access Policy Behavior

In Snowflake, a row access policy is applied to a table to control which rows a user can see based on a condition. Suppose you have a row access policy that allows users to see only rows where the column region matches their assigned region.

What happens when a user queries the table without any row access policy applied?

A The user sees only rows where region is NULL.

B The user sees no rows because no policy restricts access.

C The user sees all rows in the table regardless of region.

D The query fails with an error about missing row access policy.

💡 Hint

Think about what happens if no restrictions are applied.

If no row access policy is applied, Snowflake returns all rows in the table without filtering.

❓ Configuration

intermediate

Row Access Policy Syntax Validation

Which of the following Snowflake row access policy definitions is syntactically correct?

Assume the policy filters rows where department equals the current user's department.

Snowflake

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

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

B CREATE ROW ACCESS POLICY dept_policy (department STRING) RETURNS BOOLEAN AS department = CURRENT_ROLE();

C CREATE ROW ACCESS POLICY dept_policy AS (department STRING) RETURNS BOOLEAN AS department = CURRENT_ROLE();

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

💡 Hint

Check the arrow syntax and comparison operator.

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

❓ Architecture

advanced

Designing Row Access Policies for Multi-Region Data

You manage a Snowflake table with sales data for multiple regions. You want to ensure that users can only see sales data for their assigned region. Which architecture best enforces this using row access policies?

A Create a row access policy that filters rows where <code>region</code> equals the user's current role name, then apply it to the sales table.

B Create one row access policy that checks if the <code>region</code> column matches the user's assigned region stored in a session variable, then apply it to the sales table.

C Create separate tables for each region and grant access only to users assigned to those tables.

D Use a view that filters rows by region and grant users access to the view instead of the table.

💡 Hint

Consider how row access policies can use session variables for dynamic filtering.

Using a row access policy that compares the region column to a session variable holding the user's assigned region allows dynamic, centralized filtering.

❓ security

advanced

Row Access Policy Security Implications

Which of the following is a security risk when using row access policies in Snowflake?

A Applying a row access policy that uses a session variable that users can modify to bypass restrictions.

B Using a row access policy that filters rows based on a fixed column value.

C Applying row access policies only on sensitive columns, not on the entire table.

D Granting users only SELECT privileges on the table with row access policies applied.

💡 Hint

Think about how users might manipulate session variables.

If users can change session variables used in row access policies, they might see unauthorized rows, creating a security risk.

❓ service_behavior

expert

Row Access Policy Impact on Query Results

Given a Snowflake table employees with a row access policy that only allows rows where department = 'HR', what will be the result of this query by a user without any special roles?

SELECT COUNT(*) FROM employees;

A The query fails with an access denied error.

B The count of all rows in the employees table regardless of department.

C The query returns zero because the user has no roles.

D The count of only rows where department is 'HR'.

💡 Hint

Row access policies filter rows invisibly based on conditions.

The row access policy filters rows so the user only sees rows where department is 'HR', so the count reflects only those rows.

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