Masking Policy vs Row Access Policy: 8-Point Comparison

Snowflake's two core data-protection features, Masking Policy and Row Access Policy, are both policy-based access controls available on Enterprise Edition and above, but they target fundamentally different things. Masking Policy transforms the value of a column, while Row Access Policy filters the visibility of rows. Using both correctly — and combining them when needed — is required knowledge for the SnowPro Security exam and for real-world deployments.

8-Point Comparison Table

Masking Policy Implementation Example

-- Mask email addresses
CREATE OR REPLACE MASKING POLICY mask_email
  AS (val VARCHAR) RETURNS VARCHAR →
  CASE
    WHEN IS_ROLE_IN_SESSION('HR_ADMIN') THEN val
    WHEN IS_ROLE_IN_SESSION('MANAGER') THEN
      CONCAT(LEFT(val, 2), '****@', SPLIT_PART(val, '@', 2))
    ELSE '****@****.***'
  END;

-- Attach to a column
ALTER TABLE hr.employees
  ALTER COLUMN email
  SET MASKING POLICY mask_email;

-- Numeric salary masking
CREATE OR REPLACE MASKING POLICY mask_salary
  AS (val NUMBER) RETURNS NUMBER →
  CASE
    WHEN IS_ROLE_IN_SESSION('HR_ADMIN') THEN val
    WHEN IS_ROLE_IN_SESSION('MANAGER') THEN
      ROUND(val, -4)  -- Round to nearest 10,000
    ELSE NULL
  END;

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

Row Access Policy Implementation Example

-- Department-based row filter
CREATE OR REPLACE ROW ACCESS POLICY rap_department
  AS (dept_val VARCHAR) RETURNS BOOLEAN →
  IS_ROLE_IN_SESSION('HR_ADMIN')
  OR dept_val = (
    SELECT department FROM hr.user_dept_mapping
    WHERE user_name = CURRENT_USER()
  );

-- Attach to the table
ALTER TABLE hr.employees
  ADD ROW ACCESS POLICY rap_department ON (department);

Combined Application Pattern

A Masking Policy and a Row Access Policy can be applied to the same table simultaneously. In that case, they are evaluated in the order Row Access Policy → Masking Policy.

-- Scenario: apply both to the hr.employees table
-- Row Access Policy: only show employees in the user's own department
-- Masking Policy: mask the salary column depending on the role

-- Step 1: attach the Row Access Policy
ALTER TABLE hr.employees
  ADD ROW ACCESS POLICY rap_department ON (department);

-- Step 2: attach the Masking Policies
ALTER TABLE hr.employees
  ALTER COLUMN salary
  SET MASKING POLICY mask_salary;

ALTER TABLE hr.employees
  ALTER COLUMN email
  SET MASKING POLICY mask_email;

-- Result (for the MANAGER role):
-- 1. Row Access Policy narrows the result to the user's own department
-- 2. salary column is rounded to the nearest 10,000
-- 3. email column is returned as the first 2 characters plus a masked suffix

When to Use Secure Views Instead

Design Decision Guide

Requirement: restrict data for specific users or roles
│
├─ Restrict at the row level (e.g., show only employees in the user's department)
│   └─ → Row Access Policy
│
├─ Transform column values before returning (e.g., mask part of an SSN)
│   └─ → Masking Policy
│
├─ Completely hide a column
│   ├─ Drop the column from query results → Projection Policy
│   └─ Drop the column from a view       → Secure View
│
└─ Both row restriction + column masking
    └─ → Combine Row Access Policy + Masking Policy

Pitfalls and Caveats

• Masking Policy and the WHERE clause: A Masking Policy applies when query results are returned, but WHERE clause filters run against the pre-mask values. In other words, SELECT email WHERE email = '[email protected]' evaluates the WHERE condition against the original value even though the returned email is masked.
• Row Access Policy and COUNT: When you run COUNT(*) on a table with a Row Access Policy, it returns the post-filter row count. A user who knows the total row count through another channel could infer how many rows are hidden from them.
• Debugging policies: The owner role can inspect policy logic with DESCRIBE MASKING POLICY / DESCRIBE ROW ACCESS POLICY. Always validate the intended behavior with a test role before rolling out to production.
• Conditional Masking: By passing multiple columns into a Masking Policy, you can vary the masking based on other column values (for example, unmask only when region equals APAC).

Conditional Masking

-- Switch masking based on the value of another column
CREATE OR REPLACE MASKING POLICY mask_salary_by_region
  AS (salary_val NUMBER, region_val VARCHAR) RETURNS NUMBER →
  CASE
    WHEN IS_ROLE_IN_SESSION('HR_ADMIN') THEN salary_val
    WHEN IS_ROLE_IN_SESSION('APAC_MANAGER')
      AND region_val = 'APAC' THEN salary_val
    ELSE NULL
  END;

-- Attach by mapping the two columns
ALTER TABLE hr.employees
  ALTER COLUMN salary
  SET MASKING POLICY mask_salary_by_region
  USING (salary, region);

Check Your Understanding

Frequently Asked Questions