Snowflake - Row Level Security

Implementing Row-Level Security in Snowflake

Snowflake's row access policies offer a powerful way to implement row-level security (RLS) for your employee table with different access requirements based on employee type. This implementation will ensure that freelance records are visible to everyone, while full-time, part-time, and contract employee records are restricted to only finance and management roles.

Understanding Row-Level Security in Snowflake

Row-level security in Snowflake is implemented through row access policies, which are schema-level objects that determine whether specific rows in a table can be viewed based on conditions you define1. These policies contain expressions that evaluate to Boolean values—TRUE means "show this row" and FALSE means "don't show this row". This mechanism allows you to control data access at a granular level without modifying your application code or creating multiple views.

How Row Access Policies Work

A row access policy works by applying a filter condition at query runtime that determines which rows a user can access. The policy evaluates conditions (often based on user roles or attributes) to decide whether to display each row. These policies can leverage Snowflake context functions (like IS_ROLE_IN_SESSION()) or condition expression functions (like CASE statements).

Let us look at Example.

Scenario:

We have employee table in snowflake which has 100s of columns and millions of rows. Table has one column as emp_type which has values as fulltime, parttime, contract and freelance.

Requirement is to create a row level security.

Records where emp_type = Freelance would be visible for everyone but where emp_type in ( fulltime, parttime, contract ) would be visible only certain roles like finance and mgmt.

Solution:

Below is one approach using Snowflake’s native row access policies. Row access policies let you define filtering logic that’s applied automatically when users query the table. In your case, you want to allow all users to see rows where emp_type = 'freelance', while restricting rows with employee types like 'fulltime', 'parttime', or 'contract' to only those users with roles such as FINANCE or MGMT.

Below steps should be followed.

Implementation Steps

Step 1: Create the Necessary Roles (If Not Already Existing)

First, ensure you have the required roles created in your Snowflake environment:

-- Create roles if they don't already exist
USE ROLE SECURITYADMIN;

CREATE ROLE IF NOT EXISTS FINANCE;
CREATE ROLE IF NOT EXISTS MGMT;
CREATE ROLE IF NOT EXISTS GENERAL_USER;

-- Grant privileges to roles (customize as needed)
GRANT USAGE ON DATABASE your_database TO ROLE FINANCE;
GRANT USAGE ON DATABASE your_database TO ROLE MGMT;
GRANT USAGE ON DATABASE your_database TO ROLE GENERAL_USER;

-- Grant usage on schema
GRANT USAGE ON SCHEMA your_database.your_schema TO ROLE FINANCE;
GRANT USAGE ON SCHEMA your_database.your_schema TO ROLE MGMT;
GRANT USAGE ON SCHEMA your_database.your_schema TO ROLE GENERAL_USER;

-- Grant SELECT privilege on the employee table
GRANT SELECT ON TABLE your_database.your_schema.employee TO ROLE FINANCE;
GRANT SELECT ON TABLE your_database.your_schema.employee TO ROLE MGMT;
GRANT SELECT ON TABLE your_database.your_schema.employee TO ROLE GENERAL_USER;        

Step 2: Create the Row Access Policy

Next, create a row access policy that implements your security requirements:

-- Use a role with the necessary privileges to create a row access policy
USE ROLE SECURITYADMIN;

CREATE OR REPLACE ROW ACCESS POLICY emp_type_policy
    AS (emp_type VARCHAR) 
    RETURNS BOOLEAN ->
    CASE
        -- Finance and Management roles can see all employee types
        WHEN IS_ROLE_IN_SESSION('FINANCE') OR IS_ROLE_IN_SESSION('MGMT') THEN TRUE
        -- All other roles can only see Freelance employee types
        ELSE emp_type = 'Freelance'
    END;        

This policy creates a Boolean expression that:

• Returns TRUE (allowing access) for any row when the current session is using the FINANCE or MGMT role

For all other roles, only returns TRUE when the emp_type is 'Freelance'.

Step 3: Apply the Row Access Policy to Your Employee Table

After creating the policy, you need to apply it to your employee table.

