Skip to main content

Row level Security

πŸ”’ What is Row-Level Security (RLS)?​

Row-Level Security (RLS) controls which rows of a table a user can access, based on policies. It ensures users see only the data they’re authorized to see, even if they share the same queries or views.


πŸ’‘ Use Cases for Row-Level Security​

  • βœ… Multi-Tenant SaaS Applications Each customer (tenant) should see only their own data.

  • βœ… Departmental Data Segregation Different teams (HR, Finance) see only the rows relevant to their work.

  • βœ… Regional Data Restrictions Data visibility is based on a user’s region (APAC, EMEA, etc.).

  • βœ… Regulatory Compliance Restrict sensitive data access for compliance (e.g., PCI DSS, GDPR).


βš™οΈ SQL Implementation in Snowflake​

Snowflake implements RLS using row access policies (introduced in 2022).


1️⃣ Create a Row Access Policy​

CREATE ROW ACCESS POLICY rls_policy_region
AS (region STRING) RETURNS BOOLEAN ->
CURRENT_ROLE() IN (
'REGION_' || UPPER(region) || '_ROLE',
'ADMIN_ROLE'
);
  • βœ… This policy allows only users with a role matching the region (e.g., REGION_APAC_ROLE) or ADMIN_ROLE.

2️⃣ Apply the Policy to a Table​

ALTER TABLE sales_data
ADD ROW ACCESS POLICY rls_policy_region ON (region);
  • βœ… Now, whenever users query sales_data, they see only rows matching their authorized region.

3️⃣ Example Query​

-- A user with REGION_APAC_ROLE:
SELECT * FROM sales_data;
  • βœ… Output: Only rows where region = 'APAC'.

🌟 Best Practices for Row-Level Security​

πŸ”Ή Minimize Policy Complexity Keep policies simple and easy to maintain.

πŸ”Ή Use Roles Thoughtfully Define clear roles (REGION_*_ROLE, DEPARTMENT_*_ROLE) that map to data access needs.

πŸ”Ή Combine with Other Security Layers

  • βœ… Column-level masking for sensitive columns.
  • βœ… Network policies & MFA for stronger access control.

πŸ”Ή Audit & Monitor Use Snowflake’s Account Usage views (e.g., ACCESS_HISTORY) to monitor RLS usage and validate access patterns.

πŸ”Ή Test Policies Thoroughly

  • βœ… Use test accounts to verify that RLS policies are working as intended before deploying to production.

πŸ› οΈ When to Use Row-Level Security?​

  • βœ… Data Separation – Different users/roles see different rows, but run the same queries.
  • βœ… Dynamic Filtering – No need to create separate tables or views for each role.
  • βœ… Compliance & Least Privilege – Enforce least privilege data access at the row level.

πŸš€ Summary​

βœ”οΈ Row-Level Security (RLS) in Snowflake dynamically filters rows based on user roles. βœ”οΈ Ideal for multi-tenant apps, data compliance, and departmental/geo segregation. βœ”οΈ Use CREATE ROW ACCESS POLICY + ALTER TABLE ... ADD ROW ACCESS POLICY for easy implementation. βœ”οΈ Combine RLS with RBAC roles and masking policies for comprehensive security.


Scenario​

We have a sales_data table storing sales records for different regions. We want to ensure users see only the data for their assigned region.


πŸ”¨ Step 1: Setup – Create Roles and Users (Simulated)​

-- 1️⃣ Create roles
CREATE ROLE REGION_APAC_ROLE;
CREATE ROLE REGION_EMEA_ROLE;
CREATE ROLE ADMIN_ROLE;

-- 2️⃣ Create users (for simulation)
-- Note: In a real Snowflake environment, create users in the UI or with CREATE USER.
-- Simulated users here for demonstration
-- GRANT roles to simulated users
GRANT ROLE REGION_APAC_ROLE TO USER apac_user;
GRANT ROLE REGION_EMEA_ROLE TO USER emea_user;
GRANT ROLE ADMIN_ROLE TO USER admin_user;

-- 3️⃣ Grant privileges on the database
USE ROLE SECURITYADMIN;

