Implementing Row-Level Security and Column Masking Policies for Multi-Tenant Analytics in Snowflake and BigQuery

Introduction

You've built a beautiful analytics platform. Multiple clients — let's call them tenants — are all running queries against your data warehouse. One day you realize that with the wrong JOIN or a misconfigured view, Tenant A could theoretically see Tenant B's revenue numbers, customer PII, or pricing data. That's not a compliance failure waiting to happen. That's a catastrophe waiting to happen.

Multi-tenant analytics is one of the most common architectures in modern SaaS and managed analytics platforms, and it's also one of the most frequently under-secured. Most teams start by creating separate schemas or databases per tenant, which works until it becomes operationally unsustainable at dozens or hundreds of tenants. The better approach is a shared schema architecture with robust, policy-driven data access controls enforced at the warehouse level — not in your application layer, not in your BI tool, and not in a labyrinth of views you have to maintain by hand.

By the end of this lesson, you'll understand how to implement production-grade Row-Level Security (RLS) and column masking in both Snowflake and BigQuery, covering the architectural differences between the two platforms, when to use each mechanism, and how to avoid the subtle gotchas that catch teams off guard in production.

What you'll learn:

• How Snowflake's Row Access Policies and Dynamic Data Masking work, and how to attach them to tables at scale
• How BigQuery's Row-Level Security with row access policies and column-level security with policy tags work architecturally differently from Snowflake's approach
• How to design a tenant-mapping table that drives both RLS and masking consistently across your platform
• How to test and audit your policies to confirm they're actually doing what you think they're doing
• Performance implications of each approach and when policy-based security beats alternative architectures

---

Prerequisites

You should be comfortable with:

• SQL at an intermediate level (CTEs, window functions, joins)
• Basic Snowflake or BigQuery administration (roles, users, schemas)
• The concept of multi-tenancy in data platforms — multiple clients sharing infrastructure
• Familiarity with IAM concepts (roles, grants, permissions)

---

The Architecture We're Building

Before writing a single policy, let's establish what we're actually building. Picture a SaaS analytics platform called Orbis Analytics that serves B2B clients. Orbis has three tenants today: Apex Retail, Meridian Finance, and Coastline Health. All three tenants have analysts who log into Orbis's BI tool and run queries against a shared orders, customers, and transactions table.

The requirements are:

1. Each tenant can only see rows that belong to their organization
2. Certain columns — specifically email, ssn, credit_card_number — must be masked for analysts and fully visible only to data engineers and compliance officers
3. The security must be enforced at the warehouse layer, not the BI tool layer
4. Adding a new tenant should require zero schema changes and minimal operational overhead

This is a realistic production scenario. Let's build it.

---

Part 1: Snowflake Implementation

The Foundation: Tenant Mapping

The secret ingredient in any scalable RLS system is a well-designed mapping table that connects the current session's identity to the set of tenant IDs they're allowed to see. In Snowflake, that identity is the user's current role.

Start by creating your schema structure:

-- Administrative database for policy infrastructure
CREATE DATABASE orbis_security;
CREATE SCHEMA orbis_security.access_control;

-- The mapping table: which roles can see which tenant_ids?
CREATE TABLE orbis_security.access_control.tenant_role_mapping (
    role_name       VARCHAR(100) NOT NULL,
    tenant_id       VARCHAR(50)  NOT NULL,
    access_level    VARCHAR(20)  NOT NULL DEFAULT 'analyst',  -- analyst | engineer | admin
    created_at      TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP(),
    CONSTRAINT pk_tenant_role_mapping PRIMARY KEY (role_name, tenant_id)
);

-- Populate it
INSERT INTO orbis_security.access_control.tenant_role_mapping 
    (role_name, tenant_id, access_level)
VALUES
    ('APEX_RETAIL_ANALYST',       'apex_retail',      'analyst'),
    ('MERIDIAN_FINANCE_ANALYST',  'meridian_finance',  'analyst'),
    ('COASTLINE_HEALTH_ANALYST',  'coastline_health',  'analyst'),
    ('ORBIS_DATA_ENGINEER',       'apex_retail',      'engineer'),
    ('ORBIS_DATA_ENGINEER',       'meridian_finance',  'engineer'),
    ('ORBIS_DATA_ENGINEER',       'coastline_health',  'engineer'),
    ('ORBIS_COMPLIANCE',          'apex_retail',      'admin'),
    ('ORBIS_COMPLIANCE',          'meridian_finance',  'admin'),
    ('ORBIS_COMPLIANCE',          'coastline_health',  'admin');

