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

Need a 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

Need a 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

Need a 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'

Need a hint?

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