I tried Snowflake's masking policy and row access policy

2025.08.27

This is Kawabata.

Masking policies implement column-level security, controlling "how cell data is displayed" according to user roles, while row access policies implement row-level security, controlling "which rows are shown to users."

I will verify how these features for establishing an organization's data governance structure can be practically used.

Target Audience

Those interested in masking policies and row access policies
Those interested in establishing an organizational data governance structure

Preparation and Test Environment

To conduct actual verification, I will create roles, databases, and test data.

Required Environment

Snowflake account: I am testing with a Snowflake Enterprise edition trial account.### Roles and Permissions
I created the following roles for verification.

Role name	Description
GOVERNANCE_ADMIN	A role with advanced privileges responsible for centrally defining and managing policies.
HR_MANAGER	A business role with authorized access to highly confidential data such as personal information.
SALES_MANAGER	A business role with access permitted only to data from specific regions.
ANALYST	A business role for general analysts with restricted data access.

					
			-- Switch role
USE ROLE SECURITYADMIN;

-- Create custom roles
CREATE ROLE IF NOT EXISTS GOVERNANCE_ADMIN;
CREATE ROLE IF NOT EXISTS HR_MANAGER;
CREATE ROLE IF NOT EXISTS SALES_MANAGER;
CREATE ROLE IF NOT EXISTS ANALYST;

-- Set up role hierarchy (allowing GOVERNANCE_ADMIN to manage other roles)
GRANT ROLE HR_MANAGER TO ROLE GOVERNANCE_ADMIN;
GRANT ROLE SALES_MANAGER TO ROLE GOVERNANCE_ADMIN;
GRANT ROLE ANALYST TO ROLE GOVERNANCE_ADMIN;

-- Grant GOVERNANCE_ADMIN role to the current user to facilitate testing
-- Replace KAWABATA with your own username
GRANT ROLE GOVERNANCE_ADMIN TO USER KAWABATA;

		
		

Next, we grant the necessary permissions to the GOVERNANCE_ADMIN role to create and apply policies to objects.

					
			-- Grant policy application permissions to GOVERNANCE_ADMIN
GRANT APPLY MASKING POLICY ON ACCOUNT TO ROLE GOVERNANCE_ADMIN;
GRANT APPLY ROW ACCESS POLICY ON ACCOUNT TO ROLE GOVERNANCE_ADMIN;

Create a database and schema for verification.

					
			-- Switch role
USE ROLE SYSADMIN;

-- Create a verification database
CREATE DATABASE IF NOT EXISTS GOVERNANCE_TEST;

-- Create schemas within the database
CREATE SCHEMA IF NOT EXISTS GOVERNANCE_TEST.RAW_DATA;
CREATE SCHEMA IF NOT EXISTS GOVERNANCE_TEST.POLICIES;

Grant policy creation permissions to GOVERNANCE_ADMIN in the POLICIES schema.

					
			-- Switch role
USE ROLE SECURITYADMIN;

-- Grant policy creation permissions to GOVERNANCE_ADMIN in the POLICIES schema
GRANT USAGE ON DATABASE GOVERNANCE_TEST TO ROLE GOVERNANCE_ADMIN;
GRANT USAGE ON SCHEMA GOVERNANCE_TEST.POLICIES TO ROLE GOVERNANCE_ADMIN;
GRANT CREATE MASKING POLICY ON SCHEMA GOVERNANCE_TEST.POLICIES TO ROLE GOVERNANCE_ADMIN;
GRANT CREATE ROW ACCESS POLICY ON SCHEMA GOVERNANCE_TEST.POLICIES TO ROLE GOVERNANCE_ADMIN;

-- Grant USAGE permissions on database and schema to other roles as well
GRANT USAGE ON DATABASE GOVERNANCE_TEST TO ROLE HR_MANAGER;
GRANT USAGE ON DATABASE GOVERNANCE_TEST TO ROLE SALES_MANAGER;
GRANT USAGE ON DATABASE GOVERNANCE_TEST TO ROLE ANALYST;