Notice that ORBIS_DATA_ENGINEER maps to all three tenants. When this role runs a query, it'll see all rows. A tenant-specific analyst role will only see their tenant's rows. This design means adding Tenant D later is just an INSERT into this table and a new role — no schema work required.

Creating the Row Access Policy

Snowflake's Row Access Policy is a schema-level object that contains a SQL function returning a boolean. Snowflake evaluates this function for every row, and only returns rows where the function returns TRUE. The critical thing to understand is that this evaluation happens inside the engine — it's not a pre-filter applied to the query plan in a naive way. Snowflake is smart enough to push these predicates down.

USE DATABASE orbis_security;
USE SCHEMA access_control;

CREATE OR REPLACE ROW ACCESS POLICY tenant_isolation_policy
AS (tenant_id VARCHAR) RETURNS BOOLEAN ->
    -- Option 1: The current role is the ACCOUNTADMIN (break-glass access)
    CURRENT_ROLE() IN ('ACCOUNTADMIN', 'SYSADMIN')
    OR
    -- Option 2: The current role has an explicit mapping for this tenant_id
    EXISTS (
        SELECT 1 
        FROM orbis_security.access_control.tenant_role_mapping trm
        WHERE trm.role_name = CURRENT_ROLE()
          AND trm.tenant_id = tenant_id
    );

The policy takes tenant_id as an argument — this will be the actual column from the table you attach it to. Snowflake binds the argument by position when you attach the policy.

Warning: The EXISTS subquery runs against the mapping table for every row evaluation. This is where many teams run into performance issues. We'll address this with caching strategies shortly.

Now attach the policy to your tables:

-- Assuming your analytics data lives here
USE DATABASE orbis_analytics;

-- Attach to the orders table
ALTER TABLE orbis_analytics.core.orders
    ADD ROW ACCESS POLICY orbis_security.access_control.tenant_isolation_policy 
    ON (tenant_id);

-- Attach to the customers table
ALTER TABLE orbis_analytics.core.customers
    ADD ROW ACCESS POLICY orbis_security.access_control.tenant_isolation_policy 
    ON (tenant_id);

-- Attach to the transactions table
ALTER TABLE orbis_analytics.core.transactions
    ADD ROW ACCESS POLICY orbis_security.access_control.tenant_isolation_policy 
    ON (tenant_id);

One policy, three tables. When you update the policy logic, it updates everywhere. This is the key operational advantage over maintaining dozens of views.

Creating Dynamic Data Masking Policies

Now let's handle column masking. The goal: analysts see email as a***@***.com, ssn as ***-**-XXXX, and credit_card_number as ****-****-****-1234. Engineers and compliance officers see the real values.

USE DATABASE orbis_security;
USE SCHEMA access_control;

-- Email masking policy
CREATE OR REPLACE MASKING POLICY email_mask
AS (val STRING) RETURNS STRING ->
    CASE
        WHEN CURRENT_ROLE() IN ('ORBIS_DATA_ENGINEER', 'ORBIS_COMPLIANCE', 
                                 'ACCOUNTADMIN', 'SYSADMIN') 
            THEN val
        WHEN val IS NULL 
            THEN NULL
        ELSE 
            -- Show first character, mask the rest up to @, then mask domain
            CONCAT(
                LEFT(val, 1),
                REPEAT('*', CHARINDEX('@', val) - 2),
                '@***.com'
            )
    END;

-- SSN masking policy
CREATE OR REPLACE MASKING POLICY ssn_mask
AS (val STRING) RETURNS STRING ->
    CASE
        WHEN CURRENT_ROLE() IN ('ORBIS_COMPLIANCE', 'ACCOUNTADMIN') 
            THEN val  -- Only compliance sees full SSN, not even engineers
        WHEN val IS NULL 
            THEN NULL
        ELSE 
            CONCAT('***-**-', RIGHT(val, 4))
    END;

