Configuring Role-Based Access Control in Snowflake and BigQuery: Warehouses, Schemas, and Row-Level Security for Analytics Teams

Introduction

Imagine you've just joined a fast-growing e-commerce company as a data engineer. The analytics team is exploding — you now have marketing analysts, finance analysts, a data science team, and external contractors all querying the same data warehouse. The marketing team accidentally ran a full-table scan on your 500-million-row orders table, blowing through your compute budget for the week. A contractor can see salary data in the HR schema. A junior analyst just dropped a production table because they had write access they never should have had.

These are not edge cases. They are the predictable consequences of treating a data warehouse like a shared Google Drive with one password. Role-Based Access Control — RBAC — is the framework that prevents this chaos. It's the practice of assigning permissions not to individual users, but to roles, which are then granted to users. When done well, RBAC means every person in your organization can access exactly what they need to do their job, and nothing more.

By the end of this lesson, you'll understand how RBAC works conceptually and how to implement it in both Snowflake and BigQuery — the two most widely used cloud data warehouses today. You'll configure warehouse-level access, schema-level permissions, and row-level security policies that restrict which rows a user can see based on who they are.

What you'll learn:

• The core concepts behind RBAC and why it matters in analytics environments
• How Snowflake's role hierarchy and privilege system works, including virtual warehouse access control
• How BigQuery's IAM-based permission model differs fundamentally from Snowflake's approach
• How to implement schema-level permissions that isolate teams from each other
• How to configure row-level security (RLS) in both platforms so analysts only see data relevant to their region, department, or customer segment

---

Prerequisites

• You have access to a Snowflake trial account or an existing Snowflake environment where you have ACCOUNTADMIN or SECURITYADMIN privileges
• You have access to Google Cloud Platform with a BigQuery project where you have roles/bigquery.admin or roles/owner
• You're comfortable with basic SQL syntax — SELECT, WHERE, CREATE TABLE
• You understand the concept of a database and a table

---

How RBAC Actually Works: The Mental Model

Before writing a single line of SQL, let's build the right mental model. Think of a large hospital. Not everyone who works there can access every room. A surgeon can enter the operating theater, but not the pharmacy storage room. A pharmacist can access the medication inventory, but not read patient files. A billing clerk can see invoice records, but not lab results.

What makes this work is that access is defined by job function — the role you play — not by who you are as a specific person. When a new surgeon joins, you don't create a custom set of keys for them. You hand them the "Surgeon" keycard, which already has the right doors programmed in.

That's RBAC. The "keycards" are roles. The "doors" are database objects — tables, schemas, views, warehouses. The privileges are the specific actions allowed: SELECT (read), INSERT (write), CREATE (create new objects), DROP (delete objects).

The power of this model is manageability. If you need to give the entire analytics team access to a new reporting schema, you grant access to one role, and all twenty analysts get it instantly. If a contractor's engagement ends, you revoke one role assignment, and they lose access to everything immediately.

---

Snowflake RBAC: Roles, Privileges, and the Hierarchy

Snowflake is built with RBAC as a first-class feature. Everything revolves around roles. Let's walk through configuring a realistic setup for an analytics team.

Snowflake's Built-in Role Hierarchy

Snowflake comes with several predefined system roles. Understanding their hierarchy saves you from accidentally granting too much power:

• ORGADMIN — manages organization-level settings
• ACCOUNTADMIN — the superuser role. Full control over everything. Use sparingly.
• SYSADMIN — creates and manages warehouses and databases
• SECURITYADMIN — manages users and roles
• USERADMIN — creates users and roles but cannot grant privileges
• PUBLIC — automatically granted to all users. Assume everything here is visible to everyone.

In practice, your ACCOUNTADMIN account should be like a safe deposit box: locked away, used only for high-stakes operations, and never used for day-to-day work.

Creating Custom Roles

Let's say you're setting up access for three teams: marketing analysts, finance analysts, and data scientists. You want them isolated from each other, with data scientists getting broader read access.

-- Always use SECURITYADMIN or ACCOUNTADMIN to manage roles
USE ROLE SECURITYADMIN;

-- Create team-specific roles
CREATE ROLE marketing_analyst;
CREATE ROLE finance_analyst;
CREATE ROLE data_scientist;

