3.2 Core Workflows and Decision Points

The System-Defined Role Hierarchy

Snowflake ships with six system-defined roles that cannot be dropped and have built-in privileges. The exam tests their responsibilities and their position in the hierarchy precisely.

The default inheritance is: ACCOUNTADMIN sits above both SYSADMIN and SECURITYADMIN; SECURITYADMIN sits above USERADMIN; and everything rolls up from PUBLIC at the bottom. ACCOUNTADMIN therefore inherits all privileges below it.

Role Inheritance and Custom Roles

Role inheritance means that if role A is granted to role B, then B automatically holds every privilege of A. This is how privileges aggregate. Snowflake's recommended pattern is to build custom roles for business functions (e.g., ANALYST, DATA_ENGINEER), grant object privileges to those custom roles, and then grant the custom roles to SYSADMIN. That way SYSADMIN — and therefore ACCOUNTADMIN — can manage everything, while end users hold only the narrow custom role they need.

Switching and activating roles

A user can be granted many roles but uses one primary (current) role at a time, activated with USE ROLE my_role. Secondary roles can be activated with USE SECONDARY ROLES ALL, letting a session combine privileges across roles. The role that creates an object becomes its owner and holds OWNERSHIP, which can be transferred with GRANT OWNERSHIP.

Standard grant workflow

To give an analyst read access to a schema's tables:

1. USE ROLE USERADMIN; then CREATE ROLE ANALYST;
2. USE ROLE SECURITYADMIN; then GRANT USAGE ON DATABASE sales TO ROLE ANALYST; and GRANT USAGE ON SCHEMA sales.public TO ROLE ANALYST;
3. GRANT SELECT ON ALL TABLES IN SCHEMA sales.public TO ROLE ANALYST;
4. GRANT ROLE ANALYST TO USER jdoe; and GRANT ROLE ANALYST TO ROLE SYSADMIN;

Future Grants and Decision Points

GRANT SELECT ON ALL TABLES IN SCHEMA ... covers tables that exist now. To automatically cover tables created later, you need a future grant:

GRANT SELECT ON FUTURE TABLES IN SCHEMA sales.public TO ROLE ANALYST;

Future grants are a high-frequency exam topic. Remember: a future grant defined at the schema level takes precedence over one defined at the database level for objects in that schema, and ALL + FUTURE together cover both existing and new objects.

Which role for which task — the decision table

ACCOUNTADMIN discipline

Because ACCOUNTADMIN can see billing and every object and can bypass many controls, Snowflake recommends: grant it to as few users as possible (at least two for redundancy), require multi-factor authentication for those users, and avoid using it as a default role for routine work. Many exam stems reward choosing the least-privileged role that still completes the task rather than reaching for ACCOUNTADMIN.

Revoking, Inspecting, and Common Workflow Traps

Grants are reversed with REVOKE. Revoking a privilege from a role removes it from every user and child role that inherited it, which can have a wider blast radius than expected. To inspect the current state, use the metadata commands: SHOW GRANTS TO ROLE analyst (privileges a role holds), SHOW GRANTS TO USER jdoe (roles a user has), and SHOW GRANTS ON TABLE customers (everyone who can touch an object). These commands are themselves test material — know which one answers "who can read this table?" (SHOW GRANTS ON ...) versus "what can this role do?" (SHOW GRANTS TO ROLE ...).

The default-role nuance

Each user has a default role (DEFAULT_ROLE) that activates at login, but a user can USE ROLE to switch to any role granted to them. A user who "cannot see" an object often simply has the wrong current role active — not a missing grant. The fix is USE ROLE, not a new GRANT. Stems frequently bury this distinction.

Workflow checklist

Working this checklist mechanically prevents the two most common mistakes: forgetting the USAGE chain, and forgetting to grant the new custom role up to SYSADMIN so administrators retain control.