GRANT USAGE ON SCHEMA GOVERNANCE_TEST.RAW_DATA TO ROLE HR_MANAGER;
GRANT USAGE ON SCHEMA GOVERNANCE_TEST.RAW_DATA TO ROLE SALES_MANAGER;
GRANT USAGE ON SCHEMA GOVERNANCE_TEST.RAW_DATA TO ROLE ANALYST;
```### Data Used
Finally, we will store the dummy data created with generative AI in `GOVERNANCE_TEST.RAW_DATA`.
```sql
-- Switch role and context
USE DATABASE GOVERNANCE_TEST;
USE SCHEMA RAW_DATA;

-- Create EMPLOYEES table
CREATE OR REPLACE TABLE EMPLOYEES (
    EMPLOYEE_ID NUMBER,
    FULL_NAME VARCHAR,
    EMAIL VARCHAR,
    SSN VARCHAR,
    SALARY NUMBER(10, 2),
    VISIBILITY_LEVEL VARCHAR -- 'PUBLIC' or 'CONFIDENTIAL'
);

-- Create SALES_TRANSACTIONS table
CREATE OR REPLACE TABLE SALES_TRANSACTIONS (
    TRANSACTION_ID VARCHAR,
    CUSTOMER_ID VARCHAR,
    PRODUCT_ID VARCHAR,
    SALE_AMOUNT NUMBER(10, 2),
    SALE_DATE DATE,
    SALES_REGION VARCHAR -- 'NA', 'EU', 'APAC'
);

-- Insert data into EMPLOYEES table
INSERT INTO EMPLOYEES (EMPLOYEE_ID, FULL_NAME, EMAIL, SSN, SALARY, VISIBILITY_LEVEL) VALUES
(1, 'Alice Johnson', 'alice.j@example.com', '123-456-7890', 90000.00, 'CONFIDENTIAL'),
(2, 'Bob Williams', 'bob.w@example.com', '234-567-8901', 120000.00, 'CONFIDENTIAL'),
(3, 'Charlie Brown', 'charlie.b@example.com', '345-678-9012', 75000.00, 'PUBLIC'),
(4, 'Diana Miller', 'diana.m@example.com', '456-789-0123', 150000.00, 'CONFIDENTIAL');

-- Insert data into SALES_TRANSACTIONS table
INSERT INTO SALES_TRANSACTIONS (TRANSACTION_ID, CUSTOMER_ID, PRODUCT_ID, SALE_AMOUNT, SALE_DATE, SALES_REGION) VALUES
('TXN001', 'CUST101', 'PROD_A', 150.00, '2023-01-15', 'NA'),
('TXN002', 'CUST102', 'PROD_B', 200.50, '2023-01-16', 'EU'),
('TXN003', 'CUST103', 'PROD_A', 140.00, '2023-02-10', 'APAC'),
('TXN004', 'CUST101', 'PROD_C', 500.75, '2023-02-12', 'NA'),
('TXN005', 'CUST104', 'PROD_B', 220.00, '2023-03-05', 'EU'),
('TXN006', 'CUST105', 'PROD_D', 80.25, '2023-03-08', 'NA');

-- Grant SELECT permission on tables to each business role
GRANT SELECT ON TABLE EMPLOYEES TO ROLE HR_MANAGER;
GRANT SELECT ON TABLE EMPLOYEES TO ROLE ANALYST;
GRANT SELECT ON TABLE SALES_TRANSACTIONS TO ROLE SALES_MANAGER;
GRANT SELECT ON TABLE SALES_TRANSACTIONS TO ROLE ANALYST;
```## Dynamic Data Masking
In this chapter, we will progressively examine techniques starting with basic role-based masking, then conditional masking, and finally tag-based approaches.

Commonly used masking techniques are as follows:
|Masking Technique|Description|Use Case|
| ---- | ---- | ---- |
|Complete Masking|Completely replaces column values with a fixed string (e.g., '***MASKED***').|Sensitive information such as social security numbers, passwords, etc., where there is no need to see the actual value.|
|Partial Masking|Displays only part of the data while masking the rest (e.g., showing only the last 4 digits of a credit card number).|Credit card numbers, phone numbers, etc., where partial information is needed for verification.|
|Pseudonymization/Hashing|Using hash functions like `SHA2` to convert original values into unique pseudonymous values. Original values cannot be restored, but the same input value always produces the same output value, making it useful as a join key for analysis.|User IDs, email addresses, etc., when conducting analysis without identifying individuals.|
|Encryption|Using `ENCRYPT`/`DECRYPT` functions with a passphrase to encrypt data so that only specific users can decrypt it.|Highly confidential medical or financial information.|

### Basic Role-Based Masking
#### Creating Masking Policies
We'll create two masking policies in the `POLICIES` schema we created.
```sql
USE ROLE GOVERNANCE_ADMIN;
USE SCHEMA GOVERNANCE_TEST.POLICIES;

