Snowflake Row Access Policies: Implementing Row-Level Security

A Row Access Policy is a Snowflake security feature that automatically applies row-level filters to tables and views based on the executing user's role or session attributes. The policy is attached to a table and transparently filters rows for every query that references that table. It is available on Enterprise Edition or higher.

CREATE ROW ACCESS POLICY: Basic Syntax

-- Basic Row Access Policy (role-based)
CREATE OR REPLACE ROW ACCESS POLICY rap_region_filter
  AS (region_val VARCHAR) RETURNS BOOLEAN →
  CASE
    WHEN IS_ROLE_IN_SESSION('ADMIN') THEN TRUE
    WHEN IS_ROLE_IN_SESSION('APAC_ANALYST')
      AND region_val = 'APAC' THEN TRUE
    WHEN IS_ROLE_IN_SESSION('EMEA_ANALYST')
      AND region_val = 'EMEA' THEN TRUE
    ELSE FALSE
  END;

-- Attach the policy to the table
ALTER TABLE sales.transactions
  ADD ROW ACCESS POLICY rap_region_filter ON (region);

-- Detach the policy
ALTER TABLE sales.transactions
  DROP ROW ACCESS POLICY rap_region_filter;

Policy arguments are mapped to table columns. With RETURNS BOOLEAN, only rows that return TRUE are included in the query results. Rows that return FALSE or NULL are excluded.

Role Evaluation Patterns with CURRENT_ROLE()

-- CURRENT_ROLE()-based evaluation (Primary Role only)
CREATE OR REPLACE ROW ACCESS POLICY rap_dept_filter
  AS (dept_val VARCHAR) RETURNS BOOLEAN →
  CURRENT_ROLE() IN ('SYSADMIN', 'HR_ADMIN')
  OR dept_val = (
    SELECT department
    FROM hr.user_department_mapping
    WHERE user_name = CURRENT_USER()
  );

-- IS_ROLE_IN_SESSION()-based (supports Secondary Roles)
CREATE OR REPLACE ROW ACCESS POLICY rap_dept_filter_v2
  AS (dept_val VARCHAR) RETURNS BOOLEAN →
  IS_ROLE_IN_SESSION('SYSADMIN')
  OR IS_ROLE_IN_SESSION('HR_ADMIN')
  OR dept_val = (
    SELECT department
    FROM hr.user_department_mapping
    WHERE user_name = CURRENT_USER()
  );

The Mapping Table Pattern

In large organizations with many roles, hard-coding role names inside the policy is hard to maintain. A Mapping Table lets you manage access-control conditions as table data, so you can update access rules without modifying the policy itself.

-- Create the mapping table
CREATE TABLE security.region_access_mapping (
  role_name    VARCHAR,
  region       VARCHAR,
  access_level VARCHAR  -- 'FULL' or 'RESTRICTED'
);

-- Populate mapping data
INSERT INTO security.region_access_mapping VALUES
  ('APAC_ANALYST', 'APAC', 'FULL'),
  ('EMEA_ANALYST', 'EMEA', 'FULL'),
  ('GLOBAL_ANALYST', 'APAC', 'FULL'),
  ('GLOBAL_ANALYST', 'EMEA', 'FULL'),
  ('GLOBAL_ANALYST', 'AMER', 'FULL');

-- Row Access Policy that references the Mapping Table
CREATE OR REPLACE ROW ACCESS POLICY rap_region_mapping
  AS (region_val VARCHAR) RETURNS BOOLEAN →
  IS_ROLE_IN_SESSION('ADMIN')
  OR EXISTS (
    SELECT 1
    FROM security.region_access_mapping m
    WHERE IS_ROLE_IN_SESSION(m.role_name)
      AND m.region = region_val
  );

-- Attach to the table
ALTER TABLE sales.transactions
  ADD ROW ACCESS POLICY rap_region_mapping ON (region);

Comparison with Masking Policy

Required Privileges

-- Privileges required to manage Row Access Policies
-- Create a policy
GRANT CREATE ROW ACCESS POLICY ON SCHEMA security
  TO ROLE policy_admin;

-- Attach a policy (apply to tables)
GRANT APPLY ROW ACCESS POLICY ON ACCOUNT
  TO ROLE policy_admin;

-- Or grant apply privilege only on specific tables
GRANT APPLY ROW ACCESS POLICY ON TABLE sales.transactions
  TO ROLE policy_admin;

-- The OWNERSHIP role on the policy can alter or drop it
-- The APPLY privilege role can attach or detach the policy
-- Splitting these two privileges achieves separation of duties

Policy Evaluation Order

When a Row Access Policy and a Masking Policy are applied to the same table, the Row Access Policy is evaluated first, and the Masking Policy is then applied to the rows that pass the filter. Snowflake controls this order internally, and users cannot change it.

Design Best Practices

• Exempt admin roles: Return TRUE for SYSADMIN or ACCOUNTADMIN in the first condition of the policy. If administrators lose data access, troubleshooting becomes very difficult.
• Use IS_ROLE_IN_SESSION(): CURRENT_ROLE() does not consider Secondary Roles, so using IS_ROLE_IN_SESSION() ensures correct evaluation even when Secondary Roles are active.
• Simulate during testing: There is no EXECUTE USING ... command, so USE ROLE with a test role and run the query to confirm that only the expected rows are returned.
• Protect the Mapping Table: Tightly control access privileges on the Mapping Table itself. If the Mapping Table is tampered with, access control can be bypassed.

Check Your Understanding

Frequently Asked Questions