System-defined roles (ACCOUNTADMIN, SYSADMIN, etc.) in Snowflake - Step-by-Step Execution

Process Flow - System-defined roles (ACCOUNTADMIN, SYSADMIN, etc.)

Start: User logs in

↓

Assign system-defined role

↓

Role grants specific privileges

↓

User performs actions

↓

System checks role privileges

↓

Allow or deny action

↓

End

User logs in and is assigned a system-defined role. The role grants specific privileges. When the user acts, the system checks these privileges to allow or deny actions.

Execution Sample

Snowflake

USE ROLE ACCOUNTADMIN;
SHOW USERS;
USE ROLE SYSADMIN;
CREATE WAREHOUSE mywh;
USE ROLE PUBLIC;
SELECT CURRENT_ROLE();

This code switches roles and performs actions allowed by each role, showing how privileges differ.

Process Table

Step	Action	Role Used	Privileges Checked	Result
1	USE ROLE ACCOUNTADMIN	ACCOUNTADMIN	Full admin privileges	Role switched to ACCOUNTADMIN
2	SHOW USERS	ACCOUNTADMIN	Can view all users	List of users displayed
3	USE ROLE SYSADMIN	SYSADMIN	Manage warehouses, databases	Role switched to SYSADMIN
4	CREATE WAREHOUSE mywh	SYSADMIN	Can create warehouses	Warehouse 'mywh' created
5	USE ROLE PUBLIC	PUBLIC	Minimal privileges	Role switched to PUBLIC
6	SELECT CURRENT_ROLE()	PUBLIC	Can query current role	Returns 'PUBLIC'
7	CREATE WAREHOUSE testwh	PUBLIC	No create warehouse privilege	Error: insufficient privileges

💡 Execution stops after error due to insufficient privileges for PUBLIC role.

Status Tracker

Variable	Start	After Step 1	After Step 3	After Step 5	After Step 7
Current Role	None	ACCOUNTADMIN	SYSADMIN	PUBLIC	PUBLIC

Key Moments - 3 Insights

Why does the CREATE WAREHOUSE command fail when using the PUBLIC role?

Because PUBLIC role has minimal privileges and cannot create warehouses, as shown in execution_table step 7 where the system denies the action.

Can the ACCOUNTADMIN role perform all actions without restrictions?

Yes, ACCOUNTADMIN has full admin privileges, so actions like SHOW USERS succeed as in step 2.

What happens when switching roles in Snowflake?

The current role changes and the system checks privileges based on the new role, as tracked in variable_tracker and shown in steps 1, 3, and 5.

Visual Quiz - 3 Questions

Look at the execution_table, what is the current role after step 3?

A ACCOUNTADMIN

B SYSADMIN

C PUBLIC

D No role assigned

💡 Hint

Check the 'Role Used' column at step 3 in execution_table.

At which step does the system deny an action due to insufficient privileges?

A Step 2

B Step 4

C Step 7

D Step 6

💡 Hint

Look for 'Error' or 'insufficient privileges' in the Result column of execution_table.

If the user stays in SYSADMIN role, what would happen if they try to create a warehouse?

A Warehouse created successfully

B Action denied due to lack of privileges

C Role automatically switches to ACCOUNTADMIN

D Error: role not found

💡 Hint

Refer to step 4 in execution_table where SYSADMIN creates a warehouse.

Concept Snapshot

System-defined roles in Snowflake control what users can do.
ACCOUNTADMIN has full privileges.
SYSADMIN manages warehouses and databases.
PUBLIC has minimal access.
Switch roles with USE ROLE command.
Actions succeed or fail based on current role privileges.

Full Transcript

In Snowflake, system-defined roles like ACCOUNTADMIN, SYSADMIN, and PUBLIC determine what actions a user can perform. When a user logs in, they can switch roles using the USE ROLE command. Each role has specific privileges: ACCOUNTADMIN has full control, SYSADMIN can manage warehouses and databases, and PUBLIC has very limited access. When a user performs an action, Snowflake checks if the current role has the needed privileges. If yes, the action succeeds; if not, it fails with an error. For example, creating a warehouse works under SYSADMIN but fails under PUBLIC. This flow ensures secure and organized access control.