NashTech Blog

Get in touch

Implementing Row-Level and Column-Level Security in Snowflake

Implementing Row-Level Security (RLS) and Column-Level Security (CLS) in Snowflake is essential for securing sensitive data and ensuring users only access the data they are authorized to view. In this blog, we’ll cover both RLS and CLS and provide examples for implementing them using Snowflake's features.

Row-Level Security (RLS)

n Snowflake, RLS can be implemented using Secure Views that filter data based on the current user's role or session context. We’ll use SESSION_CONTEXT to access user attributes and enforce RLS.

Implementing RLS with Secure Views

Let’s say we have a table ORDERS where each record is tagged with a CUSTOMER_ID, and we want each user to see only the rows that correspond to their assigned CUSTOMER_ID.
Step 1: Create a Sample Table
				
					CREATE OR REPLACE TABLE ORDERS (
    ORDER_ID INT,
    CUSTOMER_ID INT,
    PRODUCT_NAME STRING,
    AMOUNT DECIMAL
);

-- Insert sample data
INSERT INTO ORDERS (ORDER_ID, CUSTOMER_ID, PRODUCT_NAME, AMOUNT) VALUES
    (1, 101, 'Product A', 100.00),
    (2, 102, 'Product B', 150.00),
    (3, 101, 'Product C', 200.00),
    (4, 103, 'Product D', 250.00);
Step 2: Define User Attributes
Each user is assigned a CUSTOMER_ID in their session context for security filtering. You can set the context dynamically during login or by user attributes. 
				
					-- Example of setting the customer ID for a session
ALTER SESSION SET CURRENT_CUSTOMER_ID = 101;
Step 3: Create a Secure View for RLS
A Secure View can filter data based on the session’s CURRENT_CUSTOMER_ID. 
				
					CREATE OR REPLACE SECURE VIEW RLS_ORDERS AS
SELECT *
FROM ORDERS
WHERE CUSTOMER_ID = CURRENT_CUSTOMER_ID();

Column-Level Security (CLS)

Snowflake’s masking policies allow you to define rules for masking sensitive columns based on roles. For instance, sensitive columns like SSN or Salary can be masked for all users except those with a specific role.

Implementing CLS with Masking Policies

Suppose we have a EMPLOYEES table with sensitive information such as SSN.
Step 1: Create the EMPLOYEES Table
				
					CREATE OR REPLACE TABLE EMPLOYEES (
    EMP_ID INT,
    NAME STRING,
    SSN STRING,
    SALARY DECIMAL
);

-- Insert sample data
INSERT INTO EMPLOYEES (EMP_ID, NAME, SSN, SALARY) VALUES
    (1, 'Alice', '123-45-6789', 80000),
    (2, 'Bob', '987-65-4321', 90000);
Step 2: Define a Masking Policy
				
					CREATE OR REPLACE MASKING POLICY ssn_masking_policy
AS (val STRING) 
RETURNS STRING ->
    CASE
        WHEN CURRENT_ROLE() IN ('HR_ROLE') THEN val
        ELSE 'XXX-XX-XXXX'
    END;
Step 3: Apply the Masking Policy to the Column
Apply the ssn_masking_policy on the SSN column in the EMPLOYEES table. 
				
					ALTER TABLE EMPLOYEES MODIFY COLUMN SSN SET MASKING POLICY ssn_masking_policy;
Now, when users query the EMPLOYEES table, they will see masked SSN values unless they have the HR_ROLE role.

Conclusion

Snowflake makes it easy to implement Row-Level and Column-Level Security using secure views, session contexts, and masking policies. This combination ensures data privacy and security while allowing authorized users the access they need.
Scroll to Top