Lesson 7.1 — RBAC, the Snowflake Way

You know RBAC in the abstract (K8s, IAM, LDAP groups). Snowflake's twist: privileges are granted to roles, roles are granted to roles OR users, and there's a built-in hierarchy you must understand before touching production.

The built-in role hierarchy

                    ACCOUNTADMIN     ← god-mode. Avoid daily use.
                   /              \
             SECURITYADMIN      SYSADMIN     ← security admin separation
              (grants, users)   (objects, warehouses)
                   │               │
              USERADMIN        (custom roles)
             (create users)    /    │    \
                          ANALYST  ENGINEER  PII_READER
                          /    \
                       Alice  Bob    ← users get roles, not privileges directly

The roles you'll see in every Snowflake account

• ACCOUNTADMIN — everything. Use for setup only, never for regular work. Two people max should have it.
• SECURITYADMIN — manages roles, grants, masking policies. Not the one that creates objects.
• USERADMIN — creates users and roles. Inherits from SECURITYADMIN.
• SYSADMIN — creates databases, warehouses, schemas, tables. This is the "root owner" of your data objects. Most custom roles are granted to SYSADMIN so it has visibility.
• PUBLIC — default role. Everyone gets it. Grant nothing to it beyond trivial metadata.

The pattern enterprises actually use: Functional vs Access roles

Rather than grant privileges directly to "Alice," two layers are used:

-- Access role: can read meter_reads
CREATE ROLE AR_METER_READS_READ;
GRANT USAGE ON DATABASE UTILITY_PROD TO ROLE AR_METER_READS_READ;
GRANT USAGE ON SCHEMA UTILITY_PROD.OPERATIONS TO ROLE AR_METER_READS_READ;
GRANT SELECT ON TABLE UTILITY_PROD.OPERATIONS.METER_READS TO ROLE AR_METER_READS_READ;

-- Functional role: "analyst who works on customer-facing dashboards"
CREATE ROLE FR_ANALYST_CUSTOMER;
GRANT ROLE AR_METER_READS_READ TO ROLE FR_ANALYST_CUSTOMER;
GRANT ROLE AR_CIS_READ TO ROLE FR_ANALYST_CUSTOMER;

-- User gets functional roles only
GRANT ROLE FR_ANALYST_CUSTOMER TO USER alice;

-- Functional roles roll up to SYSADMIN so sysadmin sees everything
GRANT ROLE FR_ANALYST_CUSTOMER TO ROLE SYSADMIN;

Why bother with two layers?

• Decoupled change: new table? Update access role. Alice's job changed? Change functional role. The two don't collide.
• Auditable: "who can read PII?" → SHOW GRANTS OF ROLE AR_CUSTOMER_PII_READ. Clean answer.
• Standard across the industry: this is the pattern Snowflake Security Best Practices prescribes. Interviewers will ask specifically.

Active role vs secondary roles

A user has one active role at a time (the one they use to run a query) but can have many assigned. USE ROLE x; switches. "Secondary roles" (introduced 2022) allow combining grants — useful for dashboards that touch multiple domains.

Auditing grants

SHOW GRANTS TO USER alice;
SHOW GRANTS TO ROLE FR_ANALYST_CUSTOMER;
SHOW GRANTS OF ROLE AR_METER_READS_READ;
SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_USERS WHERE granted_to = 'ALICE';

The ACCOUNT_USAGE views are Snowflake's internal audit trail — 365 days of grant history, query history, access history. Lean on them in compliance reviews.