System-defined roles (ACCOUNTADMIN, SYSADMIN, etc.) in Snowflake - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is the ACCOUNTADMIN role in Snowflake?

ACCOUNTADMIN is the highest-level system-defined role in Snowflake. It has full control over all objects and operations in the account, including managing users, roles, and billing.

beginner

What permissions does the SYSADMIN role have in Snowflake?

SYSADMIN can create and manage databases, schemas, and warehouses. It has full control over data objects but cannot manage users or roles unless granted additional privileges.

beginner

Which Snowflake system-defined role is responsible for managing users and roles?

The SECURITYADMIN role manages users, roles, and grants. It controls access and security policies but does not manage data objects directly.

beginner

True or False: The PUBLIC role in Snowflake has no privileges by default.

False. The PUBLIC role has some default privileges granted by Snowflake, such as USAGE on the SNOWFLAKE database and certain schemas. It is assigned to every user by default and can have additional privileges granted to it, which all users inherit.

intermediate

Why should the ACCOUNTADMIN role be used sparingly?

Because ACCOUNTADMIN has full control over the entire Snowflake account, using it sparingly reduces the risk of accidental or malicious changes that could affect security or operations.

Which Snowflake role can create warehouses and databases but cannot manage users by default?

A SECURITYADMIN

B ACCOUNTADMIN

C SYSADMIN

D PUBLIC

SYSADMIN manages data objects like warehouses and databases but does not manage users or roles unless granted extra privileges.

Who has the highest level of control in a Snowflake account?

A ACCOUNTADMIN

B SECURITYADMIN

C PUBLIC

D SYSADMIN

ACCOUNTADMIN is the top-level role with full control over all account operations.

Which role manages user access and role grants in Snowflake?

A PUBLIC

B SYSADMIN

C ACCOUNTADMIN

D SECURITYADMIN

SECURITYADMIN manages users, roles, and grants to control access.

What is true about the PUBLIC role in Snowflake?

A It is assigned to every user by default.

B It can manage warehouses.

C It has no privileges and cannot be assigned.

D It is the highest privilege role.

PUBLIC is assigned to all users by default and can have privileges granted to it.

Why is it important to limit use of ACCOUNTADMIN role?

A It has limited privileges.

B It has full control and misuse can cause serious issues.

C It can only view data.

D It cannot manage users.

ACCOUNTADMIN has full control, so limiting its use helps protect the account from mistakes or attacks.

Explain the main responsibilities of the ACCOUNTADMIN, SYSADMIN, and SECURITYADMIN roles in Snowflake.

Think about who controls data, who controls users, and who controls everything.

Why should the PUBLIC role be carefully managed even though it is assigned to all users by default?

Consider the impact of privileges given to everyone.

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