-- Create a parent role that can manage all analyst roles
-- This makes administration cleaner
CREATE ROLE analytics_manager;

-- Grant team roles UP to the manager role
-- This means a user with analytics_manager inherits all child privileges
GRANT ROLE marketing_analyst TO ROLE analytics_manager;
GRANT ROLE finance_analyst TO ROLE analytics_manager;
GRANT ROLE data_scientist TO ROLE analytics_manager;

-- Grant analytics_manager to SYSADMIN so it appears in the hierarchy
GRANT ROLE analytics_manager TO ROLE SYSADMIN;

The GRANT ROLE ... TO ROLE ... pattern creates a role hierarchy. This is Snowflake's inheritance model — a role automatically has all the privileges of roles beneath it in the hierarchy.

Controlling Virtual Warehouse Access

In Snowflake, a virtual warehouse is the compute cluster that actually runs your queries. Warehouses cost money per second they're running. Giving marketing analysts access to your LARGE warehouse meant for data science batch jobs is how you accidentally spend $10,000 in a week.

-- Switch to SYSADMIN to manage warehouses
USE ROLE SYSADMIN;

-- Create separate warehouses for each team
-- XS = Extra Small, which is fine for typical analyst query workloads
CREATE WAREHOUSE marketing_wh
  WAREHOUSE_SIZE = 'XSMALL'
  AUTO_SUSPEND = 60       -- Suspend after 60 seconds of inactivity
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE;

CREATE WAREHOUSE finance_wh
  WAREHOUSE_SIZE = 'XSMALL'
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE;

CREATE WAREHOUSE datascience_wh
  WAREHOUSE_SIZE = 'MEDIUM'  -- Data scientists run heavier workloads
  AUTO_SUSPEND = 300
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE;

-- Now grant USAGE privilege on each warehouse to the appropriate role
USE ROLE SECURITYADMIN;

GRANT USAGE ON WAREHOUSE marketing_wh TO ROLE marketing_analyst;
GRANT USAGE ON WAREHOUSE finance_wh TO ROLE finance_analyst;
GRANT USAGE ON WAREHOUSE datascience_wh TO ROLE data_scientist;

USAGE on a warehouse means the role can use it to run queries. Without this, a user assigned that role gets a permission denied error the moment they try to execute any SQL.

Granting Schema-Level Privileges

Now let's set up your databases and schemas, and grant access appropriately.

USE ROLE SYSADMIN;

-- Create the main analytics database
CREATE DATABASE analytics_db;

-- Create isolated schemas for each domain
CREATE SCHEMA analytics_db.marketing;
CREATE SCHEMA analytics_db.finance;
CREATE SCHEMA analytics_db.shared;       -- Shared reference data
CREATE SCHEMA analytics_db.raw_events;  -- Raw clickstream data

-- Switch back to SECURITYADMIN for privilege grants
USE ROLE SECURITYADMIN;

-- Grant database USAGE first — required before schema or table access works
GRANT USAGE ON DATABASE analytics_db TO ROLE marketing_analyst;
GRANT USAGE ON DATABASE analytics_db TO ROLE finance_analyst;
GRANT USAGE ON DATABASE analytics_db TO ROLE data_scientist;

-- Marketing analysts: READ their schema and SHARED only
GRANT USAGE ON SCHEMA analytics_db.marketing TO ROLE marketing_analyst;
GRANT USAGE ON SCHEMA analytics_db.shared TO ROLE marketing_analyst;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics_db.marketing TO ROLE marketing_analyst;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics_db.shared TO ROLE marketing_analyst;

-- FUTURE grants are critical — they apply to tables created LATER
GRANT SELECT ON FUTURE TABLES IN SCHEMA analytics_db.marketing TO ROLE marketing_analyst;
GRANT SELECT ON FUTURE TABLES IN SCHEMA analytics_db.shared TO ROLE marketing_analyst;

-- Finance analysts: READ their schema and SHARED only
GRANT USAGE ON SCHEMA analytics_db.finance TO ROLE finance_analyst;
GRANT USAGE ON SCHEMA analytics_db.shared TO ROLE finance_analyst;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics_db.finance TO ROLE finance_analyst;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics_db.shared TO ROLE finance_analyst;
GRANT SELECT ON FUTURE TABLES IN SCHEMA analytics_db.finance TO ROLE finance_analyst;
GRANT SELECT ON FUTURE TABLES IN SCHEMA analytics_db.shared TO ROLE finance_analyst;

