Snowflake governance questions often test column masking, row access policies, tags, and the difference between protecting columns and filtering rows. This lab uses demo customer data. Masking and row access policies require Enterprise Edition or higher.

Step 1: Create the demo workspace

USE ROLE ACCOUNTADMIN;

CREATE WAREHOUSE IF NOT EXISTS GOVERNANCE_LAB_WH
  WAREHOUSE_SIZE = XSMALL
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE;

CREATE DATABASE IF NOT EXISTS GOVERNANCE_LAB_DB;
CREATE SCHEMA IF NOT EXISTS GOVERNANCE_LAB_DB.SECURE_DATA;
CREATE SCHEMA IF NOT EXISTS GOVERNANCE_LAB_DB.GOVERNANCE;

USE WAREHOUSE GOVERNANCE_LAB_WH;
USE DATABASE GOVERNANCE_LAB_DB;

Step 2: Create roles and sample data

CREATE ROLE IF NOT EXISTS GOVERNANCE_LAB_FULL_ACCESS;
CREATE ROLE IF NOT EXISTS GOVERNANCE_LAB_EAST_ANALYST;
CREATE ROLE IF NOT EXISTS GOVERNANCE_LAB_WEST_ANALYST;

CREATE OR REPLACE TABLE SECURE_DATA.CUSTOMERS (
  CUSTOMER_ID NUMBER,
  CUSTOMER_NAME STRING,
  EMAIL STRING,
  REGION STRING,
  LIFETIME_VALUE NUMBER(10, 2)
);

INSERT INTO SECURE_DATA.CUSTOMERS
SELECT * FROM VALUES
  (101, 'Apex Supply', 'buyer1@example.com', 'East', 1200.00),
  (102, 'Northwind',   'buyer2@example.com', 'West',  850.50),
  (103, 'Summit Co',   'buyer3@example.com', 'East',  430.25),
  (104, 'Metro Goods', 'buyer4@example.com', 'West', 2200.10)
AS v (CUSTOMER_ID, CUSTOMER_NAME, EMAIL, REGION, LIFETIME_VALUE);

Step 3: Create and apply a tag

USE SCHEMA GOVERNANCE;

CREATE OR REPLACE TAG DATA_CLASSIFICATION
  ALLOWED_VALUES 'PUBLIC', 'INTERNAL', 'CONFIDENTIAL', 'RESTRICTED';

ALTER TABLE SECURE_DATA.CUSTOMERS
  MODIFY COLUMN EMAIL
  SET TAG GOVERNANCE.DATA_CLASSIFICATION = 'RESTRICTED';

ALTER TABLE SECURE_DATA.CUSTOMERS
  MODIFY COLUMN LIFETIME_VALUE
  SET TAG GOVERNANCE.DATA_CLASSIFICATION = 'CONFIDENTIAL';

Inspect tag references.

SELECT *
FROM TABLE(
  INFORMATION_SCHEMA.TAG_REFERENCES(
    'SECURE_DATA.CUSTOMERS',
    'TABLE'
  )
);

Step 4: Create and apply a masking policy

CREATE OR REPLACE MASKING POLICY GOVERNANCE.EMAIL_MASK AS
  (email_value STRING) RETURNS STRING ->
    CASE
      WHEN CURRENT_ROLE() IN ('ACCOUNTADMIN', 'GOVERNANCE_LAB_FULL_ACCESS') THEN email_value
      ELSE REGEXP_REPLACE(email_value, '(^.).*(@.*$)', '\\1***\\2')
    END;

ALTER TABLE SECURE_DATA.CUSTOMERS
  MODIFY COLUMN EMAIL
  SET MASKING POLICY GOVERNANCE.EMAIL_MASK;

Step 5: Create and apply a row access policy

CREATE OR REPLACE ROW ACCESS POLICY GOVERNANCE.REGION_ACCESS_POLICY AS
  (region_value STRING) RETURNS BOOLEAN ->
    CASE
      WHEN CURRENT_ROLE() IN ('ACCOUNTADMIN', 'GOVERNANCE_LAB_FULL_ACCESS') THEN TRUE
      WHEN CURRENT_ROLE() = 'GOVERNANCE_LAB_EAST_ANALYST' THEN region_value = 'East'
      WHEN CURRENT_ROLE() = 'GOVERNANCE_LAB_WEST_ANALYST' THEN region_value = 'West'
      ELSE FALSE
    END;

