Snowflake Roles Users and Access Control

Access control in Snowflake determines who can do what with which objects. Every query, every data load, every schema creation — Snowflake checks permissions before executing any command. A well-designed access control model keeps sensitive data private, gives every team exactly the access they need, and prevents accidental or malicious data changes. A poorly designed model either locks people out of data they need or exposes data to people who should not see it.

Snowflake uses Role-Based Access Control (RBAC). Instead of granting permissions directly to individual users, you grant permissions to roles, then assign roles to users. When a user logs in and activates a role, they inherit all the permissions that role holds.

The RBAC Model: An Office Badge Analogy

Think of roles as office access badges. The company does not program individual names into every door — that would require updating hundreds of doors every time someone joins or leaves. Instead, the company creates badge types: Employee, Manager, Security, Executive. Each badge type opens specific doors. When Bob joins as a Manager, he gets a Manager badge. When he transfers to a different team, the IT team updates his badge type — not the individual doors.

RBAC DIAGRAM
=============

USERS                 ROLES                  PRIVILEGES ON OBJECTS
-----                 -----                  ---------------------
Alice  -----> ANALYST_ROLE -----> SELECT on ANALYTICS.FACT_SALES
Bob    --/                  \---> SELECT on ANALYTICS.DIM_CUSTOMER
Carol  -----> ENGINEER_ROLE ---> SELECT, INSERT, UPDATE on RAW_DATA.*
Dave   --/                  \---> CREATE TABLE in STAGING schema
Eve    -----> ADMIN_ROLE   ----> All privileges on RETAIL_DB

When Alice runs a query:
  Snowflake checks: Does ANALYST_ROLE have SELECT on the queried table?
  YES: query runs
  NO:  "Insufficient privileges" error

System-Defined Roles in Every Snowflake Account

Snowflake creates five built-in roles in every new account. These form a hierarchy where higher roles inherit all permissions of lower roles in their lineage.

SYSTEM ROLE HIERARCHY
======================

ACCOUNTADMIN
  (Full control: billing, security, all account settings, all objects)
  |
  +-- SYSADMIN
  |   (Create/manage databases, schemas, tables, warehouses)
  |   (Best role for day-to-day data engineering work)
  |
  +-- SECURITYADMIN
  |   (Create/manage users, roles, network policies)
  |   (Manages who can access what, not the data itself)
  |
  +-- USERADMIN
  |   (Subset of SECURITYADMIN — create users and roles only)
  |
  +-- PUBLIC
      (Every user has this role automatically)
      (Grants access to PUBLIC schema objects shared company-wide)

IMPORTANT RULES:
  Always grant SYSADMIN or custom roles to SYSADMIN
  Never use ACCOUNTADMIN for routine work — use it only for account config
  Create custom roles for team-specific permissions rather than granting SYSADMIN

Creating Custom Roles and Users

-- As USERADMIN or SECURITYADMIN:

-- Create custom roles
CREATE ROLE ANALYST;
CREATE ROLE SENIOR_ANALYST;
CREATE ROLE DATA_ENGINEER;
CREATE ROLE DATA_SCIENTIST;
CREATE ROLE BI_VIEWER;

-- Build role hierarchy (senior roles inherit junior role permissions)
GRANT ROLE ANALYST        TO ROLE SENIOR_ANALYST;
GRANT ROLE SENIOR_ANALYST TO ROLE DATA_SCIENTIST;
-- DATA_SCIENTIST now has all SENIOR_ANALYST and ANALYST permissions plus its own

-- Connect custom roles to SYSADMIN (best practice)
GRANT ROLE DATA_ENGINEER  TO ROLE SYSADMIN;
GRANT ROLE ANALYST        TO ROLE SYSADMIN;
-- SYSADMIN can now manage objects owned by these roles

-- Create users
CREATE USER alice_chen
  LOGIN_NAME      = 'alice.chen@company.com'
  DISPLAY_NAME    = 'Alice Chen'
  DEFAULT_ROLE    = ANALYST
  DEFAULT_WAREHOUSE = REPORTING_WH
  DEFAULT_NAMESPACE = RETAIL_DB.ANALYTICS
  PASSWORD        = 'TempPass2024!'
  MUST_CHANGE_PASSWORD = TRUE;