-- Data scientists: broad read access, but still not finance schema
GRANT USAGE ON SCHEMA analytics_db.marketing TO ROLE data_scientist;
GRANT USAGE ON SCHEMA analytics_db.shared TO ROLE data_scientist;
GRANT USAGE ON SCHEMA analytics_db.raw_events TO ROLE data_scientist;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics_db.marketing TO ROLE data_scientist;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics_db.shared TO ROLE data_scientist;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics_db.raw_events TO ROLE data_scientist;
GRANT SELECT ON FUTURE TABLES IN SCHEMA analytics_db.marketing TO ROLE data_scientist;
GRANT SELECT ON FUTURE TABLES IN SCHEMA analytics_db.shared TO ROLE data_scientist;
GRANT SELECT ON FUTURE TABLES IN SCHEMA analytics_db.raw_events TO ROLE data_scientist;

Warning: The FUTURE grants trap. Many engineers set up access correctly for existing tables, then create a new table and wonder why users can't see it. GRANT SELECT ON ALL TABLES only applies to tables that exist at the time of the grant. Always pair it with a GRANT SELECT ON FUTURE TABLES statement.

Creating Users and Assigning Roles

USE ROLE SECURITYADMIN;

-- Create a user for a marketing analyst
CREATE USER sarah_chen
  PASSWORD = 'TemporaryPass123!'  -- Force change on first login
  MUST_CHANGE_PASSWORD = TRUE
  DEFAULT_ROLE = marketing_analyst
  DEFAULT_WAREHOUSE = marketing_wh
  DEFAULT_NAMESPACE = analytics_db.marketing;  -- Default schema context

-- Assign the role
GRANT ROLE marketing_analyst TO USER sarah_chen;

Setting DEFAULT_ROLE, DEFAULT_WAREHOUSE, and DEFAULT_NAMESPACE means when Sarah logs in, she's immediately in the right context without having to run USE commands manually.

Row-Level Security in Snowflake with Row Access Policies

Snowflake's row-level security feature is called Row Access Policies. This is where things get powerful. Instead of creating separate views for each team, you write a single policy function that dynamically filters rows based on the current user's role.

Imagine your orders table has a region column. European operations should only see EU orders. North American operations should only see NA orders.

USE ROLE SYSADMIN;

-- Create a mapping table that defines which roles see which regions
CREATE TABLE analytics_db.shared.region_access_map (
  role_name   VARCHAR,
  region      VARCHAR
);

INSERT INTO analytics_db.shared.region_access_map VALUES
  ('marketing_analyst_eu', 'EU'),
  ('marketing_analyst_na', 'NA'),
  ('data_scientist', 'EU'),      -- Data scientists see all
  ('data_scientist', 'NA');

-- Create the Row Access Policy
CREATE OR REPLACE ROW ACCESS POLICY analytics_db.shared.region_filter
  AS (order_region VARCHAR) RETURNS BOOLEAN ->
    -- ACCOUNTADMIN and SYSADMIN always see everything
    IS_ROLE_IN_SESSION('ACCOUNTADMIN')
    OR IS_ROLE_IN_SESSION('SYSADMIN')
    -- Otherwise, check the mapping table
    OR EXISTS (
      SELECT 1
      FROM analytics_db.shared.region_access_map
      WHERE role_name = CURRENT_ROLE()
        AND region = order_region
    );

-- Apply the policy to the orders table on the region column
ALTER TABLE analytics_db.marketing.orders
  ADD ROW ACCESS POLICY analytics_db.shared.region_filter
  ON (region);

Now when Sarah (marketing_analyst_eu) runs SELECT * FROM orders, Snowflake transparently filters the results to EU rows only — without Sarah needing to know that filtering is happening, and without you needing to create a separate view.

---

BigQuery IAM: A Different Philosophy

BigQuery takes a fundamentally different approach than Snowflake. Instead of a custom role hierarchy within the warehouse, BigQuery uses Google Cloud IAM — the same permission system that governs every GCP resource, from Cloud Storage buckets to virtual machines.