-- Masking policy for EMAIL column
CREATE OR REPLACE MASKING POLICY email_mask AS (val STRING) RETURNS STRING ->
  CASE
    WHEN CURRENT_ROLE() IN ('HR_MANAGER', 'GOVERNANCE_ADMIN') THEN val
    ELSE '***MASKED***'
  END;

-- Masking policy for SSN column
CREATE OR REPLACE MASKING POLICY ssn_mask AS (val STRING) RETURNS STRING ->
  CASE
    WHEN CURRENT_ROLE() IN ('HR_MANAGER', 'GOVERNANCE_ADMIN') THEN val
    ELSE 'XXX-XXX-XXXX'
  END;

		
		

Applying Policies

Apply the created policies to each column of the EMPLOYEES table.

					
			USE ROLE GOVERNANCE_ADMIN;
USE SCHEMA GOVERNANCE_TEST.RAW_DATA;

-- Apply policy to EMAIL column of EMPLOYEES table
ALTER TABLE IF EXISTS EMPLOYEES MODIFY COLUMN EMAIL SET MASKING POLICY POLICIES.email_mask;

-- Apply policy to SSN column of EMPLOYEES table
ALTER TABLE IF EXISTS EMPLOYEES MODIFY COLUMN SSN SET MASKING POLICY POLICIES.ssn_mask;
```#### Validating the Results
Let's execute the query and check how the output changes.
When checking with the `HR_MANAGER` role, we can see all data as follows:
```sql
-- Execute query as HR_MANAGER role (original values are displayed)
USE ROLE HR_MANAGER;
SELECT FULL_NAME, EMAIL, SSN FROM GOVERNANCE_TEST.RAW_DATA.EMPLOYEES;

		
		

2025-08-26_11h46_21

When checking with the ANALYST role, the data was masked as follows:

					
			-- Execute query as ANALYST role (masked values are displayed)
USE ROLE ANALYST;
SELECT FULL_NAME, EMAIL, SSN FROM GOVERNANCE_TEST.RAW_DATA.EMPLOYEES;

2025-08-26_11h47_16

We have confirmed that the display changes depending on the user's permissions!

Partial Masking

Let's also check if partial masking is possible in a similar way.

					
			USE ROLE GOVERNANCE_ADMIN;
USE SCHEMA GOVERNANCE_TEST.POLICIES;

-- Partial masking policy for EMAIL column
CREATE OR REPLACE MASKING POLICY email_mask_partial AS (val STRING) RETURNS STRING ->
  CASE
    WHEN CURRENT_ROLE() IN ('HR_MANAGER', 'GOVERNANCE_ADMIN') THEN val
    -- Replace part before '@' with '*****@'
    ELSE REGEXP_REPLACE(val, '.+\@', '*****@')
  END;

-- Apply the policy
ALTER TABLE GOVERNANCE_TEST.RAW_DATA.EMPLOYEES MODIFY COLUMN EMAIL SET MASKING POLICY email_mask_partial;

		
		

When checking with the ANALYST role, the data was partially masked as follows:
2025-08-27_10h20_37

Conditional Data Masking

Creating a Conditional Policy

Let's create a policy named salary_mask.
Here, we configure it to display the value only when the visibility_level column is PUBLIC, and display -1 otherwise.

					
			USE ROLE GOVERNANCE_ADMIN;
USE SCHEMA GOVERNANCE_TEST.POLICIES;

CREATE OR REPLACE MASKING POLICY salary_mask AS (val NUMBER, visibility_level VARCHAR) RETURNS NUMBER ->
  CASE
    WHEN CURRENT_ROLE() IN ('HR_MANAGER', 'GOVERNANCE_ADMIN') THEN val
    WHEN visibility_level = 'PUBLIC' THEN val
    ELSE -1 -- Since it's NUMBER type, the mask value should also be NUMBER type
  END;
