Row access policies in Snowflake - Commands & Configuration

Introduction

Row access policies control which rows of data a user can see in a table. They help protect sensitive information by filtering data based on user roles or conditions.

When you want to restrict employees to see only their own sales records in a sales table.

When you need to hide salary details from all users except HR staff.

When you want to show different data to users based on their department.

When you want to enforce data privacy rules without creating multiple copies of the data.

When you want to apply security rules directly in the database for consistent access control.

Commands

This command creates a row access policy named sales_region_policy. It allows users to see only rows where the region matches their current region.

Terminal

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

Expected Output

Successfully created row access policy sales_region_policy.

This command attaches the sales_region_policy to the sales_table on the region column. It enforces the policy when users query the table.

Terminal

ALTER TABLE sales_table ADD ROW ACCESS POLICY sales_region_policy ON (region);

Expected Output

Successfully altered table sales_table.

This command queries the sales_table. The row access policy filters rows so the user only sees data for their region.

Terminal

SELECT * FROM sales_table;

Expected Output

ID | PRODUCT | REGION | SALES

1  | Widget  | US     | 1000

3  | Gadget  | US     | 500

Key Concept

If you remember nothing else from this pattern, remember: row access policies filter table rows dynamically based on user context or conditions.

Common Mistakes

Not attaching the row access policy to the table after creating it.

The policy won't be enforced and users will see all rows.

Always run ALTER TABLE to add the row access policy to the target table.

Writing the policy condition without referencing the correct column or user context.

The policy may block all rows or allow all rows unintentionally.

Use correct column names and Snowflake functions like CURRENT_REGION() or CURRENT_ROLE() in the policy condition.

Summary

Create a row access policy with a condition to filter rows based on user context.

Attach the policy to a table column using ALTER TABLE to enforce it.

Query the table to see filtered rows according to the policy.