Week 113 – Intermediate

Snowflake recently introduced a new type property for users, offering options like person, service, and legacy_service. This classification allows for better account security and clearer tracking of user roles. But do you know how to apply these user types in real-world scenarios?

In this challenge, you’ll design a Snowflake environment that requires the creation of various users based on their roles and authentication needs. Your task is to write the SQL commands to create each user with the appropriate type (person, service, or legacy_service) to suit the scenarios described below.

Scenario 1: Data Analyst Team

The Data Analyst team consists of five users who will be accessing Snowflake using Single Sign-On (SSO). They need read and write access to specific databases and will interact via the Snowflake UI and query execution.

Task for Scenario 1:
Write the SQL commands to create these users with appropriate authentication and role assignments.

Scenario 2: ETL Process Automation

You need to set up a service account for an automated ETL pipeline. The ETL process connects using key-pair authentication, performing data imports daily.

Task for Scenario 2:
Write the SQL command to create a service account that does not use passwords or MFA, but relies on key-pair authentication.

Scenario 3: Legacy System Integration

An older reporting tool requires access to Snowflake. This tool uses legacy password-based authentication and cannot be upgraded immediately to use key-pair authentication.

Task for Scenario 3:
Write the SQL command to create a legacy_service user that supports password-based authentication.

Scenario 4: Monitoring & Query History Analysis

You need to set up a service that runs periodic queries to analyze query history and monitor performance. It should use key-pair authentication for automation and not require interactive logins.

Task for Scenario 4:
Write the SQL command to create a service account for this purpose, without enabling MFA or requiring a password.

---

Hint: Remember to leverage Snowflake’s best practices for user management and security!
And don’t worry, once you finish this challenge, you’ll be the super hero of Snowflake security… or at least the protector of your Snowflake account 🛡️🦸🏻‍♀️

Good luck!

Remember if you want to participate:

1. Sign up as a member of Frosty Friday. You can do this by clicking on the sidebar, and then going to ‘REGISTER‘ (note joining our mailing list does not give you a Frosty Friday account)
2. Post your code to GitHub and make it publicly available (Check out our guide if you don’t know how to here)
3. Post the URL in the comments of the challenge.