System-defined roles (ACCOUNTADMIN, SYSADMIN, etc.) in Snowflake - Mini Project: Build & Apply

Understanding Snowflake System-Defined Roles

📋 What You'll Learn

💡 Why This Matters

🌍 Real World

Snowflake administrators use system-defined roles to manage permissions securely and efficiently in cloud data platforms.

💼 Career

Understanding and managing roles is essential for cloud data engineers, database administrators, and security professionals working with Snowflake.

1

Create the list of system-defined roles

Create a list variable called system_roles containing these exact strings: 'ACCOUNTADMIN', 'SYSADMIN', 'SECURITYADMIN', 'USERADMIN', and 'PUBLIC'.

Snowflake

# Create the list variable system_roles with the exact roles
# Your code here

Need a hint?

Use square brackets to create a list and include all role names as strings.

2

Create a user name variable

Create a variable called user_name and set it to the exact string 'data_analyst'.

Snowflake

system_roles = ['ACCOUNTADMIN', 'SYSADMIN', 'SECURITYADMIN', 'USERADMIN', 'PUBLIC']
# Create the variable user_name with value 'data_analyst'
# Your code here

Need a hint?

Assign the string 'data_analyst' to the variable user_name using an equals sign.

3

Assign the SYSADMIN role to the user

Write the exact SQL command to grant the role SYSADMIN to the user stored in user_name. Use the format: GRANT ROLE SYSADMIN TO USER data_analyst; but replace data_analyst with the variable user_name in a Python f-string called grant_command.

Snowflake

system_roles = ['ACCOUNTADMIN', 'SYSADMIN', 'SECURITYADMIN', 'USERADMIN', 'PUBLIC']
user_name = 'data_analyst'
# Create the SQL grant command using an f-string and assign it to grant_command
# Your code here

Need a hint?

Use an f-string to insert the user_name variable inside the SQL command string.

4

Add a comment about ACCOUNTADMIN role

Add a comment line explaining that the ACCOUNTADMIN role has full control over the Snowflake account. The comment should start with # and mention ACCOUNTADMIN.

Snowflake

system_roles = ['ACCOUNTADMIN', 'SYSADMIN', 'SECURITYADMIN', 'USERADMIN', 'PUBLIC']
user_name = 'data_analyst'
grant_command = f"GRANT ROLE SYSADMIN TO USER {user_name};"
# Add a comment about ACCOUNTADMIN role here
# Your code here

Need a hint?

Start the comment with # and clearly mention ACCOUNTADMIN's full control.