All topics
54 / 66
Snowflake

RBAC & Discretionary Access Control in Snowflake

Design a secure, scalable permission model using roles and grants.

In Snowflake, users have no privileges. Roles do. Users are granted roles, and every permission arrives that way.

Roles are like keyrings: instead of cutting a new key for every employee for every door (users to objects directly), you hand employees a keyring (role) that already opens the doors relevant to their job, and promoting someone just means handing them an additional keyring.

Key Concepts

1
    privilege -> role -> user
2
That single rule is what makes the model manageable: when someone changes team, you change which roles they hold, not which permissions they have.
3
Roles inherit from each other. Granting role A to role B means B holds everything A does:
4
    ACCOUNTADMIN          full control of the account
      ├── SECURITYADMIN   manages users and grants
      │     └── USERADMIN
      └── SYSADMIN        parent of your custom roles
            ├── ETL_ROLE
            └── ANALYST_ROLE
    PUBLIC                automatically held by everyone
5
The convention is to hang your custom roles under SYSADMIN, so that role can administer everything the business owns without anyone needing ACCOUNTADMIN for daily work.
SYSADMINACCOUNTADMIN
6
Ownership is the discretionary part. Every object has an owning role, and that role may grant access on it to others without asking an administrator. Structured hierarchy, plus owner discretion — hence RBAC with DAC layered on.
7
It also means who creates an object matters. Create a table while using ACCOUNTADMIN and it is owned by ACCOUNTADMIN, so nobody else can manage it — a very common way for permissions to end up broken.
ACCOUNTADMIN
8
Two rules that prevent most of the mess.Nobody works as ACCOUNTADMIN. Reserve it for account-level settings, give it to two people, and require switching to it deliberately. It can see billing, change security settings and drop anything.
ACCOUNTADMIN
9
Separate access roles from functional roles. One set of roles describes access to objects (ORDERS_READ, ORDERS_WRITE); another describes jobs (ANALYST, ETL). Grant the access roles to the functional roles.
ORDERS_READORDERS_WRITEANALYSTETL
10
Without that split, every new dataset means editing every person's role, and permissions drift into a state nobody can audit.
11
Future grants stop the endless catching-up. A grant applies to objects that exist now. Tomorrow's table is not covered:
12
    GRANT SELECT ON FUTURE TABLES IN SCHEMA analytics TO ROLE ANALYST;
13
Now every table created there later is readable automatically. Any pipeline that creates tables needs this, or somebody is granting access by hand every morning.
14
Checking what someone can actually do. SHOW GRANTS TO ROLE x and SHOW GRANTS ON TABLE y are the direct answers, and the ACCOUNT_USAGE.GRANTS_TO_ROLES view is how you audit the whole account. Worth knowing, because inherited privileges through a role hierarchy are not obvious by reading the role names.
SHOW GRANTS TO ROLE xSHOW GRANTS ON TABLE yACCOUNT_USAGE.GRANTS_TO_ROLES
15
What the interviewer is probing.1. "How does privilege inheritance work?" Probing: the model. Stalls: "Users get grants." Moves up: privileges go to roles, roles are granted to other roles forming a hierarchy, and users activate one role at a time.
16
2. "What is the usual role design?" Probing: the pattern. Stalls: "Admin and user." *Moves up:* functional roles granted to users, access roles holding object privileges, and functional roles granted the access roles.
17
3. "Why is future grants important?" Probing: the operational detail. Stalls: "Convenience." Moves up: without it, every new table needs grants re-applied, which is how permission drift happens.
18
4. "What should ACCOUNTADMIN be used for?" Probing: the discipline. Stalls: "Daily work." Moves up: almost nothing day to day — it should be a small, audited set of people with MFA, not the role pipelines run as.
Use cases:rbacaccess-controlsecuritygovernance