Setting Up Role-Based Access Control in Snowflake: A Template for Growing Teams
Last updated: April 2025
Quick answer: Create functional roles (DATA_ENGINEER, DATA_ANALYST, etc.) under SYSADMIN, grant specific privileges per role, use future grants so new tables auto-inherit permissions, and never use ACCOUNTADMIN for daily work. This article includes a complete SQL script you can run to set up a multi-team RBAC environment from scratch.
Snowflake's Built-In Role Hierarchy
Every Snowflake account ships with five system-defined roles. Understanding the hierarchy is the first step to building your own:
- ORGADMIN - Manages organization-level operations across multiple Snowflake accounts. Most teams never use this directly.
- ACCOUNTADMIN - The top-level role within an account. Can do everything: create warehouses, manage billing, read all data. This is your break-glass role, not your daily driver.
- SECURITYADMIN - Manages users, roles, and grants. Can create new roles and assign privileges. Use this for identity and access management tasks.
- SYSADMIN - Creates and manages databases, schemas, warehouses, and other objects. All custom roles should roll up to SYSADMIN so it can manage the objects they create.
- PUBLIC - Every user automatically has this role. Grant the absolute minimum here (usually nothing).
Why You Should Never Use ACCOUNTADMIN Daily
Three concrete reasons. First, blast radius: every accidental DROP DATABASE hits every database in the account. Second, audit trail: when 8 people share ACCOUNTADMIN, your audit logs show the same role for every action - you can't tell who did what. Third, compliance: SOC 2 and HIPAA auditors specifically look for least-privilege enforcement. If your data engineers are running queries as ACCOUNTADMIN, that's a finding.
Designing Your Role Hierarchy
Here's a role template that works for most data teams with 5–50 people. You'll create functional roles for each team function and grant them to SYSADMIN so the hierarchy stays clean:
| Role | Purpose | Typical Privileges |
|---|---|---|
DATA_ENGINEER | Build and maintain pipelines | CREATE TABLE, INSERT, UPDATE, DELETE, CREATE TASK, CREATE STREAM on assigned schemas |
DATA_ANALYST | Query and report on data | SELECT on analytics schemas, USAGE on BI warehouse |
DATA_SCIENTIST | Explore data, build models | SELECT on analytics + raw schemas, USAGE on ML warehouse, CREATE STAGE |
BI_DEVELOPER | Build dashboards and semantic layers | SELECT on analytics schemas, USAGE on BI warehouse |
APP_SERVICE_ACCOUNT | Application-level read access | SELECT on specific views only, USAGE on app warehouse with tight resource monitor |
Complete SQL Setup Script
Run this as SECURITYADMIN (for role and grant management) and SYSADMIN (for database/warehouse creation). The script sets up a production-ready RBAC environment:
-- ============================================ -- Step 1: Create functional roles (as SECURITYADMIN) -- ============================================ USE ROLE SECURITYADMIN; CREATE ROLE IF NOT EXISTS DATA_ENGINEER; CREATE ROLE IF NOT EXISTS DATA_ANALYST; CREATE ROLE IF NOT EXISTS DATA_SCIENTIST; CREATE ROLE IF NOT EXISTS BI_DEVELOPER; CREATE ROLE IF NOT EXISTS APP_SERVICE_ACCOUNT; -- Grant all custom roles to SYSADMIN (maintains hierarchy) GRANT ROLE DATA_ENGINEER TO ROLE SYSADMIN; GRANT ROLE DATA_ANALYST TO ROLE SYSADMIN; GRANT ROLE DATA_SCIENTIST TO ROLE SYSADMIN; GRANT ROLE BI_DEVELOPER TO ROLE SYSADMIN; GRANT ROLE APP_SERVICE_ACCOUNT TO ROLE SYSADMIN; -- ============================================ -- Step 2: Create warehouses (as SYSADMIN) -- ============================================ USE ROLE SYSADMIN; CREATE WAREHOUSE IF NOT EXISTS ETL_WH WAREHOUSE_SIZE = 'MEDIUM' AUTO_SUSPEND = 60 AUTO_RESUME = TRUE; CREATE WAREHOUSE IF NOT EXISTS BI_WH WAREHOUSE_SIZE = 'SMALL' AUTO_SUSPEND = 120 AUTO_RESUME = TRUE; CREATE WAREHOUSE IF NOT EXISTS ML_WH WAREHOUSE_SIZE = 'LARGE' AUTO_SUSPEND = 60 AUTO_RESUME = TRUE; CREATE WAREHOUSE IF NOT EXISTS APP_WH WAREHOUSE_SIZE = 'XSMALL' AUTO_SUSPEND = 60 AUTO_RESUME = TRUE; -- ============================================ -- Step 3: Grant warehouse access -- ============================================ USE ROLE SECURITYADMIN; GRANT USAGE ON WAREHOUSE ETL_WH TO ROLE DATA_ENGINEER; GRANT USAGE ON WAREHOUSE BI_WH TO ROLE DATA_ANALYST; GRANT USAGE ON WAREHOUSE BI_WH TO ROLE BI_DEVELOPER; GRANT USAGE ON WAREHOUSE ML_WH TO ROLE DATA_SCIENTIST; GRANT USAGE ON WAREHOUSE APP_WH TO ROLE APP_SERVICE_ACCOUNT; -- ============================================ -- Step 4: Create databases and schemas (as SYSADMIN) -- ============================================ USE ROLE SYSADMIN; CREATE DATABASE IF NOT EXISTS RAW_DB; CREATE DATABASE IF NOT EXISTS ANALYTICS_DB; CREATE SCHEMA IF NOT EXISTS RAW_DB.INGESTION; CREATE SCHEMA IF NOT EXISTS RAW_DB.STAGING; CREATE SCHEMA IF NOT EXISTS ANALYTICS_DB.MARTS; CREATE SCHEMA IF NOT EXISTS ANALYTICS_DB.REPORTS; -- ============================================ -- Step 5: Grant database and schema privileges -- ============================================ USE ROLE SECURITYADMIN; -- DATA_ENGINEER: full access to RAW_DB, read + create on ANALYTICS_DB GRANT USAGE ON DATABASE RAW_DB TO ROLE DATA_ENGINEER; GRANT USAGE ON ALL SCHEMAS IN DATABASE RAW_DB TO ROLE DATA_ENGINEER; GRANT CREATE TABLE, CREATE VIEW, CREATE STREAM, CREATE TASK ON SCHEMA RAW_DB.INGESTION TO ROLE DATA_ENGINEER; GRANT CREATE TABLE, CREATE VIEW ON SCHEMA RAW_DB.STAGING TO ROLE DATA_ENGINEER; GRANT USAGE ON DATABASE ANALYTICS_DB TO ROLE DATA_ENGINEER; GRANT USAGE ON ALL SCHEMAS IN DATABASE ANALYTICS_DB TO ROLE DATA_ENGINEER; GRANT CREATE TABLE, CREATE VIEW ON SCHEMA ANALYTICS_DB.MARTS TO ROLE DATA_ENGINEER; -- DATA_ANALYST: read-only on analytics GRANT USAGE ON DATABASE ANALYTICS_DB TO ROLE DATA_ANALYST; GRANT USAGE ON ALL SCHEMAS IN DATABASE ANALYTICS_DB TO ROLE DATA_ANALYST; GRANT SELECT ON ALL TABLES IN SCHEMA ANALYTICS_DB.MARTS TO ROLE DATA_ANALYST; GRANT SELECT ON ALL VIEWS IN SCHEMA ANALYTICS_DB.REPORTS TO ROLE DATA_ANALYST; -- ============================================ -- Step 6: Future grants (auto-apply to new objects) -- ============================================ GRANT SELECT ON FUTURE TABLES IN SCHEMA ANALYTICS_DB.MARTS TO ROLE DATA_ANALYST; GRANT SELECT ON FUTURE VIEWS IN SCHEMA ANALYTICS_DB.REPORTS TO ROLE DATA_ANALYST; GRANT SELECT ON FUTURE TABLES IN SCHEMA ANALYTICS_DB.MARTS TO ROLE BI_DEVELOPER; GRANT SELECT ON FUTURE VIEWS IN SCHEMA ANALYTICS_DB.REPORTS TO ROLE BI_DEVELOPER; -- DATA_SCIENTIST: read on both raw and analytics GRANT USAGE ON DATABASE RAW_DB TO ROLE DATA_SCIENTIST; GRANT USAGE ON ALL SCHEMAS IN DATABASE RAW_DB TO ROLE DATA_SCIENTIST; GRANT SELECT ON FUTURE TABLES IN SCHEMA RAW_DB.STAGING TO ROLE DATA_SCIENTIST; GRANT SELECT ON FUTURE TABLES IN SCHEMA ANALYTICS_DB.MARTS TO ROLE DATA_SCIENTIST; -- ============================================ -- Step 7: Assign roles to users -- ============================================ GRANT ROLE DATA_ENGINEER TO USER jane_engineer; GRANT ROLE DATA_ANALYST TO USER bob_analyst; GRANT ROLE DATA_SCIENTIST TO USER alice_ml;
Future Grants: Don't Skip This
Without future grants, every new table you create requires a manual GRANT before analysts can see it. In practice, this means someone creates a table, an analyst files a ticket, and 3 days later they can finally query it. Future grants fix this:
-- Every new table in MARTS is automatically SELECTable by analysts GRANT SELECT ON FUTURE TABLES IN SCHEMA analytics_db.marts TO ROLE DATA_ANALYST;
Future grants apply to objects created after the grant is set. They don't retroactively apply to existing objects. So run both GRANT SELECT ON ALL TABLES (for existing) and GRANT SELECT ON FUTURE TABLES (for new ones).
Managed Access Schemas
In a regular schema, whoever creates a table can grant access to it. This leads to sprawl: an engineer creates a staging table and grants SELECT to a random user, bypassing your role hierarchy entirely.
Managed access schemas fix this. In a managed access schema, only the schema owner can grant privileges:
CREATE SCHEMA analytics_db.secure_marts WITH MANAGED ACCESS; -- Now only the schema owner (the role that ran this CREATE) -- can grant SELECT on tables in secure_marts. -- Table creators cannot grant access themselves.
Service Account Best Practices
Service accounts (for BI tools, applications, ETL orchestrators) need special attention:
- Create a dedicated role per service account. Don't reuse DATA_ANALYST for your Tableau service user.
- Grant SELECT only on specific views, not raw tables. Views let you control exactly which columns and rows the service can access.
- Attach a resource monitor to the service account's warehouse to prevent runaway queries from burning credits.
- Use key-pair authentication instead of passwords for programmatic access.
- Set a network policy to restrict the IP addresses the service account can connect from.
The ACCOUNTADMIN Ownership Gotcha
This trips up nearly every team at least once. If you're logged in as ACCOUNTADMIN and run CREATE DATABASE analytics_db, that database is owned by ACCOUNTADMIN. SYSADMIN can't see it, can't manage it, and can't grant access to it - even though SYSADMIN is supposed to own all databases.
The fix: always switch to SYSADMIN before creating databases and schemas. If you've already created objects as ACCOUNTADMIN, transfer ownership:
GRANT OWNERSHIP ON DATABASE analytics_db TO ROLE SYSADMIN COPY CURRENT GRANTS;
Key Takeaways
- Reserve ACCOUNTADMIN for break-glass scenarios. Daily work should use functional roles under SYSADMIN.
- Create one role per team function (DATA_ENGINEER, DATA_ANALYST, etc.) and grant all custom roles to SYSADMIN.
- Use future grants on every schema to auto-apply permissions to new tables and views.
- Use managed access schemas to prevent table creators from granting ad-hoc access.
- Always create databases and warehouses as SYSADMIN, not ACCOUNTADMIN.
- Lock down service accounts with dedicated roles, view-only access, resource monitors, key-pair auth, and network policies.
Frequently Asked Questions
Q: Why shouldn't I use ACCOUNTADMIN for daily work in Snowflake?
ACCOUNTADMIN has unrestricted access to every object in the account. Using it daily violates least-privilege principles, increases the blast radius of mistakes, and makes audit trails meaningless since every action appears under the same all-powerful role.
Q: What are future grants in Snowflake?
Future grants automatically apply privileges to objects that don't exist yet. For example, GRANT SELECT ON FUTURE TABLES IN SCHEMA analytics TO ROLE DATA_ANALYST ensures every new table created in that schema automatically gets SELECT access for the DATA_ANALYST role.
Q: What is a managed access schema in Snowflake?
In a managed access schema, only the schema owner can grant privileges on objects within it. Individual table creators cannot grant access to their own tables, centralizing access control and preventing ad-hoc permission sprawl.
Q: What happens when I create a database as ACCOUNTADMIN?
The database is owned by ACCOUNTADMIN, not SYSADMIN. This means SYSADMIN cannot manage it or grant access to it. Best practice is to always create databases as SYSADMIN, or immediately transfer ownership after creation.