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
| Function | Returns |
|---|---|
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:
- Row access policy runs first — filters out rows.
- 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:
| Pitfall | Fix |
|---|---|
| Mapping table inaccessible to querying role | Grant SELECT on mapping tables to all relevant roles |
| Masking return type mismatch | Ensure return type matches column data type |
| No admin bypass in policy | Always include SYSADMIN/SECURITYADMIN exception |
| Circular policy dependencies | Never 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.