-- Credit card masking policy
CREATE OR REPLACE MASKING POLICY credit_card_mask
AS (val STRING) RETURNS STRING ->
    CASE
        WHEN CURRENT_ROLE() IN ('ORBIS_DATA_ENGINEER', 'ORBIS_COMPLIANCE',
                                 'ACCOUNTADMIN') 
            THEN val
        WHEN val IS NULL 
            THEN NULL
        ELSE 
            CONCAT('****-****-****-', RIGHT(REPLACE(val, '-', ''), 4))
    END;

Notice the deliberate difference between email_mask and ssn_mask: engineers can see real emails (useful for debugging), but SSNs are restricted to compliance only. Your masking policies should reflect your actual data classification tiers, not just blanket rules.

Attach the masking policies to columns:

ALTER TABLE orbis_analytics.core.customers 
    MODIFY COLUMN email 
    SET MASKING POLICY orbis_security.access_control.email_mask;

ALTER TABLE orbis_analytics.core.customers 
    MODIFY COLUMN ssn 
    SET MASKING POLICY orbis_security.access_control.ssn_mask;

ALTER TABLE orbis_analytics.core.transactions 
    MODIFY COLUMN credit_card_number 
    SET MASKING POLICY orbis_security.access_control.credit_card_mask;

Performance Considerations in Snowflake

The EXISTS subquery in a row access policy gets executed for every row that passes the micro-partition pruning phase. For large tables with billions of rows, this can become expensive. Here are two mitigation strategies:

Strategy 1: Use CURRENT_ROLE() directly in a CASE expression when your tenant structure is stable

CREATE OR REPLACE ROW ACCESS POLICY tenant_isolation_policy_fast
AS (tenant_id VARCHAR) RETURNS BOOLEAN ->
    CASE CURRENT_ROLE()
        WHEN 'ACCOUNTADMIN'             THEN TRUE
        WHEN 'SYSADMIN'                 THEN TRUE
        WHEN 'ORBIS_DATA_ENGINEER'      THEN TRUE
        WHEN 'ORBIS_COMPLIANCE'         THEN TRUE
        WHEN 'APEX_RETAIL_ANALYST'      THEN tenant_id = 'apex_retail'
        WHEN 'MERIDIAN_FINANCE_ANALYST' THEN tenant_id = 'meridian_finance'
        WHEN 'COASTLINE_HEALTH_ANALYST' THEN tenant_id = 'coastline_health'
        ELSE FALSE
    END;

This approach is faster because it avoids a subquery, but it requires updating the policy when you add tenants. Use it when you have fewer than ~20 tenants and tenant onboarding is infrequent.

Strategy 2: Cluster your tables on tenant_id

When Snowflake knows which tenant a query is for (because the role maps to one tenant), it can use micro-partition pruning to skip irrelevant partitions entirely. Clustering on tenant_id makes this much more efficient:

ALTER TABLE orbis_analytics.core.orders 
    CLUSTER BY (tenant_id);

With clustering, a query from APEX_RETAIL_ANALYST touching orders will only scan the micro-partitions containing apex_retail data. Combined with the row access policy, you get both correctness and performance.

Tip: Use SYSTEM$CLUSTERING_INFORMATION('orbis_analytics.core.orders', '(tenant_id)') to check how well-clustered your table is and whether it's worth the reclustering cost.

Auditing Your Snowflake Policies

Never assume your policies work. Verify them. Snowflake provides system views to inspect policy attachments:

-- See all row access policies attached in your account
SELECT 
    ref_database_name,
    ref_schema_name,
    ref_entity_name,
    ref_column_name,
    policy_name,
    policy_kind
FROM TABLE(INFORMATION_SCHEMA.POLICY_REFERENCES(
    policy_name => 'orbis_security.access_control.tenant_isolation_policy'
));

-- Check which masking policies are active on a specific table
SELECT 
    column_name,
    masking_policy_name
FROM TABLE(INFORMATION_SCHEMA.POLICY_REFERENCES(
    ref_entity_name => 'orbis_analytics.core.customers',
    ref_entity_domain => 'TABLE'
));

To verify actual policy behavior, use SYSTEM$POLICY_CONTEXT to simulate what a given role would see:

-- This checks whether the row with tenant_id = 'meridian_finance' would be visible
-- to the APEX_RETAIL_ANALYST role
SELECT SYSTEM$POLICY_CONTEXT(
    'ROW ACCESS POLICY',
    'orbis_security.access_control.tenant_isolation_policy',
    'APEX_RETAIL_ANALYST',
    'meridian_finance'
);