CREATE USER bob_kumar
  LOGIN_NAME    = 'bob.kumar@company.com'
  DEFAULT_ROLE  = DATA_ENGINEER
  DEFAULT_WAREHOUSE = ETL_WH
  PASSWORD      = 'TempPass2024!'
  MUST_CHANGE_PASSWORD = TRUE;

-- Assign roles to users
GRANT ROLE ANALYST        TO USER alice_chen;
GRANT ROLE SENIOR_ANALYST TO USER alice_chen;   -- Alice gets both roles
GRANT ROLE DATA_ENGINEER  TO USER bob_kumar;

Granting Privileges on Objects

Privileges define what actions a role can perform on a specific object. Snowflake follows a minimum-privilege principle: grant only the privileges a role actually needs to do its job.

-- Grant warehouse usage (required to run queries)
GRANT USAGE ON WAREHOUSE REPORTING_WH TO ROLE ANALYST;
GRANT USAGE ON WAREHOUSE ETL_WH TO ROLE DATA_ENGINEER;

-- Grant database and schema access (required before granting table access)
GRANT USAGE ON DATABASE RETAIL_DB TO ROLE ANALYST;
GRANT USAGE ON SCHEMA RETAIL_DB.ANALYTICS TO ROLE ANALYST;
GRANT USAGE ON SCHEMA RETAIL_DB.RAW_DATA  TO ROLE DATA_ENGINEER;

-- Grant table-level privileges
GRANT SELECT ON ALL TABLES IN SCHEMA RETAIL_DB.ANALYTICS TO ROLE ANALYST;
GRANT SELECT ON ALL VIEWS  IN SCHEMA RETAIL_DB.ANALYTICS TO ROLE ANALYST;

-- Future grants (auto-grant on newly created objects)
GRANT SELECT ON FUTURE TABLES IN SCHEMA RETAIL_DB.ANALYTICS TO ROLE ANALYST;
GRANT SELECT ON FUTURE VIEWS  IN SCHEMA RETAIL_DB.ANALYTICS TO ROLE ANALYST;
-- Without FUTURE GRANTS: every new table requires a manual privilege grant

-- Grant write privileges to DATA_ENGINEER
GRANT ALL PRIVILEGES ON SCHEMA RETAIL_DB.RAW_DATA  TO ROLE DATA_ENGINEER;
GRANT ALL PRIVILEGES ON SCHEMA RETAIL_DB.STAGING    TO ROLE DATA_ENGINEER;
GRANT CREATE TABLE ON SCHEMA RETAIL_DB.STAGING      TO ROLE DATA_ENGINEER;

-- Show what privileges a role has
SHOW GRANTS TO ROLE ANALYST;

-- Show all privileges on a specific object
SHOW GRANTS ON TABLE RETAIL_DB.ANALYTICS.FACT_SALES;

Complete Privilege Reference

OBJECT TYPE   PRIVILEGE          ALLOWS
-----------   ---------          ------
DATABASE      USAGE              See the database and its schemas
              CREATE SCHEMA      Create new schemas in the database

SCHEMA        USAGE              See the schema and its objects
              CREATE TABLE       Create tables in the schema
              CREATE VIEW        Create views in the schema
              CREATE STAGE       Create stages in the schema

TABLE         SELECT             Query the table
              INSERT             Add rows to the table
              UPDATE             Modify existing rows
              DELETE             Remove rows from the table
              TRUNCATE           Remove all rows (fast delete)
              REFERENCES         Reference in foreign key (Snowflake allows no-enforce FK)

VIEW          SELECT             Query the view

WAREHOUSE     USAGE              Use the warehouse to run queries
              MODIFY             Resize, suspend, resume the warehouse
              MONITOR            View warehouse credit usage

STAGE         USAGE              Use the stage in COPY INTO
              READ               List files in the stage (internal stages)
              WRITE              Upload files to the stage (PUT)

Privilege Inheritance Through Role Hierarchy

PRIVILEGE INHERITANCE EXAMPLE
================================

Role: ANALYST
  Grants: SELECT on ANALYTICS.FACT_SALES
          SELECT on ANALYTICS.DIM_CUSTOMER

Role: SENIOR_ANALYST (ANALYST granted to SENIOR_ANALYST)
  Inherits: All ANALYST privileges
  Additional: SELECT on ANALYTICS.FACT_ORDERS_DETAIL
              SELECT on RAW_DATA.ORDERS_RAW