-- Apply the policy to the employee table
ALTER TABLE your_database.your_schema.employee
ADD ROW ACCESS POLICY emp_type_policy ON (emp_type);        

This command links the policy to the employee table and specifies that the policy should use the emp_type column for its evaluations.

Step 4: Test the Row Access Policy

To verify that your policy works as expected, you should test it with different roles.

-- Test with a FINANCE role (should see all employee types)
USE ROLE FINANCE;
SELECT COUNT(*) AS total_rows, 
       COUNT(CASE WHEN emp_type = 'Freelance' THEN 1 END) AS freelance_count,
       COUNT(CASE WHEN emp_type = 'fulltime' THEN 1 END) AS fulltime_count,
       COUNT(CASE WHEN emp_type = 'parttime' THEN 1 END) AS parttime_count,
       COUNT(CASE WHEN emp_type = 'contract' THEN 1 END) AS contract_count
FROM your_database.your_schema.employee;

-- Test with a general user role (should only see 'Freelance' records)
USE ROLE GENERAL_USER;
SELECT COUNT(*) AS total_rows, 
       COUNT(CASE WHEN emp_type = 'Freelance' THEN 1 END) AS freelance_count
FROM your_database.your_schema.employee;        

Best Practices and Considerations

Using a Mapping Table for More Complex Scenarios

For more complex access requirements, you might consider using a mapping table approach. This is particularly useful if you need to manage access for many roles or if access patterns change frequently.

-- Create a mapping table

CREATE OR REPLACE TABLE your_database.your_schema.emp_type_access_mapping (
    emp_type VARCHAR,
    accessible_role VARCHAR
);

-- Populate the mapping table

INSERT INTO your_database.your_schema.emp_type_access_mapping
VALUES
('fulltime', 'FINANCE'),
('parttime', 'FINANCE'),
('contract', 'FINANCE'),
('Freelance', 'FINANCE'),
('fulltime', 'MGMT'),
('parttime', 'MGMT'),
('contract', 'MGMT'),
('Freelance', 'MGMT'),
('Freelance', 'GENERAL_USER');

-- Create a row access policy using the mapping table
CREATE OR REPLACE ROW ACCESS POLICY emp_type_mapping_policy
    AS (emp_type VARCHAR)
    RETURNS BOOLEAN ->
    EXISTS (
        SELECT 1
        FROM your_database.your_schema.emp_type_access_mapping
        WHERE emp_type_access_mapping.emp_type = emp_type
        AND emp_type_access_mapping.accessible_role = CURRENT_ROLE()    );

-- Apply the policy

ALTER TABLE your_database.your_schema.employee
ADD ROW ACCESS POLICY emp_type_mapping_policy ON (emp_type);        

This approach allows for more flexible access management by simply updating the mapping table rather than modifying the policy itself6.

Performance Considerations

When implementing row access policies on large tables (like your employee table with millions of rows), consider the following:

1. Policy Expression Complexity: Keep policy expressions as simple as possible to minimize performance impact.
2. Memoizable Functions: For better performance, consider using memoizable functions instead of subqueries in your policy expressions.
3. Centralized Mapping Tables: Store mapping tables in the same database as the protected table, especially if your policy uses the CURRENT_ACCOUNT() function.

Required Privileges

To implement row access policies, you need specific privileges:

1. To create a row access policy: CREATE ROW ACCESS POLICY privilege on the schema.
2. To apply a row access policy: Either the APPLY ROW ACCESS POLICY privilege on the schema or the OWNERSHIP privilege on the table and the APPLY privilege on the row access policy.
3. Additionally, USAGE privilege on the parent database and schema is required.

Conclusion

Implementing row-level security in Snowflake through row access policies provides a robust way to protect sensitive employee data. By following the steps outlined above, you can ensure that freelance employee records are accessible to all users, while full-time, part-time, and contract employee records are only visible to users with finance or management roles.

This approach leverages Snowflake's native security features to implement data access controls directly at the data layer, ensuring consistent enforcement across all applications and query interfaces. The solution is also scalable and can be adapted to more complex scenarios as your security requirements evolve.