Row access policies in Snowflake - Mini Project: Build & Apply

Row Access Policies in Snowflake

📖 Scenario: You are a data engineer at a company that stores sensitive employee data in Snowflake. You need to control which rows of the employees table each user can see based on their department.

🎯 Goal: Create a row access policy in Snowflake that restricts access to employee rows by department. Then apply this policy to the employees table so users only see rows for their own department.

📋 What You'll Learn

Create a row access policy named department_policy that checks if the department column matches the current user's department.

Create a table named employees with columns id, name, and department.

Apply the department_policy to the employees table on the department column.

Use the built-in function CURRENT_ROLE() or a session variable to simulate user department for the policy.

💡 Why This Matters

🌍 Real World

Row access policies help companies protect sensitive data by restricting which rows users can see based on their identity or role.

💼 Career

Data engineers and cloud architects use row access policies to enforce data security and compliance in cloud data warehouses like Snowflake.

1

Create the employees table

Write a SQL statement to create a table named employees with columns id (integer), name (string), and department (string).

Snowflake

-- Your code here to create the employees table

Hint

Use CREATE OR REPLACE TABLE employees and define the three columns with their types.

2

Create the row access policy department_policy

Write a SQL statement to create a row access policy named department_policy that returns TRUE only if the department column equals the session variable USER_DEPT. Use current_setting('USER_DEPT') to get the user's department.

Snowflake

CREATE OR REPLACE TABLE employees (
    id INT,
    name STRING,
    department STRING
);
-- Your code here to create the row access policy

Hint

Use CREATE OR REPLACE ROW ACCESS POLICY department_policy AS (department STRING) RETURNS BOOLEAN -> department = current_setting('USER_DEPT');

3

Apply the row access policy to the employees table

Write a SQL statement to alter the employees table and add the row access policy department_policy on the department column.

Snowflake

CREATE OR REPLACE TABLE employees (
    id INT,
    name STRING,
    department STRING
);

CREATE OR REPLACE ROW ACCESS POLICY department_policy AS
  (department STRING) RETURNS BOOLEAN ->
  department = current_setting('USER_DEPT');

-- Your code here to apply the row access policy to employees table

Hint

Use ALTER TABLE employees ADD ROW ACCESS POLICY department_policy ON (department);

4

Set the session variable and test row access policy

Write a SQL statement to set the session variable USER_DEPT to 'Sales'. This simulates the current user's department for the row access policy.

Snowflake

CREATE OR REPLACE TABLE employees (
    id INT,
    name STRING,
    department STRING
);

CREATE OR REPLACE ROW ACCESS POLICY department_policy AS
  (department STRING) RETURNS BOOLEAN ->
  department = current_setting('USER_DEPT');

ALTER TABLE employees ADD ROW ACCESS POLICY department_policy ON (department);

-- Your code here to set the session variable USER_DEPT to 'Sales'

Hint

Use ALTER SESSION SET USER_DEPT = 'Sales'; to set the session variable.

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