---

Part 2: BigQuery Implementation

BigQuery's security model is architecturally different from Snowflake's. Rather than policy objects you write in SQL and attach to tables, BigQuery uses:

• Row-level security via row access policies (IAM-based, with a filter expression)
• Column-level security via policy tags in Data Catalog, connected to IAM bindings

This is important to internalize: BigQuery's security is IAM-native. Your Google Cloud principals (users, service accounts, groups) are the identity source, not database roles.

Setting Up Row-Level Security in BigQuery

BigQuery row access policies are created with a CREATE ROW ACCESS POLICY DDL statement. Each policy defines a filter expression (a WHERE clause) that's applied for a specific set of grantees.

First, let's look at the table structure:

-- In BigQuery, your multi-tenant orders table might look like this
-- Dataset: orbis_analytics, Table: orders
CREATE TABLE orbis_analytics.orders (
    order_id        STRING NOT NULL,
    tenant_id       STRING NOT NULL,
    customer_id     STRING NOT NULL,
    order_date      DATE,
    total_amount    NUMERIC(15, 2),
    status          STRING,
    created_at      TIMESTAMP
);

Now create row access policies per tenant. In BigQuery, each policy is associated with specific IAM grantees:

-- Policy for Apex Retail analysts
-- Their Google Group is apex-retail-analysts@orbis.com
CREATE OR REPLACE ROW ACCESS POLICY apex_retail_row_policy
ON orbis_analytics.orders
GRANT TO ('group:apex-retail-analysts@orbis.com')
FILTER USING (tenant_id = 'apex_retail');

-- Policy for Meridian Finance analysts
CREATE OR REPLACE ROW ACCESS POLICY meridian_finance_row_policy
ON orbis_analytics.orders
GRANT TO ('group:meridian-finance-analysts@orbis.com')
FILTER USING (tenant_id = 'meridian_finance');

-- Policy for Coastline Health analysts
CREATE OR REPLACE ROW ACCESS POLICY coastline_health_row_policy
ON orbis_analytics.orders
GRANT TO ('group:coastline-health-analysts@orbis.com')
FILTER USING (tenant_id = 'coastline_health');

-- Data engineers and admins see everything -- 
-- They're granted bigquery.dataViewer at the dataset level
-- AND have a row access policy that returns all rows
CREATE OR REPLACE ROW ACCESS POLICY orbis_full_access_policy
ON orbis_analytics.orders
GRANT TO (
    'group:orbis-data-engineers@orbis.com',
    'group:orbis-compliance@orbis.com',
    'serviceAccount:orbis-pipeline-sa@orbis-project.iam.gserviceaccount.com'
)
FILTER USING (TRUE);

Critical behavior: In BigQuery, if a principal matches any row access policy on a table, they see the union of all rows permitted by matching policies. If a principal matches no row access policy, they see no rows at all — not all rows. This is the opposite of what many people assume. Your full-access policy must explicitly grant access.