GRANT USAGE ON DATABASE mydb TO ROLE REGION_APAC_ROLE;
GRANT USAGE ON SCHEMA mydb.public TO ROLE REGION_APAC_ROLE;
GRANT SELECT ON TABLE mydb.public.sales_data TO ROLE REGION_APAC_ROLE;

GRANT USAGE ON DATABASE mydb TO ROLE REGION_EMEA_ROLE;
GRANT USAGE ON SCHEMA mydb.public TO ROLE REGION_EMEA_ROLE;
GRANT SELECT ON TABLE mydb.public.sales_data TO ROLE REGION_EMEA_ROLE;

GRANT USAGE ON DATABASE mydb TO ROLE ADMIN_ROLE;
GRANT USAGE ON SCHEMA mydb.public TO ROLE ADMIN_ROLE;
GRANT SELECT ON TABLE mydb.public.sales_data TO ROLE ADMIN_ROLE;

Step 2: Create Simulated Table and Insert Data​

USE ROLE SYSADMIN;
USE DATABASE mydb;
USE SCHEMA public;

-- 1️⃣ Create the table
CREATE OR REPLACE TABLE sales_data (
id INT,
customer_name STRING,
region STRING,
amount NUMBER
);

-- 2️⃣ Insert some data
INSERT INTO sales_data (id, customer_name, region, amount) VALUES
(1, 'Alice', 'APAC', 1000),
(2, 'Bob', 'EMEA', 1500),
(3, 'Charlie', 'APAC', 2000),
(4, 'Diana', 'EMEA', 1800),
(5, 'Eve', 'APAC', 1200);

Step 3: Create the Row Access Policy​

USE ROLE SECURITYADMIN;

CREATE OR REPLACE ROW ACCESS POLICY rls_policy_region
AS (region STRING) RETURNS BOOLEAN ->
(
CURRENT_ROLE() = 'ADMIN_ROLE'
OR CURRENT_ROLE() = 'REGION_' || UPPER(region) || '_ROLE'
);
  • βœ… This policy allows:

    • Users with ADMIN_ROLE to see all rows.
    • Users with a REGION-specific role (e.g., REGION_APAC_ROLE) to see only rows for their region.

Step 4: Apply the Policy to the Table​

USE ROLE SECURITYADMIN;

ALTER TABLE sales_data
ADD ROW ACCESS POLICY rls_policy_region ON (region);

Step 5: Test the Policy with Simulated Roles​

πŸ”Ž As APAC User

USE ROLE REGION_APAC_ROLE;
SELECT * FROM sales_data;

Expected Output:

idcustomer_nameregionamount
1AliceAPAC1000
3CharlieAPAC2000
5EveAPAC1200

πŸ”Ž As EMEA User

USE ROLE REGION_EMEA_ROLE;
SELECT * FROM sales_data;

Expected Output:

idcustomer_nameregionamount
2BobEMEA1500
4DianaEMEA1800

πŸ”Ž As Admin User

USE ROLE ADMIN_ROLE;
SELECT * FROM sales_data;

Expected Output:

idcustomer_nameregionamount
1AliceAPAC1000
2BobEMEA1500
3CharlieAPAC2000
4DianaEMEA1800
5EveAPAC1200

⚑ Key Best Practices from This Example​

  • βœ… Dynamic Role Mapping Instead of hard-coding region names, dynamically concatenate REGION_ + region for easy scalability.

  • βœ… ADMIN Override Always include an ADMIN_ROLE (or similar) to access all data for auditing.

  • βœ… Testing Test the policy with all roles to ensure it works as expected.

  • βœ… Keep Policies Simple The policy logic is straightforward: match the region or admin override.

  • βœ… Use Masking for Column-level Security If needed, add masking policies for sensitive columns (e.g., customer_name).


πŸš€ Summary​

βœ”οΈ We created a multi-tenant table (sales_data). βœ”οΈ Implemented an RLS policy to filter rows by region dynamically. βœ”οΈ Users with region-specific roles see only their region’s data. βœ”οΈ Admin sees everything!