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.
After the initial creation of the designated Security Admin and the System Admin users, Account Admin usage should be limited to viewing account objects, viewing and managing billing and credit utilization, storage usage, and stopping runaway queries.
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:

  • Setting the Snowflake Password Policy
  • Creating users
  • Assigning user defaults
    • Database
    • Warehouse (compute)
    • Default role
  • Create custom roles
  • Grant system and custom roles to users
  • Reset user passwords
  • Enable, disable and/or drop users from the system
  • Unlocking a user
  • Modify user parameters
  • Viewing user configuration
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.