Snowflake Row-Level and Column-Level Security

Standard role-based access control grants or denies access to entire tables. But many real-world situations require finer control: a sales manager should see only their own region's orders, a doctor should see only their own patients, or a data analyst should see transaction amounts but not the payment card numbers. Snowflake provides two policy-based mechanisms to enforce these requirements: Row Access Policies for filtering rows and Dynamic Data Masking for obscuring column values. Both policies apply automatically at query time, transparently to the end user, without any changes to the underlying data.

The Problem These Features Solve

SCENARIO: GLOBAL SALES TABLE (10 million rows)

Without row-level security:
  EMEA Manager queries orders:
    SELECT * FROM ORDERS;
    --> Returns all 10 million rows (US, EMEA, APAC combined)
    --> Manager sees other regions' sensitive pricing

Without column-level masking:
  Analyst queries customers:
    SELECT customer_name, credit_card_number, email FROM CUSTOMERS;
    --> Returns real credit card numbers
    --> Analyst does not need this for their analysis

WITH ROW ACCESS POLICY + DATA MASKING:
  EMEA Manager queries orders:
    SELECT * FROM ORDERS;
    --> Snowflake silently adds: WHERE region = 'EMEA'
    --> Returns only 3.2 million EMEA rows — manager unaware of filter

  Analyst queries customers:
    SELECT customer_name, credit_card_number, email FROM CUSTOMERS;
    --> credit_card_number shows as ****-****-****-4521 (masked)
    --> No error, no blocked query — just protected data

Row Access Policies

A Row Access Policy is a SQL function that Snowflake evaluates for every row in a queried table. It returns TRUE (show this row) or FALSE (hide this row) based on the current session's context — typically the user's active role, their username, or values in a mapping table that defines what each role or user is allowed to see.

Simple Row Access Policy: Filter by Role

-- Step 1: Create the row access policy
-- This policy shows all rows to SYSADMIN, shows only the relevant region to others
CREATE ROW ACCESS POLICY rap_region_filter
AS (region VARCHAR) RETURNS BOOLEAN ->
  CASE
    WHEN CURRENT_ROLE() IN ('SYSADMIN', 'ACCOUNTADMIN')
      THEN TRUE    -- admins see all rows
    WHEN CURRENT_ROLE() = 'EMEA_ANALYST'
      THEN region = 'EMEA'
    WHEN CURRENT_ROLE() = 'APAC_ANALYST'
      THEN region = 'APAC'
    WHEN CURRENT_ROLE() = 'US_ANALYST'
      THEN region = 'US'
    ELSE FALSE     -- any other role sees no rows at all
  END;

-- Step 2: Attach the policy to the table's region column
ALTER TABLE ORDERS
  ADD ROW ACCESS POLICY rap_region_filter ON (region);

-- Now when anyone queries ORDERS:
--   EMEA_ANALYST sees: WHERE region = 'EMEA' applied silently
--   SYSADMIN sees: all rows
--   PUBLIC role sees: 0 rows

Advanced Row Access Policy: Using a Mapping Table

A mapping table approach is more flexible than hardcoding roles in the policy. You maintain the mapping in a regular Snowflake table, and the policy reads from it dynamically. Adding a new manager or changing a user's territory only requires updating the mapping table row — no policy change needed.

-- Step 1: Create the permission mapping table
CREATE TABLE SECURITY.REGION_ACCESS_MAP (
    username    VARCHAR(100),
    region      VARCHAR(50)
);

-- Populate with user-region assignments
INSERT INTO SECURITY.REGION_ACCESS_MAP VALUES
  ('alice.chen',  'EMEA'),
  ('alice.chen',  'US'),      -- Alice can see both EMEA and US
  ('bob.kumar',   'APAC'),
  ('carol.jones', 'US');

-- Step 2: Create policy that looks up the mapping table
CREATE ROW ACCESS POLICY rap_user_region_map
AS (region VARCHAR) RETURNS BOOLEAN ->
  CASE
    WHEN CURRENT_ROLE() IN ('SYSADMIN', 'ACCOUNTADMIN')
      THEN TRUE
    ELSE EXISTS (
      SELECT 1
      FROM SECURITY.REGION_ACCESS_MAP m
      WHERE m.username = CURRENT_USER()
        AND m.region   = region
    )
  END;

