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.

Practice

1. Which Snowflake system-defined role has full control over the entire account, including all objects and users?

easy

A. ACCOUNTADMIN

B. SYSADMIN

C. SECURITYADMIN

D. PUBLIC

Solution

1. Step 1: Understand role privileges

   ACCOUNTADMIN is designed to have full control over the Snowflake account, including all objects and user management.

2. Step 2: Compare with other roles

   SYSADMIN manages data objects, SECURITYADMIN manages users and roles, and PUBLIC has minimal default privileges.

3. Final Answer:

   ACCOUNTADMIN -> Option A

4. Quick Check:

   Full account control = ACCOUNTADMIN [OK]

Hint: Full control role is ACCOUNTADMIN, others have limited scopes [OK]

Common Mistakes:

• Confusing SYSADMIN with full control
• Thinking SECURITYADMIN manages data objects
• Assuming PUBLIC has admin privileges

2. Which of the following is the correct syntax to switch to the SYSADMIN role in Snowflake?

easy

A. SET ROLE SYSADMIN;

B. CHANGE ROLE TO SYSADMIN;

C. USE ROLE SYSADMIN;

D. SWITCH TO SYSADMIN;

Solution

1. Step 1: Recall Snowflake role switching syntax

   The correct command to switch roles is USE ROLE <role_name>;.

2. Step 2: Verify options

   Only USE ROLE SYSADMIN; matches the correct syntax exactly.

3. Final Answer:

   USE ROLE SYSADMIN; -> Option C

4. Quick Check:

   Switch role command = USE ROLE [OK]

Hint: Switch roles with USE ROLE followed by role name [OK]

Common Mistakes:

• Using SET ROLE instead of USE ROLE
• Adding extra words like TO or SWITCH
• Missing semicolon at the end

3. Given the following commands executed in order:

USE ROLE SECURITYADMIN;
CREATE USER test_user PASSWORD='Pass1234';
USE ROLE SYSADMIN;
CREATE TABLE test_table(id INT);

Which statement is true about the created objects?

medium

A. test_user is created successfully; test_table creation fails due to insufficient privileges.

B. Both test_user and test_table creation fail.

C. test_user creation fails; test_table is created successfully.

D. Both test_user and test_table are created successfully.

Solution

1. Step 1: Analyze role privileges for user creation

   SECURITYADMIN can create users, so CREATE USER test_user succeeds.

2. Step 2: Analyze role privileges for table creation

   SYSADMIN manages data objects, so CREATE TABLE test_table succeeds.

3. Final Answer:

   Both test_user and test_table are created successfully. -> Option D

4. Quick Check:

   SECURITYADMIN creates users, SYSADMIN creates tables [OK]

Hint: SECURITYADMIN for users, SYSADMIN for tables; both succeed [OK]

Common Mistakes:

• Assuming SYSADMIN can create users
• Assuming SECURITYADMIN can create tables
• Confusing role privileges

4. You run the command CREATE USER new_user PASSWORD='abc123'; while using the SYSADMIN role and get an error. What is the most likely cause?

medium

A. Password does not meet complexity requirements.

B. SYSADMIN role does not have privileges to create users.

C. The command syntax is incorrect.

D. The user already exists.

Solution

1. Step 1: Check role privileges for user creation

   Only SECURITYADMIN or ACCOUNTADMIN can create users; SYSADMIN lacks this privilege.

2. Step 2: Consider other options

   Password complexity or syntax errors would cause different error messages; user existence would be a different error.

3. Final Answer:

   SYSADMIN role does not have privileges to create users. -> Option B

4. Quick Check:

   User creation requires SECURITYADMIN or ACCOUNTADMIN [OK]

Hint: User creation needs SECURITYADMIN, not SYSADMIN [OK]

Common Mistakes:

• Blaming password complexity without checking error
• Assuming SYSADMIN can create users
• Ignoring role privilege boundaries

5. You want to delegate user and role management to one admin and data object management to another, while keeping full control yourself. Which role assignments follow best practices?

hard

A. Assign SECURITYADMIN to user1, SYSADMIN to user2, ACCOUNTADMIN to yourself.

B. Assign ACCOUNTADMIN to user1, SECURITYADMIN to user2, SYSADMIN to yourself.

C. Assign SYSADMIN to user1, ACCOUNTADMIN to user2, SECURITYADMIN to yourself.

D. Assign PUBLIC to user1, SYSADMIN to user2, SECURITYADMIN to yourself.

Solution

1. Step 1: Understand role responsibilities

   SECURITYADMIN manages users and roles; SYSADMIN manages data objects; ACCOUNTADMIN has full control.

2. Step 2: Match roles to admin tasks

   Assign SECURITYADMIN to the admin handling users, SYSADMIN to the admin handling data, and keep ACCOUNTADMIN yourself for full control.

3. Final Answer:

   Assign SECURITYADMIN to user1, SYSADMIN to user2, ACCOUNTADMIN to yourself. -> Option A

4. Quick Check:

   Delegate roles by responsibility, keep ACCOUNTADMIN for full control [OK]

Hint: Delegate SECURITYADMIN for users, SYSADMIN for data, keep ACCOUNTADMIN [OK]

Common Mistakes:

• Giving ACCOUNTADMIN to others unnecessarily
• Assigning PUBLIC role for admin tasks
• Mixing up role responsibilities