This also means a user who is in both apex-retail-analysts@orbis.com and orbis-data-engineers@orbis.com (perhaps they're a dual-role user) will see all rows, because the full-access policy grants FILTER USING (TRUE).

You can query the applied policies like this:

SELECT * 
FROM orbis_analytics.INFORMATION_SCHEMA.ROW_ACCESS_POLICIES
WHERE table_name = 'orders';

Scaling the BigQuery Approach with Session Parameters

The per-policy-per-group approach works but creates a proliferation problem: with 100 tenants you'd have 100 row access policies per table. A more scalable approach uses a service account with session variables, common when you have a query proxy layer (like a BI tool with a single service account).

Here's the pattern: your application layer passes the tenant context, and you use a session variable in the filter:

-- Create a single row access policy that checks a session variable
CREATE OR REPLACE ROW ACCESS POLICY tenant_session_policy
ON orbis_analytics.orders
GRANT TO ('serviceAccount:orbis-bi-proxy@orbis-project.iam.gserviceaccount.com')
FILTER USING (
    tenant_id = SESSION_USER()  -- Only works if SESSION_USER maps to tenant
    OR 
    -- Better: use a custom claim from your query parameterization
    tenant_id IN (
        SELECT tenant_id 
        FROM orbis_security.tenant_access_mapping
        WHERE principal_email = SESSION_USER()
    )
);

Tip: When using a shared service account for a BI proxy (Looker, Metabase, etc.), the SESSION_USER() function won't give you the end user. You'll need to implement user impersonation or use the BI tool's row-level security features layered on top of BigQuery's policies. BigQuery's native RLS is most powerful when each human user authenticates directly.

Column-Level Security with Policy Tags

BigQuery's column masking is different from Snowflake's — it's not programmatic SQL masking. Instead, it's access control: a user either sees the column value or they see NULL. More nuanced masking (like showing partial SSNs) requires BigQuery's Data Masking feature in Sensitive Data Protection (formerly DLP).

Here's the architecture: you define policy tags in Data Catalog, assign them to columns, and then control access via IAM.

Step 1: Create a taxonomy and policy tags (this is done via the Data Catalog API or Console UI)

Using the bq CLI:

# Create a taxonomy for data sensitivity
gcloud data-catalog taxonomies create \
    --location=us-central1 \
    --display-name="Orbis Data Sensitivity" \
    --description="Sensitivity classification for Orbis Analytics"

# The taxonomy ID is returned -- use it to create tags
# Let's say the taxonomy ID is 1234567890

gcloud data-catalog taxonomies policy-tags create \
    --location=us-central1 \
    --taxonomy=1234567890 \
    --display-name="PII_HIGH" \
    --description="Highest sensitivity PII: SSN, full credit card"

gcloud data-catalog taxonomies policy-tags create \
    --location=us-central1 \
    --taxonomy=1234567890 \
    --display-name="PII_MEDIUM" \
    --description="Medium sensitivity PII: email, phone"

Step 2: Assign policy tags to BigQuery columns

-- Assign policy tags using ALTER TABLE in BigQuery
-- (You can also do this via the Console or API)
ALTER TABLE orbis_analytics.customers
    ALTER COLUMN email SET OPTIONS (
        description='Customer email - PII_MEDIUM'
    );

-- For actual policy tag assignment via SQL:
-- BigQuery SQL DDL for policy tags uses the OPTIONS syntax
-- The full resource name comes from your Data Catalog taxonomy

ALTER TABLE orbis_analytics.customers
    ALTER COLUMN ssn 
    SET OPTIONS (
        policy_tags='["projects/orbis-project/locations/us-central1/taxonomies/1234567890/policyTags/PII_HIGH_TAG_ID"]'
    );

ALTER TABLE orbis_analytics.customers
    ALTER COLUMN email 
    SET OPTIONS (
        policy_tags='["projects/orbis-project/locations/us-central1/taxonomies/1234567890/policyTags/PII_MEDIUM_TAG_ID"]'
    );

Step 3: Grant fine-grained reader access

# Grant access to see PII_MEDIUM tagged columns (engineers can see emails)
gcloud data-catalog taxonomies policy-tags set-iam-policy \
    projects/orbis-project/locations/us-central1/taxonomies/1234567890/policyTags/PII_MEDIUM_TAG_ID \
    policy.json

# policy.json contents:
# {
#   "bindings": [{
#     "role": "roles/datacatalog.categoryFineGrainedReader",
#     "members": [
#       "group:orbis-data-engineers@orbis.com",
#       "group:orbis-compliance@orbis.com"
#     ]
#   }]
# }

Any principal without categoryFineGrainedReader on the policy tag gets NULL when querying that column. Analysts querying customers will see NULL for ssn and NULL for email. It's blunt but effective.

For the partial-masking behavior (showing a***@***.com), you'll need to configure data masking rules in Sensitive Data Protection, which is a more complex integration — but the framework is the same policy tag system.

---

Snowflake vs. BigQuery: Choosing the Right Tool

Concern	Snowflake	BigQuery
Identity source	Database roles	Google Cloud IAM (users, groups, SAs)
Row filtering	Policy SQL function (flexible)	Filter expression per policy (declarative)
Column masking	SQL expression masking (format-preserving)	Access control (NULL) or DLP rules
Policy as code	SQL DDL, easy to version control	Mix of SQL, gcloud CLI, IAM JSON
Tenant scaling	Single policy + mapping table	One policy per tenant group (can explode)
Performance control	Clustering + policy optimization	Partition pruning still applies
Audit	POLICY_REFERENCES, Query History	INFORMATION_SCHEMA.ROW_ACCESS_POLICIES, Audit Logs

The core insight: Snowflake gives you more programmatic control over how data is filtered and masked, at the cost of writing more SQL. BigQuery's model is more declarative and IAM-native, which integrates better with GCP's ecosystem but is less flexible for custom masking scenarios.

---

Hands-On Exercise

Let's put everything together. You're going to build the complete Orbis Analytics security layer in Snowflake from scratch.

Setup: Create the base environment

-- Run as ACCOUNTADMIN or a role with CREATE DATABASE privilege
CREATE DATABASE orbis_security;
CREATE DATABASE orbis_analytics;
CREATE SCHEMA orbis_security.access_control;
CREATE SCHEMA orbis_analytics.core;

-- Create the roles
CREATE ROLE apex_retail_analyst;
CREATE ROLE meridian_finance_analyst;
CREATE ROLE orbis_data_engineer;
CREATE ROLE orbis_compliance;

-- Create test users (in production, use SSO/SCIM)
CREATE USER alice PASSWORD='TempPass123!' DEFAULT_ROLE=apex_retail_analyst;
CREATE USER bob   PASSWORD='TempPass123!' DEFAULT_ROLE=orbis_data_engineer;
GRANT ROLE apex_retail_analyst   TO USER alice;
GRANT ROLE orbis_data_engineer   TO USER bob;

Step 1: Create and populate the mapping table

USE DATABASE orbis_security;
USE SCHEMA access_control;

CREATE TABLE tenant_role_mapping (
    role_name    VARCHAR(100),
    tenant_id    VARCHAR(50),
    access_level VARCHAR(20)
);

INSERT INTO tenant_role_mapping VALUES
    ('APEX_RETAIL_ANALYST',      'apex_retail',     'analyst'),
    ('MERIDIAN_FINANCE_ANALYST', 'meridian_finance', 'analyst'),
    ('ORBIS_DATA_ENGINEER',      'apex_retail',     'engineer'),
    ('ORBIS_DATA_ENGINEER',      'meridian_finance', 'engineer');

Step 2: Create sample data

USE DATABASE orbis_analytics;
USE SCHEMA core;

CREATE TABLE customers (
    customer_id    VARCHAR(20),
    tenant_id      VARCHAR(50),
    full_name      VARCHAR(100),
    email          VARCHAR(200),
    ssn            VARCHAR(11),
    signup_date    DATE
);

INSERT INTO customers VALUES
    ('C001', 'apex_retail',     'Sarah Chen',     'sarah.chen@example.com',    '412-55-1234', '2022-03-15'),
    ('C002', 'apex_retail',     'Marcus Webb',    'marcus.webb@example.com',   '531-77-8901', '2022-07-22'),
    ('C003', 'meridian_finance','Priya Sharma',   'priya.sharma@example.com',  '678-23-4567', '2023-01-10'),
    ('C004', 'meridian_finance','James Okonkwo',  'james.okonkwo@example.com', '789-44-2345', '2023-04-18');

Step 3: Create and apply policies

USE DATABASE orbis_security;
USE SCHEMA access_control;

CREATE OR REPLACE ROW ACCESS POLICY tenant_isolation_policy
AS (tenant_id VARCHAR) RETURNS BOOLEAN ->
    CURRENT_ROLE() IN ('ACCOUNTADMIN', 'SYSADMIN', 'ORBIS_COMPLIANCE')
    OR
    EXISTS (
        SELECT 1 FROM orbis_security.access_control.tenant_role_mapping
        WHERE role_name = CURRENT_ROLE()
          AND tenant_id = tenant_id
    );

CREATE OR REPLACE MASKING POLICY ssn_mask
AS (val STRING) RETURNS STRING ->
    CASE
        WHEN CURRENT_ROLE() = 'ORBIS_COMPLIANCE' THEN val
        WHEN val IS NULL THEN NULL
        ELSE CONCAT('***-**-', RIGHT(val, 4))
    END;

-- Grant usage on the policy objects
GRANT OWNERSHIP ON MASKING POLICY ssn_mask TO ROLE ACCOUNTADMIN COPY CURRENT GRANTS;
GRANT APPLY ON MASKING POLICY orbis_security.access_control.ssn_mask TO ROLE ACCOUNTADMIN;

USE DATABASE orbis_analytics;
USE SCHEMA core;

-- Grant the roles access to read the table
GRANT USAGE ON DATABASE orbis_analytics TO ROLE apex_retail_analyst;
GRANT USAGE ON DATABASE orbis_analytics TO ROLE orbis_data_engineer;
GRANT USAGE ON SCHEMA orbis_analytics.core TO ROLE apex_retail_analyst;
GRANT USAGE ON SCHEMA orbis_analytics.core TO ROLE orbis_data_engineer;
GRANT SELECT ON TABLE orbis_analytics.core.customers TO ROLE apex_retail_analyst;
GRANT SELECT ON TABLE orbis_analytics.core.customers TO ROLE orbis_data_engineer;

-- Also grant read on the mapping table (needed for the policy subquery)
GRANT USAGE ON DATABASE orbis_security TO ROLE apex_retail_analyst;
GRANT USAGE ON SCHEMA orbis_security.access_control TO ROLE apex_retail_analyst;
GRANT SELECT ON TABLE orbis_security.access_control.tenant_role_mapping TO ROLE apex_retail_analyst;

-- Apply the row access policy
ALTER TABLE orbis_analytics.core.customers
    ADD ROW ACCESS POLICY orbis_security.access_control.tenant_isolation_policy
    ON (tenant_id);

-- Apply the masking policy
ALTER TABLE orbis_analytics.core.customers
    MODIFY COLUMN ssn
    SET MASKING POLICY orbis_security.access_control.ssn_mask;

Step 4: Verify the behavior

-- Switch to alice's role (Apex Retail analyst)
USE ROLE apex_retail_analyst;
SELECT customer_id, tenant_id, full_name, email, ssn FROM orbis_analytics.core.customers;
-- Expected: 2 rows (C001, C002), SSN shows as ***-**-1234 and ***-**-8901

-- Switch to bob's role (data engineer)
USE ROLE orbis_data_engineer;
SELECT customer_id, tenant_id, full_name, email, ssn FROM orbis_analytics.core.customers;
-- Expected: 4 rows, SSN still masked (engineer doesn't have compliance access)

-- Switch to compliance role
USE ROLE orbis_compliance;
SELECT customer_id, tenant_id, full_name, email, ssn FROM orbis_analytics.core.customers;
-- Expected: 4 rows, full SSN visible

If you see the wrong row counts or wrong masking behavior, jump to the troubleshooting section below.

---

Common Mistakes & Troubleshooting

Mistake 1: The Ambiguous Column Name in Snowflake Row Access Policies

This is the most common gotcha. In the policy definition:

CREATE OR REPLACE ROW ACCESS POLICY tenant_isolation_policy
AS (tenant_id VARCHAR) RETURNS BOOLEAN ->
    EXISTS (
        SELECT 1 FROM tenant_role_mapping
        WHERE role_name = CURRENT_ROLE()
          AND tenant_id = tenant_id  -- BUG: this compares tenant_id to itself!
    );

The AND tenant_id = tenant_id condition compares the subquery column to itself — always true. The correct form uses the policy parameter explicitly. Use a parameter name that won't collide with column names in your mapping table:

CREATE OR REPLACE ROW ACCESS POLICY tenant_isolation_policy
AS (row_tenant_id VARCHAR) RETURNS BOOLEAN ->
    EXISTS (
        SELECT 1 FROM tenant_role_mapping
        WHERE role_name = CURRENT_ROLE()
          AND tenant_id = row_tenant_id  -- Now unambiguous
    );

Mistake 2: Missing Grants on the Policy Reference Tables

Your row access policy subquery reads from tenant_role_mapping. Every role that queries the protected table needs SELECT on that mapping table too — even though they'll never directly query it themselves. If this grant is missing, the policy evaluation will fail with a permissions error, and in Snowflake's default behavior, a policy that errors returns FALSE — meaning the row is hidden. Your users won't see an error; they'll just see no data and wonder if the table is empty.

-- Don't forget this for every analyst role
GRANT USAGE ON DATABASE orbis_security TO ROLE apex_retail_analyst;
GRANT USAGE ON SCHEMA orbis_security.access_control TO ROLE apex_retail_analyst;
GRANT SELECT ON TABLE orbis_security.access_control.tenant_role_mapping 
    TO ROLE apex_retail_analyst;

Mistake 3: BigQuery's "No Policy = No Rows" Behavior

Teams migrating from Snowflake often expect BigQuery's default to be "if there's no matching policy, see everything." It's the opposite. If you attach even one row access policy to a BigQuery table, any principal not covered by at least one policy sees zero rows — silently. Always create an explicit full-access policy for your admin and engineering groups.

Mistake 4: Forgetting to Handle NULL tenant_id Values

If your tenant_id column can be NULL (perhaps for internal system rows), row access policies can behave unexpectedly. In Snowflake, NULL = 'apex_retail' evaluates to NULL (not FALSE), but in the context of a boolean policy, NULL-returning policies are treated as FALSE. Add explicit NULL handling:

CREATE OR REPLACE ROW ACCESS POLICY tenant_isolation_policy
AS (row_tenant_id VARCHAR) RETURNS BOOLEAN ->
    row_tenant_id IS NULL  -- System rows visible to everyone
    OR
    CURRENT_ROLE() IN ('ACCOUNTADMIN', 'SYSADMIN', 'ORBIS_COMPLIANCE')
    OR
    EXISTS (
        SELECT 1 FROM orbis_security.access_control.tenant_role_mapping
        WHERE role_name = CURRENT_ROLE()
          AND tenant_id = row_tenant_id
    );

Mistake 5: Applying Multiple Row Access Policies in Snowflake

Snowflake only allows one row access policy per table. If you try to add a second, you'll get an error. Design your single policy to handle all access tiers, not one policy per tenant.

Debugging Tip: Use Query History + Explain Plans

When a policy isn't behaving as expected, run the query as ACCOUNTADMIN and compare results:

-- As ACCOUNTADMIN (bypasses row access policies? No — it's handled by the policy)
-- To truly bypass for debugging, detach the policy temporarily
ALTER TABLE orbis_analytics.core.customers
    DROP ROW ACCESS POLICY orbis_security.access_control.tenant_isolation_policy;

-- Run your diagnostic query
SELECT COUNT(*), tenant_id FROM orbis_analytics.core.customers GROUP BY tenant_id;

-- Re-attach immediately
ALTER TABLE orbis_analytics.core.customers
    ADD ROW ACCESS POLICY orbis_security.access_control.tenant_isolation_policy
    ON (tenant_id);

Never leave a policy detached. Automate this with a script that always re-attaches.

---

Summary & Next Steps

You've built a production-ready multi-tenant security layer across both Snowflake and BigQuery. The core patterns you now have in your toolkit:

• A mapping table pattern that makes tenant onboarding a data operation, not a schema operation
• Row Access Policies in Snowflake with proper parameter naming, grant chains, and performance tuning via clustering
• Dynamic Data Masking with tiered access levels that reflect real data classification requirements
• BigQuery Row Access Policies with the IAM-native grantee model and the critical "no policy = no rows" understanding
• BigQuery Policy Tags for column-level access control integrated with Data Catalog

Where to go from here:

1. Terraform or Pulumi the whole thing. Both Snowflake and BigQuery have providers that support row access policies and masking policies. Moving your security configuration into IaC means it's version-controlled, reviewable, and consistent across environments. The Snowflake Terraform provider supports snowflake_row_access_policy and snowflake_masking_policy resources directly.

2. Build a policy test suite. Create a CI pipeline that rotates through every role, runs SELECT COUNT(*) on your protected tables, and asserts the expected row counts. When a policy change ships, your test suite catches regressions before production users do.

3. Add policy change auditing. In Snowflake, use SNOWFLAKE.ACCOUNT_USAGE.POLICY_HISTORY to track when policies were added, removed, or modified. In BigQuery, enable Cloud Audit Logs for Data Catalog policy tag changes and BigQuery DDL operations.

4. Explore Snowflake's Aggregation Policies — a newer feature that lets you restrict certain roles to only run aggregated queries (preventing individual row reads while still allowing analytics). This is a powerful complement to RLS for tenant environments where you want to allow trend analysis but not row-level data access.

5. Integrate with your BI layer. Tools like Looker, Metabase, and Tableau each handle underlying database RLS differently. Test your policies end-to-end through your BI tool, not just through direct SQL clients.

Security at the warehouse layer is your last line of defense and your most reliable one. Application bugs can expose data; BI tool misconfiguration can expose data; but warehouse-level policies enforce regardless of how the query arrives.