-- Step 3: Attach policy
ALTER TABLE ORDERS
  ADD ROW ACCESS POLICY rap_user_region_map ON (region);

-- Now: add/remove territory access by editing REGION_ACCESS_MAP only
-- Policy automatically reflects the change for all future queries

Managing Row Access Policies

-- View all row access policies in the account
SHOW ROW ACCESS POLICIES;

-- Describe a policy (shows its SQL definition)
DESC ROW ACCESS POLICY rap_region_filter;

-- Show which tables a policy is attached to
SELECT *
FROM SNOWFLAKE.ACCOUNT_USAGE.POLICY_REFERENCES
WHERE POLICY_NAME = 'RAP_REGION_FILTER';

-- Remove a policy from a table
ALTER TABLE ORDERS
  DROP ROW ACCESS POLICY rap_region_filter;

-- Replace one policy with another on a table
ALTER TABLE ORDERS
  DROP ROW ACCESS POLICY rap_region_filter,
  ADD ROW ACCESS POLICY  rap_user_region_map ON (region);

-- Drop a policy entirely (must be detached from all tables first)
DROP ROW ACCESS POLICY rap_region_filter;

Dynamic Data Masking

A masking policy defines how Snowflake transforms column values before displaying them to a user. The transformation depends on the querying user's role. Full administrators see the real value. Lower-privilege users see a masked version — partially obscured, fully replaced with a constant, or hashed to a non-reversible token.

Creating Masking Policies

-- Policy 1: Full mask for credit card numbers
-- Admins see: 4111-1234-5678-9012
-- Analysts see: ****-****-****-9012 (last 4 only)
-- Others see:   ****-****-****-****
CREATE MASKING POLICY mask_credit_card
AS (val VARCHAR) RETURNS VARCHAR ->
  CASE
    WHEN CURRENT_ROLE() IN ('SYSADMIN', 'ACCOUNTADMIN')
      THEN val                                -- real value
    WHEN CURRENT_ROLE() = 'SENIOR_ANALYST'
      THEN CONCAT('****-****-****-', RIGHT(val, 4))  -- last 4 digits only
    ELSE '****-****-****-****'               -- fully masked
  END;

-- Policy 2: Email masking (show domain but hide local part)
-- alice.chen@company.com --> a****@company.com
CREATE MASKING POLICY mask_email
AS (val VARCHAR) RETURNS VARCHAR ->
  CASE
    WHEN CURRENT_ROLE() IN ('SYSADMIN', 'ACCOUNTADMIN')
      THEN val
    ELSE CONCAT(LEFT(val, 1), '****@',
                SPLIT_PART(val, '@', 2))
  END;

-- Policy 3: Salary masking (show range instead of exact value)
-- 85000 --> 80001-90000
CREATE MASKING POLICY mask_salary
AS (val NUMBER) RETURNS VARCHAR ->
  CASE
    WHEN CURRENT_ROLE() IN ('SYSADMIN', 'HR_ADMIN')
      THEN val::VARCHAR
    WHEN CURRENT_ROLE() = 'HR_ANALYST'
      THEN CONCAT(
        (FLOOR(val / 10000) * 10000 + 1)::VARCHAR, '-',
        (CEIL(val  / 10000) * 10000)::VARCHAR
      )
    ELSE 'CONFIDENTIAL'
  END;

-- Policy 4: SHA-256 hash for pseudonymisation (reproducible but non-reversible)
CREATE MASKING POLICY mask_pii_hash
AS (val VARCHAR) RETURNS VARCHAR ->
  CASE
    WHEN CURRENT_ROLE() IN ('SYSADMIN', 'ACCOUNTADMIN')
      THEN val
    ELSE SHA2(val, 256)    -- same input always produces same hash (join-compatible)
  END;

Attaching Masking Policies to Columns

-- Attach a masking policy to a specific column
ALTER TABLE CUSTOMERS
  MODIFY COLUMN credit_card_number
  SET MASKING POLICY mask_credit_card;

ALTER TABLE CUSTOMERS
  MODIFY COLUMN email
  SET MASKING POLICY mask_email;

ALTER TABLE EMPLOYEES
  MODIFY COLUMN base_salary
  SET MASKING POLICY mask_salary;

-- Verify masking policy is active on a table
DESCRIBE TABLE CUSTOMERS;
-- Output shows the masking policy name next to each protected column