Role: DATA_SCIENTIST (SENIOR_ANALYST granted to DATA_SCIENTIST)
  Inherits: All SENIOR_ANALYST privileges (which include ANALYST)
  Additional: SELECT on ANALYTICS.ML_FEATURE_STORE
              CREATE TABLE in ANALYTICS schema

User Priya (has DATA_SCIENTIST role):
  Can query: FACT_SALES, DIM_CUSTOMER, FACT_ORDERS_DETAIL,
             ORDERS_RAW, ML_FEATURE_STORE
  Can create tables in: ANALYTICS schema
  Cannot: INSERT, UPDATE, DELETE on any table (not granted)

Switching Roles in a Session

-- See current active role
SELECT CURRENT_ROLE();

-- Switch role within a session (SQL)
USE ROLE ANALYST;
USE ROLE DATA_ENGINEER;
USE ROLE ACCOUNTADMIN;

-- Check all roles available to current user
SHOW ROLES;

-- In Snowsight: click your name (bottom-left) → Switch Role

Service Accounts and Automated Users

Automated tools like dbt, Airflow, Fivetran, or custom Python scripts connect to Snowflake using service accounts — dedicated users created for machine-to-machine access. Service accounts should have minimal privileges, dedicated roles, and rotate passwords regularly.

-- Create a service account for dbt
CREATE USER dbt_service_acct
  LOGIN_NAME       = 'dbt_service'
  DEFAULT_ROLE     = DBT_ROLE
  DEFAULT_WAREHOUSE = TRANSFORM_WH
  PASSWORD         = 'GeneratedSecurePassword123!'
  COMMENT          = 'Service account for dbt transformation jobs';

-- Create a minimal role for dbt
CREATE ROLE DBT_ROLE;
GRANT USAGE  ON WAREHOUSE TRANSFORM_WH   TO ROLE DBT_ROLE;
GRANT USAGE  ON DATABASE  RETAIL_DB      TO ROLE DBT_ROLE;
GRANT USAGE  ON ALL SCHEMAS IN DATABASE RETAIL_DB TO ROLE DBT_ROLE;
GRANT SELECT ON ALL TABLES IN DATABASE RETAIL_DB  TO ROLE DBT_ROLE;
GRANT ALL PRIVILEGES ON SCHEMA RETAIL_DB.ANALYTICS TO ROLE DBT_ROLE;
GRANT ROLE DBT_ROLE TO USER dbt_service_acct;

-- Use RSA key pair authentication instead of password for service accounts
ALTER USER dbt_service_acct SET RSA_PUBLIC_KEY = 'MIIBIjANBgkq...';

Monitoring Access and Auditing

-- View all users in the account
SHOW USERS;

-- See which roles a user has
SHOW GRANTS TO USER alice_chen;

-- See all users who have a specific role
SHOW GRANTS OF ROLE ANALYST;

-- Audit login history (who logged in, from where, when)
SELECT *
FROM SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY
WHERE event_timestamp >= DATEADD('day', -7, CURRENT_TIMESTAMP())
ORDER BY event_timestamp DESC;

-- Audit privilege grants (who was granted what, by whom)
SELECT *
FROM SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_ROLES
WHERE grantee_name = 'ANALYST'
ORDER BY created_on DESC;

Key Points

  • Snowflake uses Role-Based Access Control (RBAC) — grant permissions to roles, assign roles to users, never grant permissions directly to users
  • Five system roles exist in every account: ACCOUNTADMIN, SYSADMIN, SECURITYADMIN, USERADMIN, and PUBLIC — use SYSADMIN for routine data work
  • Always create custom roles for business teams (ANALYST, DATA_ENGINEER, BI_VIEWER) rather than assigning SYSADMIN to regular users
  • Use FUTURE GRANTS to automatically apply privileges on new objects created in a schema — otherwise every new table requires a manual grant
  • Role hierarchy enables privilege inheritance — a role granted to another role passes all its privileges upward in the hierarchy
  • Service accounts for automated tools should have the minimum privileges needed and use RSA key pair authentication instead of passwords
  • Monitor access with LOGIN_HISTORY and GRANTS_TO_ROLES views in SNOWFLAKE.ACCOUNT_USAGE for security auditing
Previous lessons
Back to courses
Next lessons

Leave a Comment

Your email address will not be published. Required fields are marked *