3.1 Column-Level Masking & Row Access Policies

3.1 Column-Level Masking & Row Access Policies

In enterprise data architectures, securing sensitive information requires granular access controls that operate seamlessly without duplicating physical storage. Snowflake provides two foundational policy-driven security primitives: Dynamic Data Masking (DDM) for column-level security and Row Access Policies (RAP) for row-level security. Both mechanisms dynamically intercept queries at compilation time, injecting policy logic directly into the execution plan while leaving underlying micro-partitions completely unchanged.

---

Dynamic Data Masking Architecture

Dynamic Data Masking operates as a schema-level object that transforms plain-text column values into masked or obfuscated representations at query runtime based on the caller's session context.

-- Create a centralized security schema
CREATE SCHEMA IF NOT EXISTS governance_db.security_policies;

-- Define a Dynamic Data Masking policy for PII strings
CREATE OR REPLACE MASKING POLICY governance_db.security_policies.mask_pii_string
  AS (val STRING) RETURNS STRING ->
  CASE
    WHEN IS_ROLE_IN_SESSION('PRIVILEGED_ANALYST_ROLE') THEN val
    WHEN IS_ROLE_IN_SESSION('SUPPORT_ROLE') THEN CONCAT(LEFT(val, 2), '***', RIGHT(val, 2))
    ELSE '********'
  END;

Signature Rules and Return Types

When authoring masking policies, Snowflake enforces strict signature contract rules:

1. Data Type Equivalence: The return data type of the masking expression must match exactly the input data type of the target column. A policy accepting STRING must return STRING; a policy accepting NUMBER(10,2) must return NUMBER(10,2). Returning VARCHAR from a NUMBER masking policy causes a compilation error.
2. Type Coercion Limits: Snowflake does not perform implicit lossy conversions inside policy expressions. For date, timestamp, or numeric columns, masking values must be valid instances of that type (e.g., '1970-01-01'::DATE or -999999 for numeric columns).
3. Single Policy per Column: A column can have only one masking policy applied directly at any given time.

Context Functions: CURRENT_ROLE() vs IS_ROLE_IN_SESSION()

A critical distinction tested on the SnowPro Advanced: Architect exam is the behavior of session authorization functions:

Architect Tip: Always prefer IS_ROLE_IN_SESSION() over CURRENT_ROLE(). Using CURRENT_ROLE() = 'DATA_OFFICER' breaks when a user inherits DATA_OFFICER through an executive role or activates multiple roles simultaneously in modern BI tools.

Applying and Unsetting Masking Policies

To bind a policy to an existing table or view column, execute ALTER TABLE ... MODIFY COLUMN:

-- Bind masking policy to customer email
ALTER TABLE sales_db.public.customers 
  MODIFY COLUMN email SET MASKING POLICY governance_db.security_policies.mask_pii_string;

-- Detach masking policy
ALTER TABLE sales_db.public.customers 
  MODIFY COLUMN email UNSET MASKING POLICY;

To replace an existing policy on a column, use the FORCE keyword:

ALTER TABLE sales_db.public.customers 
  MODIFY COLUMN email SET MASKING POLICY governance_db.security_policies.mask_pii_v2 FORCE;

---

Conditional Masking Policies

Standard masking policies evaluate only the column being masked. However, enterprise compliance often requires conditional masking, where masking logic depends on the values of other columns within the same row (such as country code, classification level, or account tier).

Conditional Masking Syntax

In a conditional masking policy, the first argument represents the column being masked, while subsequent arguments represent additional context columns passed from the same table:

-- Create conditional policy: first argument is target column, subsequent arguments are conditional
CREATE OR REPLACE MASKING POLICY governance_db.security_policies.mask_salary_conditional
  AS (salary NUMBER(12,2), employee_country STRING, is_executive BOOLEAN) 
  RETURNS NUMBER(12,2) ->
  CASE
    WHEN IS_ROLE_IN_SESSION('PAYROLL_ADMIN') THEN salary
    WHEN is_executive = TRUE AND NOT IS_ROLE_IN_SESSION('C_SUITE_ROLE') THEN NULL
    WHEN employee_country = 'DE' AND NOT IS_ROLE_IN_SESSION('EU_DPO_ROLE') THEN -1.00
    ELSE salary
  END;

Binding Conditional Policies via USING

When applying a conditional masking policy, you must specify the conditional columns in the USING clause in the exact positional order declared in the policy signature:

ALTER TABLE hr_db.corp.employees 
  MODIFY COLUMN base_salary 
  SET MASKING POLICY governance_db.security_policies.mask_salary_conditional 
  USING (base_salary, country_code, is_exec);

Exam Trap: The first argument in the USING clause must be the column being modified. The additional arguments can be any columns from the same table, provided their data types match the policy signature.

---

Row-Level Security: Row Access Policies (RAP)

Row Access Policies determine which rows are visible in the result set when querying a table or view. Like Dynamic Data Masking, Row Access Policies are schema-level objects evaluated dynamically at query compilation.

Row Access Policy Mechanics

A Row Access Policy takes one or more column arguments and returns a boolean value (TRUE allows the row to be returned; FALSE filters the row out):

CREATE OR REPLACE ROW ACCESS POLICY governance_db.security_policies.tenant_isolation_rap
  AS (tenant_id STRING) RETURNS BOOLEAN ->
  IS_ROLE_IN_SESSION('ENTERPRISE_ADMIN') 
  OR tenant_id = CURRENT_ROLE();