-- Remove a masking policy from a column
ALTER TABLE CUSTOMERS
  MODIFY COLUMN credit_card_number
  UNSET MASKING POLICY;

How Masking Looks at Query Time

TABLE: CUSTOMERS (actual stored data)
  customer_id | name          | credit_card_number  | email
  ------------|---------------|---------------------|-------------------
  1001        | Alice Chen    | 4111-1234-5678-9012 | alice@company.com
  1002        | Bob Kumar     | 5500-4321-8765-1098 | bob@company.com

QUERY: SELECT customer_id, name, credit_card_number, email FROM CUSTOMERS;

Result seen by SYSADMIN:
  1001 | Alice Chen | 4111-1234-5678-9012 | alice@company.com
  1002 | Bob Kumar  | 5500-4321-8765-1098 | bob@company.com

Result seen by SENIOR_ANALYST:
  1001 | Alice Chen | ****-****-****-9012 | a****@company.com
  1002 | Bob Kumar  | ****-****-****-1098 | b****@company.com

Result seen by ANALYST:
  1001 | Alice Chen | ****-****-****-**** | a****@company.com
  1002 | Bob Kumar  | ****-****-****-**** | b****@company.com

No changes to underlying data — only the displayed value differs per role.

Column-Level Security with Object Tags

Snowflake Tags let you categorise sensitive columns and then apply masking policies to all tagged columns at once. This is more scalable than manually attaching policies to each column across many tables.

-- Create tags to mark sensitive data categories
CREATE TAG pii_type
  ALLOWED_VALUES 'EMAIL', 'PHONE', 'SSN', 'CREDIT_CARD', 'NAME';

CREATE TAG data_sensitivity
  ALLOWED_VALUES 'PUBLIC', 'INTERNAL', 'CONFIDENTIAL', 'RESTRICTED';

-- Apply tags to columns
ALTER TABLE CUSTOMERS MODIFY COLUMN email
  SET TAG pii_type = 'EMAIL', data_sensitivity = 'CONFIDENTIAL';

ALTER TABLE CUSTOMERS MODIFY COLUMN credit_card_number
  SET TAG pii_type = 'CREDIT_CARD', data_sensitivity = 'RESTRICTED';

-- Attach a masking policy to a tag (policy applies to all tagged columns)
ALTER TAG pii_type
  SET MASKING POLICY mask_email USING (pii_type) FOR (VARCHAR);

-- Find all columns with a specific tag (for data cataloguing)
SELECT *
FROM TABLE(SNOWFLAKE.ACCOUNT_USAGE.TAG_REFERENCES(
  TAG_NAME   => 'SNOWFLAKE_COURSE.SECURITY.PII_TYPE',
  TAG_DOMAIN => 'COLUMN'
));

Combining Row Access Policies and Column Masking

Row access policies and masking policies work together on the same table independently. A user might see only their region's rows (row policy) and within those rows, see masked credit card numbers (column masking). Snowflake applies both policies transparently in a single query.

TABLE: ORDERS with both policies active

EMEA_ANALYST role queries: SELECT * FROM ORDERS;
  Row access policy:   Silently adds WHERE region = 'EMEA'
  Masking policy:      Replaces credit_card_number with ****-****-****-****
  Result:              Only EMEA rows, with masked payment details

SYSADMIN role queries: SELECT * FROM ORDERS;
  Row access policy:   Returns TRUE for all rows (no filter)
  Masking policy:      Returns TRUE values (no masking)
  Result:              All rows, all real values

Key Points

  • Row Access Policies filter rows at query time based on the querying role or user — the end user sees no error, just fewer rows
  • Use a mapping table with row access policies for flexible, database-driven permission management instead of hardcoding roles in policy SQL
  • Dynamic Data Masking transforms column values before displaying them — real data in storage is untouched
  • Four common masking patterns: full mask, partial mask (show last N digits), hash (pseudonymisation), and range replacement
  • Tags enable bulk policy application — tag a column as PII_TYPE=CREDIT_CARD and the masking policy attaches automatically to all matching columns
  • Row access and column masking policies apply simultaneously on the same table — they are independent and composable
  • Both features require Enterprise edition or above — they are not available on the Standard edition
Previous lessons
Back to courses
Next lessons

Leave a Comment

Your email address will not be published. Required fields are marked *