ALTER TABLE SECURE_DATA.CUSTOMERS
  ADD ROW ACCESS POLICY GOVERNANCE.REGION_ACCESS_POLICY ON (REGION);

Step 6: Grant access for testing

GRANT USAGE ON WAREHOUSE GOVERNANCE_LAB_WH TO ROLE GOVERNANCE_LAB_FULL_ACCESS;
GRANT USAGE ON WAREHOUSE GOVERNANCE_LAB_WH TO ROLE GOVERNANCE_LAB_EAST_ANALYST;
GRANT USAGE ON WAREHOUSE GOVERNANCE_LAB_WH TO ROLE GOVERNANCE_LAB_WEST_ANALYST;

GRANT USAGE ON DATABASE GOVERNANCE_LAB_DB TO ROLE GOVERNANCE_LAB_FULL_ACCESS;
GRANT USAGE ON DATABASE GOVERNANCE_LAB_DB TO ROLE GOVERNANCE_LAB_EAST_ANALYST;
GRANT USAGE ON DATABASE GOVERNANCE_LAB_DB TO ROLE GOVERNANCE_LAB_WEST_ANALYST;

GRANT USAGE ON SCHEMA GOVERNANCE_LAB_DB.SECURE_DATA TO ROLE GOVERNANCE_LAB_FULL_ACCESS;
GRANT USAGE ON SCHEMA GOVERNANCE_LAB_DB.SECURE_DATA TO ROLE GOVERNANCE_LAB_EAST_ANALYST;
GRANT USAGE ON SCHEMA GOVERNANCE_LAB_DB.SECURE_DATA TO ROLE GOVERNANCE_LAB_WEST_ANALYST;

GRANT SELECT ON TABLE GOVERNANCE_LAB_DB.SECURE_DATA.CUSTOMERS TO ROLE GOVERNANCE_LAB_FULL_ACCESS;
GRANT SELECT ON TABLE GOVERNANCE_LAB_DB.SECURE_DATA.CUSTOMERS TO ROLE GOVERNANCE_LAB_EAST_ANALYST;
GRANT SELECT ON TABLE GOVERNANCE_LAB_DB.SECURE_DATA.CUSTOMERS TO ROLE GOVERNANCE_LAB_WEST_ANALYST;

To test as those roles, grant them to your user, then USE ROLE each one.

-- Replace YOUR_USER_NAME before running.
-- GRANT ROLE GOVERNANCE_LAB_EAST_ANALYST TO USER YOUR_USER_NAME;
-- USE ROLE GOVERNANCE_LAB_EAST_ANALYST;

SELECT *
FROM GOVERNANCE_LAB_DB.SECURE_DATA.CUSTOMERS
ORDER BY CUSTOMER_ID;

Step 7: Inspect policies

SHOW MASKING POLICIES IN SCHEMA GOVERNANCE;
SHOW ROW ACCESS POLICIES IN SCHEMA GOVERNANCE;

SELECT *
FROM TABLE(
  INFORMATION_SCHEMA.POLICY_REFERENCES(
    REF_ENTITY_NAME => 'GOVERNANCE_LAB_DB.SECURE_DATA.CUSTOMERS',
    REF_ENTITY_DOMAIN => 'TABLE'
  )
);

Step 8: Clean up

USE ROLE ACCOUNTADMIN;

ALTER TABLE IF EXISTS SECURE_DATA.CUSTOMERS
  DROP ROW ACCESS POLICY GOVERNANCE.REGION_ACCESS_POLICY;

ALTER TABLE IF EXISTS SECURE_DATA.CUSTOMERS
  MODIFY COLUMN EMAIL
  UNSET MASKING POLICY;

DROP DATABASE IF EXISTS GOVERNANCE_LAB_DB;
DROP WAREHOUSE IF EXISTS GOVERNANCE_LAB_WH;

DROP ROLE IF EXISTS GOVERNANCE_LAB_FULL_ACCESS;
DROP ROLE IF EXISTS GOVERNANCE_LAB_EAST_ANALYST;
DROP ROLE IF EXISTS GOVERNANCE_LAB_WEST_ANALYST;

Operational focus

Masking policies protect column values. Row access policies filter rows. Tags classify objects and columns. Pay close attention to whether the requirement is column-level security, row-level security, metadata classification, or all three.

Protect Sensitive Data With Masking, Row Access, and Tags