Apply the policy to a table using ADD ROW ACCESS POLICY:

ALTER TABLE saas_db.app.transactions 
  ADD ROW ACCESS POLICY governance_db.security_policies.tenant_isolation_rap ON (tenant_id);

Entitlement Mapping Tables

Hardcoding role names into policy expressions is unmaintainable in enterprise environments with thousands of users, departments, or geographical boundaries. The architect-grade design pattern uses Entitlement Mapping Tables (also known as security lookup tables):

-- Entitlement mapping table
CREATE TABLE governance_db.security_policies.region_entitlements (
  role_name STRING NOT NULL,
  allowed_region STRING NOT NULL
);

-- Row Access Policy referencing the mapping table
CREATE OR REPLACE ROW ACCESS POLICY governance_db.security_policies.regional_sales_rap
  AS (sales_region STRING) RETURNS BOOLEAN ->
  IS_ROLE_IN_SESSION('SALES_GLOBAL_VP')
  OR EXISTS (
    SELECT 1 
    FROM governance_db.security_policies.region_entitlements e
    WHERE e.allowed_region = sales_region
      AND IS_ROLE_IN_SESSION(e.role_name)
  );

Mapping Table Optimization Guidelines

When implementing mapping tables in high-concurrency production environments:

1. Keep Mapping Tables Compact: Mapping tables should ideally fit within a few micro-partitions so they are readily cached in virtual warehouse memory.
2. Avoid Volatile Functions: Do not call non-deterministic functions (like RANDOM() or dynamic external calls) inside the RAP subquery.
3. Grant Select Privileges: Ensure the role creating or owning the policy has SELECT privileges on the mapping table. When a user queries the protected table, policy subqueries run under the context of the policy, but object authorization must remain intact.

---

Policy Interactions and Execution Order

A common architectural challenge is understanding how Snowflake behaves when both a Row Access Policy and one or more Dynamic Data Masking policies are applied to the same table.

Query Submitted
      │
      ▼
1. Query Compilation & Parsing
      │
      ▼
2. Row Access Policy (RAP) Evaluation ──► Filters rows out of the query scope
      │
      ▼
3. Dynamic Data Masking (DDM) Evaluation ──► Obfuscates column values on surviving rows
      │
      ▼
4. Micro-Partition Pruning & Execution Engine
      │
      ▼
Caller Receives Filtered & Masked Result Set

Why Execution Order Matters

Snowflake's query rewrite engine guarantees that Row Access Policies are evaluated FIRST, and Masking Policies are evaluated SECOND:

• Security Boundary Integrity: If masking occurred before row filtering, conditional row access logic might attempt to filter on values that were already masked into '********', resulting in invalid row elimination or runtime errors.
• Side-Channel Prevention: Evaluating row filters first ensures that masking transformations and expensive cryptographic hashing functions are never executed on records the user is not entitled to see.
• Performance Efficiency: Pruning unauthorized rows early minimizes the number of records processed by column-level masking expressions in downstream execution stages.

---

Architectural Limitations and Exam Traps

Understanding policy limitations is essential for passing the SnowPro Advanced: Architect exam:

1. External Tables and Virtual Columns

• Dynamic Data Masking cannot be applied to virtual columns in external tables.
• Masking policies can be applied to the VALUE column of an external table, or standard columns on regular internal tables, but virtual columns derived from parsed JSON payloads (METADATA$EXTERNAL_TABLE_PARTITION or expressions like VALUE:cust_id::STRING) are unsupported.
• Workaround: Create a secure view over the external table and apply the masking policy or masking logic inside the secure view definition.

2. Join Elimination and Query Optimizer Impact

• Row Access Policies that reference mapping tables via subqueries introduce implicit joins into every query against the target table.
• The Snowflake query optimizer relies on constraint-based join elimination to prune unused tables. When a RAP introduces an active subquery against an entitlement table, the optimizer cannot eliminate joins against that table, which can suppress partition pruning and degrade query latency on large fact tables.
• Mitigation: Cluster entitlement tables effectively, minimize the number of columns in the mapping table, and consider using session variables or role-name string patterns if subqueries cause query bottlenecks.

3. Policies That Reference Masked Columns (EXEMPT_OTHER_POLICIES)

• A column can have only one masking policy set directly at a time.
• By default, a row access policy or a conditional masking policy cannot reference a column that is already protected by a masking policy.
• To allow it, create or alter that column's masking policy with EXEMPT_OTHER_POLICIES = TRUE. The other policy then evaluates the column's real value, and the masking policy is ignored for that reference.
• For external tables, EXEMPT_OTHER_POLICIES = TRUE on the policy protecting the VALUE column also lets a policy set on a virtual column override the one it inherits from VALUE.

4. Cloning and Replication Behavior

• When cloning a table (CREATE TABLE cloned_tbl CLONE source_tbl), the policy assignments on its columns and rows are retained on the clone.
• If a database or schema containing policies is cloned, the cloned objects reference the policies in the source container unless both policy and table reside in the same cloned schema.

5. There Is No "Bypass Everything" Privilege

• Snowflake has no account privilege that silently exempts a role from all masking and row access policies. Exceptions are written inside each policy's logic (for example IS_ROLE_IN_SESSION('PAYROLL_ADMIN')), which keeps them auditable.
• Privileges such as APPLY MASKING POLICY and APPLY ROW ACCESS POLICY control who may attach or detach policies — a separation-of-duties control for a central governance team — not who may read unmasked data.

---

Policy Architecture Comparison