```#### Applying Conditional Policy
```sql
USE ROLE GOVERNANCE_ADMIN;
USE SCHEMA GOVERNANCE_TEST.RAW_DATA;

ALTER TABLE IF EXISTS EMPLOYEES MODIFY COLUMN SALARY
  SET MASKING POLICY POLICIES.salary_mask
  USING (SALARY, VISIBILITY_LEVEL);

		
		

Verifying Results

When checking with the HR_MANAGER role, you can see everything as shown below.

					
			-- Execute query with HR_MANAGER role (all salaries are displayed)
USE ROLE HR_MANAGER;
SELECT FULL_NAME, SALARY, VISIBILITY_LEVEL FROM GOVERNANCE_TEST.RAW_DATA.EMPLOYEES;

2025-08-26_12h02_03

When checking with the ANALYST role, the data was masked as follows.

					
			-- Execute query with ANALYST role (salary is displayed only for rows where VISIBILITY_LEVEL is 'PUBLIC')
USE ROLE ANALYST;
SELECT FULL_NAME, SALARY, VISIBILITY_LEVEL FROM GOVERNANCE_TEST.RAW_DATA.EMPLOYEES;

2025-08-26_12h02_53

We confirmed that the display varies according to the conditions granted!

Tag-Based Masking

In the previous verification, we applied the policy using the imperative command ALTER TABLE... SET MASKING POLICY. While this is direct and easy to understand, as the number of objects increases, management becomes complicated and risks such as overlooking applications increase.
Tag-based masking is like declaring "this column is PII (Personally Identifiable Information)".

Creating Tags

					
			USE ROLE SECURITYADMIN;

-- Grant permission to GOVERNANCE_ADMIN role to create tags in the POLICIES schema
GRANT CREATE TAG ON SCHEMA GOVERNANCE_TEST.POLICIES TO ROLE GOVERNANCE_ADMIN;
USE ROLE GOVERNANCE_ADMIN;
USE SCHEMA GOVERNANCE_TEST.POLICIES;

CREATE OR REPLACE TAG pii_email_tag COMMENT = 'Tag for PII email data';
CREATE OR REPLACE TAG pii_ssn_tag COMMENT = 'Tag for PII SSN data';

Associating Policies with Tags

Link the previously created policies to tags.

					
			-- Associate email_mask policy with pii_email_tag
ALTER TAG POLICIES.pii_email_tag SET MASKING POLICY POLICIES.email_mask;

-- Associate ssn_mask policy with pii_ssn_tag
ALTER TAG POLICIES.pii_ssn_tag SET MASKING POLICY POLICIES.ssn_mask;
```#### Applying Tags to Columns
We'll apply the newly created tags to each column in the EMPLOYEES table.
```sql
USE ROLE GOVERNANCE_ADMIN;
USE SCHEMA GOVERNANCE_TEST.RAW_DATA;

-- First remove any existing policies
ALTER TABLE IF EXISTS EMPLOYEES MODIFY COLUMN EMAIL UNSET MASKING POLICY;
ALTER TABLE IF EXISTS EMPLOYEES MODIFY COLUMN SSN UNSET MASKING POLICY;

-- Apply pii_email_tag to the EMAIL column
ALTER TABLE EMPLOYEES MODIFY COLUMN EMAIL SET TAG POLICIES.pii_email_tag = 'sensitive_email';

-- Apply pii_ssn_tag to the SSN column
ALTER TABLE EMPLOYEES MODIFY COLUMN SSN SET TAG POLICIES.pii_ssn_tag = 'sensitive_ssn';

		
		

Let's verify that the policies are applied via tags by using the INFORMATION_SCHEMA.POLICY_REFERENCES table function.

					
			SELECT *
FROM TABLE(GOVERNANCE_TEST.INFORMATION_SCHEMA.POLICY_REFERENCES(
  REF_ENTITY_NAME => 'GOVERNANCE_TEST.RAW_DATA.EMPLOYEES',
  REF_ENTITY_DOMAIN => 'TABLE'
));
-- This query result will confirm that POLICY_NAME, TAG_NAME, and REF_COLUMN_NAME are correctly set

		
		