This integration is powerful because it means your data access controls fit into your organization's broader cloud governance model. But it also means there are some things Snowflake does in SQL that BigQuery does in the GCP console or via gcloud commands.

IAM Roles vs. BigQuery Roles

In BigQuery, access is managed through IAM roles, which are assigned at three levels:

• Project level — applies to all datasets in the project
• Dataset level — applies to all tables in a specific dataset (equivalent to a schema)
• Table/View level — applies to a specific table or view

The key BigQuery-native IAM roles for data access are:

Role	What it allows
roles/bigquery.admin	Full control, including billing
roles/bigquery.dataEditor	Read + write tables, no schema creation
roles/bigquery.dataViewer	Read tables only
roles/bigquery.jobUser	Run queries (required to actually execute SQL)
roles/bigquery.user	Run queries + view dataset metadata

Critical distinction: In BigQuery, being able to read a table and being able to run a query are two separate permissions. A user needs both bigquery.dataViewer (or equivalent) AND bigquery.jobUser to actually query a table successfully. Forgetting this is one of the most common BigQuery access configuration mistakes.

Granting Dataset-Level Access

In BigQuery, datasets are the equivalent of schemas. Let's set up our analytics teams.

You can do this in the BigQuery Studio UI by navigating to a dataset, clicking the three-dot menu next to its name, selecting "Share" and then "Manage permissions." But let's do this programmatically so it's repeatable:

# Using bq command-line tool

# Grant the marketing analyst group READ access to the marketing dataset
bq add-iam-policy-binding \
  --member="group:marketing-analysts@company.com" \
  --role="roles/bigquery.dataViewer" \
  analytics_project:marketing_dataset

# Grant them the ability to run jobs at the project level
gcloud projects add-iam-policy-binding analytics_project \
  --member="group:marketing-analysts@company.com" \
  --role="roles/bigquery.jobUser"

# Grant finance analysts READ access only to finance dataset
bq add-iam-policy-binding \
  --member="group:finance-analysts@company.com" \
  --role="roles/bigquery.dataViewer" \
  analytics_project:finance_dataset

gcloud projects add-iam-policy-binding analytics_project \
  --member="group:finance-analysts@company.com" \
  --role="roles/bigquery.jobUser"

Using Google Groups (group:) instead of individual users (user:) is best practice. When a new marketing analyst joins, you add them to the Google Group — no IAM changes required.

Row-Level Security in BigQuery with Row Access Policies

