When planning for a cloud data warehouse such as Snowflake, it is important to have a strategic plan in place to initialize the environment for development, testing and production. Using standard patterns for this initialization is a best practice.
Sirius is publishing a four-part series to demonstrate best practices in reusable patterns for initializing Snowflake environments. In Part 1 of our series, we created a how-to guide for faster initial setup of the Snowflake environment.
Our guide focuses on “elevated accounts,” which are an organization’s administrative accounts, and provides instructions on how to set them up and a template for generating Create User SQL statements.
Stay tuned—our series will also cover structures, roles and computes. If you would like to learn more about Sirius’ Snowflake services offerings, contact a Sirius expert.
Snowflake Elevated Accounts
Introduction
Snowflake is an analytic data warehouse provided as Software as a Service (SaaS). Snowflake provides a data warehouse that is faster, easier to use, and far more flexible than traditional data warehouse offerings.
Using standard patterns encourages Snowflake usage and best practices. In this four-part series, the goal is to provide reusable patterns for initializing the Snowflake environment for development, testing and production environments.
Objective
Provide a Snowflake best practice for elevated accounts.
Project Intent
Elevated accounts (ACCOUNTADMIN and SECURITYADMIN) will be created as the organization’s administrative accounts. The administrative accounts will be assigned to pseudo users to discourage use of the accounts for any purpose other than the specific roles for which the accounts are responsible.
Solution
Overview of Solution
The following information describes the purpose and intent of each elevated role within the Snowflake environment. During the initial setup of the system, provisioning of the following accounts will inform designated users of their roles and responsibilities within the Snowflake ecosystem.
Elevated Roles
| ACCOUNTADMIN | The Account Admin role is the most powerful role in the system, since it can view and operate on all objects in the account. The Account Admin role should be assigned to two users in the organization to support managing lost or forgotten passwords for Account Admins. If only one user is configured with the Account Admin role and the password is forgotten, then the Customer must work with Snowflake to change the password, which can take up to two business days to modify. This is a strictly enforced policy of Snowflake and not the system. As a best practice, the Account Admin role must be required to enroll MFA as an added security policy.
The Account Admin role should only be used to perform the initial setup tasks in the system and manage the account-level objects and tasks on a day-to-day basis. Warning: The Account Admin role must not be used to create database objects, if other users are intended to use those objects, as they will not be visible to the users unless otherwise explicitly assigned. If the database objects are intended to be secure and only visible to the Account Admin, then creation of the database objects in this explicit scenario would be logical. |
| SECURITYADMIN | The Security Admin role is used to perform all security-related tasks. The Security Admin role should be limited to performing the following tasks:
|
| SYSADMIN | The System Admin role is responsible for creation of all database objects. Account Admin and Security Admin roles must not participate in creation of database objects as objects created by these accounts do not follow best practices. |
Technical Details
Organization
Backup ACCOUNTADMIN Account
Creating a backup ACCOUNTADMIN account will ensure that in the event the primary ACCOUNTADMIN can no longer access the Snowflake site, an alternate backup ACCOUNTADMIN can repair the primary ACCOUNTADMIN account.
Collect the following properties for the alternate ADMINACCOUNT
| Property | Value | Comment |
|---|---|---|
| User Name | backup_acctadmin_{instance} | Assign sequence number to {instance} if additional backup ADMINACCOUNT accounts are required by the Organization |
| New Password | Temporary-password | Force password change on initial login |
| Confirm Password | ‘same as above’ | |
| Comment | Backup Account Admin Account {instance} | Provide a meaningful description for the account |
| Login Name | ‘same as [User Name]’ | Enforces explicit usages of the account is for ACCOUNTADMIN tasks only |
| First Name | Designated User’s first name | |
| Last Name | Designated User’s last name | |
| Email Address | ||
| Default Role | ACCOUNTADMIN |
Creating the Backup ACCOUNTADMIN Account
As Account Owner, login to the Snowflake web console. The following dialog boxes provide guidance for creating the backup Account Admin account.
Select Account->Users, then click on (+)Create to create the backup Account Admin user account(s).
Using the Elevated Account Properties provided above, create the backup ACCOUNTADMIN user account using the following dialog boxes. Ensure that the ‘Force Password Change’ is checked.
On completion of account creation, inform the designated user(s) to follow the MFA (multi-factor authentication) configuration. MFA provides an extra security measure to ensure that stolen passwords alone will not provide access to the elevated ACCOUNTADMIN account.
On Initial Login of the backup ACCOUNTADMIN, instruct the user to select Preference -> Multi-factor Authentication.
- Select ‘Enroll MFA’
- Download ‘DUO Mobile on user’s mobile device and enter the key code from the Web Console
- Enter the key code from the web console and follow the instructions on the device to setup MFA security
- Logout and log back into the web console, enter user name and password.
- You will be presented with an input box for the current security code on your mobile device
- Enter the code to complete authentication and access to Snowflake
SECURITYADMINACCOUNT Account
Identify designated user(s) tasked with performing the responsibilities outlined in Elevated Roles for SECURITYADMIN.
Collect the following properties for the SECURITYACCOUNT
| Property | Value | Comment |
|---|---|---|
| User Name | security_admin_{instance} | Assign sequence number to {instance} if additional SECURITYACCOUNT accounts are required by the Organization |
| New Password | Temporary-password | Force password change on initial login |
| Confirm Password | ‘same as above’ | |
| Comment | Security Admin Account {instance} | Provide a meaningful description for the account |
| Login Name | ‘same as [User Name]’ | Enforces explicit usage of the account is for SECURITYADMIN tasks only |
| First Name | Designated User’s first name | |
| Last Name | Designated User’s last name | |
| Email Address | ||
| Default Role | SECURITYADMIN |
Creating the SECURITYADMIN Account
As Account Owner, login to the Snowflake web console. The following dialog boxes provide guidance for creating the backup Account Admin account.
Select Account->Users, then click on (+)Create to create the Security Admin user account(s).
Using the Elevated Account Properties provided above, create the SECURITYADMIN user account using the following dialog boxes. Ensure that the ‘Force Password Change’ is checked.
On completion of account creation, inform the designated user(s) to follow the MFA configuration. MFA provides an extra security measure to ensure stolen passwords alone will not provide access to the elevated SECURITYADMIN account.
On Initial Login of the backup SECURITYADMIN, instruct the user(s) to select Preference -> Multi-factor Authentication.
- Select ‘Enroll MFA’
- Download ‘DUO Mobile on user’s mobile device and enter the key code from the web console
- Enter the key code from the web console and follow the instructions on the device to setup MFA security
- Logout and log back into the web console, enter user name and password.
- You will be presented with an input box for the current security code on your mobile device
- Enter the code to complete authentication and access to Snowflake
SYSADMIN Account
Creation of the SYSADMIN account(s) will be performed by the designated SECURITYADMIN. The System Administrator role is used to create the Database Object, i.e., Database (namespace, i.e., DEV_EDW), Schemas (RAW, SECURE_STAGE, STAGE, OPS, SECURE_GOLD, GOLD, PUBLISH), EDW Tables, and FILE FORMAT object specifications.
Identify designated user(s) tasked with performing the responsibilities outlined in Elevated Roles for SYSADMIN.
Collect the following properties for the SYSADMIN Account
| Replaceable Parameter | Value | Comment |
|---|---|---|
| RpUserAccount | username | Use the identity of the person assigned to the SYSADMIN role |
| RpTempPassword | Temporary-password | Force password change on initial login |
| RpUserAccount | ‘same as [User Name]’ | Assigned to a User as DBA |
| RpDisplayName | ‘User Name – System Administrator’ | Display Role and User Name |
| RpFirstName | Designated User’s first name | |
| RpMiddleName | Optional | |
| RpLastName | Designated User’s last name | |
| RpEmailAddress | ||
| RpDefaultRole | SYSADMIN | |
| RpDefaultWarehouse | Loosely coupled, WAREHOUSE must exist before use | |
| RpNamespace | Loosely coupled, DATABASE can be created after SYSADMIN by the SYSADMIN |
Configure the SYSADMIN Role to the SYSADMIN User using the template below.
| Replaceable Parameter | Value |
|---|---|
| RpUserAccount | ‘username’ |
| RpRole | SYSADMIN |
The following template can be used to generate Create User SQL statements. Create a script replacing the placeholder parameters with values from the above SYSDMIN Property template.
The following template can be used in conjunction with the Create User template to subsequently script the assignment of the SYSADMIN Role to the SYSADMIN user.