2025-08-26_17h37_18

We've confirmed it programmatically.
We also checked with the ANALYST role and got the following result:
2025-08-26_17h41_49

We've confirmed that the tagged parts are properly masked!

Row Access Policy

Row access policies are schema-level objects that function like dynamic WHERE clauses applied to all queries against the protected tables.

Let's try it out.

Creating a Mapping Table

We'll create a mapping table in the POLICIES schema that defines which roles can access which regions.

					
			USE ROLE GOVERNANCE_ADMIN;
USE SCHEMA GOVERNANCE_TEST.POLICIES;

-- Grant table creation permission to GOVERNANCE_ADMIN role in the POLICIES schema
GRANT CREATE TABLE ON SCHEMA GOVERNANCE_TEST.POLICIES TO ROLE GOVERNANCE_ADMIN;

CREATE OR REPLACE TABLE REGION_ACCESS_MAP (
    ALLOWED_ROLE VARCHAR,
    SALES_REGION VARCHAR
);

-- Insert mapping data
INSERT INTO REGION_ACCESS_MAP (ALLOWED_ROLE, SALES_REGION) VALUES
('SALES_MANAGER', 'NA'),
('SALES_MANAGER', 'EU');

-- Grant SELECT permission on the mapping table to SALES_MANAGER role
GRANT SELECT ON TABLE REGION_ACCESS_MAP TO ROLE SALES_MANAGER;
```#### Creating Row Access Policy

```sql
USE ROLE GOVERNANCE_ADMIN;
USE SCHEMA GOVERNANCE_TEST.POLICIES;

CREATE OR REPLACE ROW ACCESS POLICY region_filter_policy
AS (region VARCHAR) RETURNS BOOLEAN ->
  CASE
    WHEN CURRENT_ROLE() IN ('GOVERNANCE_ADMIN') THEN TRUE
    WHEN CURRENT_ROLE() IN ('SALES_MANAGER') THEN
      EXISTS (
        SELECT 1 FROM GOVERNANCE_TEST.POLICIES.REGION_ACCESS_MAP
        WHERE ALLOWED_ROLE = CURRENT_ROLE()
          AND SALES_REGION = region
      )
    ELSE FALSE
  END;

		
		

Applying the Policy

					
			-- Applying the policy
USE ROLE GOVERNANCE_ADMIN;
USE SCHEMA GOVERNANCE_TEST.RAW_DATA;

ALTER TABLE IF EXISTS SALES_TRANSACTIONS
  ADD ROW ACCESS POLICY POLICIES.region_filter_policy ON (SALES_REGION);

Verifying the Results

					
			-- Execute query with GOVERNANCE_ADMIN role (all rows will be displayed)
USE ROLE GOVERNANCE_ADMIN;
SELECT * FROM GOVERNANCE_TEST.RAW_DATA.SALES_TRANSACTIONS;
-- 6 rows returned

2025-08-27_09h42_08

					
			-- Execute query with SALES_MANAGER role (only NA and EU rows will be displayed)
USE ROLE SALES_MANAGER;
SELECT * FROM GOVERNANCE_TEST.RAW_DATA.SALES_TRANSACTIONS;
-- 5 rows returned (NA: 3 rows, EU: 2 rows, out of APAC: 1 row, only NA and EU rows are displayed)
-- Per the mapping table definition, only NA and EU rows are displayed

2025-08-27_09h43_55

					
			-- Execute query with ANALYST role (no rows will be displayed)
USE ROLE ANALYST;
SELECT * FROM GOVERNANCE_TEST.RAW_DATA.SALES_TRANSACTIONS;
-- 0 rows returned

2025-08-27_09h44_35

I also checked the count, and similarly, it returned 0 results.
2025-08-27_09h46_20

In Conclusion

How was it?
I felt that masking policies and row access policies are important features for building an organization's data governance structure.
However, setting them up without proper planning could prevent users from seeing data they should be able to view (such as payment amounts or user counts), which defeats the purpose. Therefore, careful verification is necessary when implementing these policies.
I hope this article was helpful in some way!