BigQuery added native row-level security through Row Access Policies (not to be confused with Snowflake's similarly named feature — they work differently).

Let's apply the same regional filtering scenario:

-- Create a row access policy on the orders table
-- This restricts each group to only their regional data

CREATE ROW ACCESS POLICY marketing_eu_filter
  ON marketing_dataset.orders
  GRANT TO ("group:marketing-analysts-eu@company.com")
  FILTER USING (region = 'EU');

CREATE ROW ACCESS POLICY marketing_na_filter
  ON marketing_dataset.orders
  GRANT TO ("group:marketing-analysts-na@company.com")
  FILTER USING (region = 'NA');

-- Data scientists see everything — grant them access with no filter
CREATE ROW ACCESS POLICY data_science_all_regions
  ON marketing_dataset.orders
  GRANT TO ("group:data-scientists@company.com")
  FILTER USING (TRUE);  -- TRUE means no row is filtered out

Important BigQuery RLS behavior: When row access policies exist on a table, any user NOT explicitly covered by at least one policy sees zero rows — not an error, just empty results. This is a secure-by-default behavior, but it can be deeply confusing during debugging. Always verify which policies cover which principals.

---

Hands-On Exercise

Work through this exercise to cement what you've learned. You can complete the Snowflake portion on a free trial account and the BigQuery portion on the GCP free tier.

Scenario: You work for a retail analytics company. You have sales data that includes a sales_rep_region column. You need to ensure:

• Regional sales managers can only see their own region's data
• A national reporting role can see all data
• Finance users can query the sales totals but not individual transaction details

Snowflake steps:

1. Create roles: regional_manager_east, regional_manager_west, national_reporting, finance_read
2. Create a small sample table:

CREATE TABLE analytics_db.shared.sales_transactions (
  transaction_id    INT,
  sales_rep_region  VARCHAR,
  amount            DECIMAL(10,2),
  rep_name          VARCHAR,
  transaction_date  DATE
);

INSERT INTO analytics_db.shared.sales_transactions VALUES
  (1, 'EAST', 5200.00, 'Jordan Williams', '2024-01-15'),
  (2, 'WEST', 3800.00, 'Alex Rivera', '2024-01-15'),
  (3, 'EAST', 7100.00, 'Casey Chen', '2024-01-16'),
  (4, 'WEST', 2900.00, 'Morgan Kim', '2024-01-16');

3. Create a Row Access Policy that filters by sales_rep_region based on current role
4. Create a secure view for the finance team that only exposes transaction_id, amount, and transaction_date — no rep_name or region-level detail
5. Grant the finance role SELECT on the view, not the underlying table

BigQuery steps:

1. Create a dataset called retail_analytics
2. Create the equivalent sales table and load the sample data
3. Create two row access policies — one for EAST, one for WEST
4. Use a test IAM principal (your own account with a different role) to verify the filtering works

---

Common Mistakes & Troubleshooting

"I granted SELECT on the schema but users still can't query tables." In Snowflake, USAGE on the database AND USAGE on the schema are both required before table-level grants mean anything. The access model is additive and layered — every level of the object hierarchy needs to be unlocked.

"New tables I create aren't accessible to the roles I already set up." You've hit the FUTURE grants issue. Add GRANT SELECT ON FUTURE TABLES IN SCHEMA statements for each role. In Snowflake, go back and run these grants even for schemas that already have ALL TABLES grants.

"BigQuery users can see the dataset but get an access denied error when querying." They have dataViewer but not jobUser. In BigQuery, reading data and running compute jobs are separate permission axes. Grant roles/bigquery.jobUser at the project level.

"Users with a BigQuery row access policy are getting zero results even though they should match." Check whether you have multiple row access policies on the table. BigQuery evaluates each policy independently — a user gets the union of all rows they're allowed to see across policies that name them. If no policy names them at all, they see nothing.

"Snowflake row access policy changes aren't reflecting in query results immediately." Snowflake caches query results. Add a RESULT_SCAN workaround or simply run the query with a session variable change to bust the cache during testing. In production, policy changes propagate within seconds.

"An analyst role has access to a table they shouldn't." Use Snowflake's SHOW GRANTS ON TABLE to list every privilege currently applied to an object. Use SHOW GRANTS TO ROLE <role_name> to see what a role can do. These two commands are your primary debugging tools.

---

Summary & Next Steps

You've covered a lot of ground. Let's recap what you can now do:

In Snowflake, you know how to create a role hierarchy that matches your team structure, grant warehouse access to control compute costs, layer database-schema-table privileges correctly (including the critical FUTURE grants), and implement Row Access Policies for dynamic row-level filtering based on role.

In BigQuery, you understand that access control lives in Google Cloud IAM, that dataset-level grants act like schema-level isolation, that job execution and data reading are separately permissioned, and how to create Row Access Policies that filter data at the row level transparently.

The broader principle you should carry forward: access control is not a one-time task. It's an ongoing process. Teams change, projects expand, new tables get created. Build your access model with that dynamism in mind — use FUTURE grants, use Google Groups, document your role design, and audit regularly.

Where to go next:

• Column-level Security: Both Snowflake (column masking policies) and BigQuery (column-level security with policy tags) let you mask or hide specific columns — great for PII like email addresses or SSNs. This pairs naturally with your row-level security setup.
• Snowflake Data Sharing: Once you have your internal RBAC solid, explore how Snowflake's Secure Data Sharing lets you grant external organizations read access to your data without copying it.
• BigQuery Authorized Views: Rather than raw table access, you can grant access to a curated view — and the underlying table remains inaccessible. This is the BigQuery equivalent of Snowflake's secure views.
• Audit Logging: In Snowflake, query SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY to see who accessed what and when. In BigQuery, Cloud Audit Logs capture all data access events. Set up alerts for unusual access patterns.

Your data warehouse is now a locked building with keycards, not an open office. That's the foundation everything else in your data platform is built on.