Skip to main content

Row Access Policies & Column-Level Security

Not every user should see every row or every column. Snowflake gives you two mechanisms: Row Access Policies for filtering rows and Dynamic Data Masking for redacting column values. Both are transparent to end users — queries run the same way, but the data returned changes based on who's asking.


Row Access Policies: The Concept​

A Row Access Policy (RAP) is a schema-level object that determines which rows a user can see. The policy is a SQL expression returning a boolean: TRUE means visible, FALSE means filtered out.

Key characteristics:

  • Centrally managed — define once, apply to many tables
  • Transparent to the querying user
  • Works on tables and views
  • One row access policy per table
  • Evaluated at query time with current role mappings

Common use cases: multi-tenant isolation, regional data restrictions, department-level filtering.


Creating a Row Access Policy​

CREATE OR REPLACE ROW ACCESS POLICY rap_region_filter
AS (region_val VARCHAR) RETURNS BOOLEAN ->
CURRENT_ROLE() IN ('SYSADMIN', 'SECURITYADMIN')
OR region_val = CURRENT_SESSION()::VARCHAR
;

ALTER TABLE sales.orders
ADD ROW ACCESS POLICY rap_region_filter ON (region);

Mapping Tables for Role-Based Row Filtering​

Hardcoding roles doesn't scale. Use a mapping table instead:

CREATE TABLE governance.role_region_mapping (
role_name VARCHAR,
region VARCHAR
);

INSERT INTO governance.role_region_mapping VALUES
('ANALYST_NA', 'North America'),
('ANALYST_EU', 'Europe'),
('ANALYST_APAC', 'Asia Pacific'),
('SALES_MANAGER', 'North America'),
('SALES_MANAGER', 'Europe'),
('SALES_MANAGER', 'Asia Pacific');

Build a policy that references the mapping table:

CREATE OR REPLACE ROW ACCESS POLICY rap_region_mapping
AS (region_val VARCHAR) RETURNS BOOLEAN ->
CURRENT_ROLE() IN ('SYSADMIN', 'SECURITYADMIN')
OR EXISTS (
SELECT 1 FROM governance.role_region_mapping
WHERE role_name = CURRENT_ROLE()
AND region = region_val
)
;

When access requirements change, update the mapping table — not the policy.


Column-Level Security: Dynamic Data Masking​

Masking policies transform column values at query time based on the querying role. The underlying data stays intact.

CREATE OR REPLACE MASKING POLICY mask_email
AS (val VARCHAR) RETURNS VARCHAR ->
CASE
WHEN CURRENT_ROLE() IN ('SYSADMIN', 'DATA_ENGINEER') THEN val
WHEN CURRENT_ROLE() = 'ANALYST' THEN REGEXP_REPLACE(val, '.+@', '***@')
ELSE '***MASKED***'
END
;

ALTER TABLE customers.profiles
MODIFY COLUMN email SET MASKING POLICY mask_email;

Masking Policy Patterns​

-- SSN: show last 4 digits to privileged roles
CREATE OR REPLACE MASKING POLICY mask_ssn
AS (val VARCHAR) RETURNS VARCHAR ->
CASE
WHEN CURRENT_ROLE() IN ('HR_ADMIN') THEN val
WHEN CURRENT_ROLE() IN ('HR_ANALYST') THEN '***-**-' || RIGHT(val, 4)
ELSE '***-**-****'
END
;

-- Numeric: NULL out salary for unauthorized roles
CREATE OR REPLACE MASKING POLICY mask_salary
AS (val NUMBER) RETURNS NUMBER ->
CASE
WHEN CURRENT_ROLE() IN ('HR_ADMIN', 'FINANCE_ADMIN') THEN val
ELSE NULL
END
;

The return type must match the column's data type.


Conditional Masking with Context Functions​

FunctionReturns
CURRENT_ROLE()Active primary role
IS_ROLE_IN_SESSION()Whether a role is active (including inherited)
CURRENT_USER()Authenticated username
CURRENT_IP_ADDRESS()Client IP address
INVOKER_SHARE()Share name if query comes via data sharing

IS_ROLE_IN_SESSION() respects role hierarchy:

CREATE OR REPLACE MASKING POLICY mask_pii_hierarchical
AS (val VARCHAR) RETURNS VARCHAR ->
CASE
WHEN IS_ROLE_IN_SESSION('PII_READER') THEN val
ELSE '***REDACTED***'
END
;

Any role that inherits PII_READER gets unmasked data — no need to enumerate every role.


Combining Row Access and Masking Policies​

Both can be applied to the same table. They compose naturally:

  1. Row access policy runs first — filters out rows.
  2. Masking policies run next — transform column values on visible rows.
ALTER TABLE hr.employees
ADD ROW ACCESS POLICY rap_region_mapping ON (region);

ALTER TABLE hr.employees
MODIFY COLUMN salary SET MASKING POLICY mask_salary;

ALTER TABLE hr.employees
MODIFY COLUMN ssn SET MASKING POLICY mask_ssn;

Policy Administration and Testing​

-- See where policies are attached
SELECT *
FROM SNOWFLAKE.ACCOUNT_USAGE.POLICY_REFERENCES
WHERE policy_kind IN ('ROW_ACCESS_POLICY', 'MASKING_POLICY')
ORDER BY ref_entity_name;

Test by switching roles:

USE ROLE ANALYST;
SELECT * FROM hr.employees LIMIT 10;

USE ROLE SYSADMIN;
SELECT * FROM hr.employees LIMIT 10;

Common pitfalls:

PitfallFix
Mapping table inaccessible to querying roleGrant SELECT on mapping tables to all relevant roles
Masking return type mismatchEnsure return type matches column data type
No admin bypass in policyAlways include SYSADMIN/SECURITYADMIN exception
Circular policy dependenciesNever reference the protected table inside its own policy

Keep policy ownership under a dedicated GOVERNANCE_ADMIN role, separate from the roles being governed.


Key Takeaways​

  • Row Access Policies filter rows transparently — use mapping tables for scalable filtering.
  • Dynamic Data Masking transforms column values — the underlying data is never modified.
  • Use IS_ROLE_IN_SESSION() to respect role hierarchy in policies.
  • Both policy types compose: row filtering first, then column masking.
  • Always test policies by switching roles before deploying to production.

Next, we'll explore data classification and tag-based masking for scalable governance.