User Roles, Permissions, RBAC in Snowflake

A simple, story-driven guide to understanding Snowflake access control

Imagine Snowflake as a giant digital office building.

Inside the building:

• Users = People working in the office
• Roles = Keys to the rooms
• Permissions = What each key unlocks
• RBAC (Role-Based Access Control) = The security system that manages all keys

Snowflake doesn’t give permissions directly to people.
Instead, permissions are given to roles, and users are assigned roles.

This keeps the building safe, clean, and easy to manage.

Let’s walk through Snowflake RBAC in the simplest, clearest way.

---

🔑 1. What Is RBAC in Snowflake?

RBAC = Role-Based Access Control
A security model where:

Users → Assigned Roles → Roles Granted Permissions

You never grant privileges to users directly.

Why?

Because:

• Users come and go
• Roles stay forever
• Permissions remain organized
• Audits remain clean

Snowflake’s RBAC model is widely considered one of the cleanest in the industry.

---

👥 2. Important Terms You Must Know

Users

People or services connecting to Snowflake.

Roles

The main objects that hold permissions.

Examples:

• SYSADMIN
• USERADMIN
• SECURITYADMIN
• DATA_ENGINEER
• BI_ANALYST

Grants

Permissions attached to roles.

Examples:

• SELECT
• INSERT
• CREATE TABLE
• USAGE
• OPERATE

Role Hierarchy

Roles can be granted to other roles (like a family tree).

---

🏛 3. The Three Most Important Built-In Roles

🟦 1. SYSADMIN

Builds databases, schemas, and tables.
Owns most data objects.

Used by:

• Data engineers
• Data architects

🟩 2. SECURITYADMIN

Manages:

• Roles
• Grants
• Permissions

Used by:

• Security teams
• Administrators

🟧 3. USERADMIN

Manages:

• Creating users
• Assigning roles

Used by:

• HR IT team
• Platform engineers

These roles are separate so that no single person has all power — governance done right.

---

🗂 4. Custom Roles — The Real Power of Snowflake

Built-in roles are strong, but real companies use custom roles.

Examples:

• ROLE_DATA_ENGINEER
• ROLE_ANALYST
• ROLE_MARKETING_USER
• ROLE_ETL_PIPELINE
• ROLE_READONLY

You create custom roles to build a clean permission structure.

Example: Create a custom role

CREATE ROLE ROLE_ANALYST;
GRANT ROLE ROLE_ANALYST TO USER JOHN;

---

🛡 5. How Permissions Work (Simple Explanation)

Think of permissions like keys:

Database-level keys

• USAGE
• CREATE SCHEMA

Schema-level keys

• USAGE
• CREATE TABLE
• CREATE VIEW

Table-level keys

• SELECT
• INSERT
• UPDATE
• DELETE
• TRUNCATE

Example Grant

GRANT SELECT ON TABLE SALES_DB.ANALYTICS.SALES TO ROLE ROLE_ANALYST;

Roles collect keys. Users collect roles. This is RBAC in action.

---

🧱 6. Real Company Example: A Clean RBAC Structure

Let’s imagine a mid-size company with 3 teams:

✔ Team 1: Data Engineering

Needs:

• CREATE TABLE
• INSERT/UPDATE
• Pipeline execution
• Warehouse usage

✔ Team 2: BI/Analytics

Needs:

• SELECT access
• Read-only dashboards

✔ Team 3: Executives

Needs:

• Limited SELECT on curated data
• No raw data access

Now let’s structure it using RBAC:

---

🧩 Step-by-Step Real RBAC Setup

👣 Step 1 — Create Custom Roles

CREATE ROLE ROLE_DATA_ENGINEER;
CREATE ROLE ROLE_ANALYST;
CREATE ROLE ROLE_EXECUTIVE;
CREATE ROLE ROLE_READONLY;

---

👣 Step 2 — Assign Users to Roles

GRANT ROLE ROLE_ANALYST TO USER ANALYST_1;
GRANT ROLE ROLE_DATA_ENGINEER TO USER DE_1;
GRANT ROLE ROLE_EXECUTIVE TO USER CEO;

---

👣 Step 3 — Grant Schema & Table Permissions

Data Engineers (full control on RAW & STAGE)

GRANT USAGE ON DATABASE RAW_DB TO ROLE ROLE_DATA_ENGINEER;
GRANT USAGE ON SCHEMA RAW_DB.RAW TO ROLE ROLE_DATA_ENGINEER;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA RAW_DB.RAW TO ROLE ROLE_DATA_ENGINEER;

Analysts (read curated only)

GRANT USAGE ON DATABASE PROD_DB TO ROLE ROLE_ANALYST;
GRANT USAGE ON SCHEMA PROD_DB.ANALYTICS TO ROLE ROLE_ANALYST;
GRANT SELECT ON ALL TABLES IN SCHEMA PROD_DB.ANALYTICS TO ROLE ROLE_ANALYST;

Executives (read from reporting schema only)

GRANT SELECT ON ALL TABLES IN SCHEMA PROD_DB.REPORTING TO ROLE ROLE_EXECUTIVE;

---

👣 Step 4 — Assign Warehouses to Roles

Roles need warehouse access to run queries.

GRANT USAGE ON WAREHOUSE WH_ANALYTICS TO ROLE ROLE_ANALYST;
GRANT USAGE ON WAREHOUSE WH_ETL TO ROLE ROLE_DATA_ENGINEER;

---

🏰 7. Role Hierarchy — Building the Security Pyramid

Most companies create a simple pyramid like this:

ORGADMIN
│
└── ACCOUNTADMIN
    │
    ├── SECURITYADMIN
    │     └── USERADMIN
    │
    └── SYSADMIN
          └── <Custom Department Roles>
                ├── ROLE_DATA_ENGINEER
                ├── ROLE_ANALYST
                ├── ROLE_MARKETING
                └── ROLE_EXECUTIVE

Meaning:

• Admins manage roles
• Sysadmins own objects
• Teams get custom roles

Super clean. Super scalable.

---

🧠 8. Best Practices

✔ Use custom roles for all business teams

Do not assign SYSADMIN to everyone.

✔ Never grant privileges directly to a user

Always use roles.

✔ Use role hierarchy to simplify management

Parent roles → child roles → users.

✔ Separate “read” and “write” permissions

Avoid data accidents.

✔ Create dedicated service roles for pipelines

Avoid using human roles for automation.

✔ Periodically audit roles & unused privileges

Security stays tight.

---

🎯 One-Sentence Summary

Snowflake RBAC gives permissions to roles—not users—making access control clean, scalable, and secure across your entire organization.

---

🚀 Next Topic

👉 SQL Basics in Snowflake — SELECT, FILTER, GROUP BY