1. Snowflake Account Setup Process

 

In this sessions we will start by discussing setting up a Snowflake account, especially for those new to the Snowflake. With a Snowflake account readily available and a limited understanding of its system-defined roles, it usually becomes a challenge for a team lead or an admin to set up the environments with proper access controls to its developers or users.

In Snowflake, we have two Access Control Frameworks:

• Discretionary Access Control (DAC): Each object has an owner, who can in turn grant access to that object.

• Role-based Access Control (RBAC): Access privileges are assigned to roles, which are in turn assigned to users.

Roles

Roles are the entities to which privileges on securable objects can be granted and revoked. Roles are assigned to users to allow them to perform actions required for business functions in their organization. A user can be assigned multiple roles. This allows users to switch roles (i.e. choose which role is active in the current Snowflake session) to perform different actions using separate sets of privileges.

There are a small number of system-defined roles in a Snowflake account. System-defined roles cannot be dropped. In addition, the privileges granted to these roles by Snowflake cannot be revoked.

Types of Roles

Below are 3 major types of roles in Snowflake:

     Account roles

     Database roles

     Instance roles

System-Defined Roles

USERADMIN:

• The initial part of the account creation process is creating users and roles within an account.
• USERADMIN roles’ purpose is users and role creation. This role is granted with CREATE USER and CREATE ROLE security privileges.

SECURITYADMIN:

• A role is incomplete without any grants, and the SECURITYADMIN role is solely used for granting.
• Anything relating to grants in Snowflake is completely managed by SECURITYADMIN role.
• Once USERADMIN creates users and roles, you can use SECURITYADMIN to grant the users appropriate roles.
• You can grant warehouses, databases, schemas, integration objects, and access to create tables, stages, views, etc., to a role using SECURITYADMIN role.
• SECURITYADMIN role inherits the privileges of the USERADMIN role via the system role hierarchy.
• Note that Snowflake doesn’t have the concept of user groups. Instead, the Users are created, and necessary roles are granted to the user.

SYSADMIN:

• SYSADMIN creates the objects like databases, warehouses, schemas, etc., in an account.
• Although it creates objects like databases, warehouses, etc., it doesn’t grant access to these objects to the roles. It’s done by SECURITYADMIN.

ACCOUNTADMIN:

• ACCOUNTADMIN role encapsulates the SYSADMIN and SECURITYADMIN system-defined roles. It is the top-level role in the system and should be granted only to a limited/controlled number of users in your account.
• Other than this, ACCOUNTADMIN only has access to CREATE INTEGRATION objects in Snowflake.
• As a best practice, enable Users with ACCOUNTADMIN roles should have MFA enabled.

ORGADMIN:

• This role is mainly used to create accounts within an organization.
• Each account acts as a separate entity and will have its own databases, warehouses, and other objects.

PUBLIC:

• As the name suggests, this role can be accessed by every other user in an account.
• Objects created as a part of a PUBLIC role can be accessed by anyone and used when there is no need for access controls over the objects, and can be shared across the account.
• Generally, non recommended to use this role for production purposes.

 

Snowflake has five system-defined roles that are automatically created when a Snowflake account is provisioned:

• ·       ACCOUNTADMIN (Account Administrator)
• ·         SYSADMIN (System Administrator)
• ·         SECURITYADMIN (Security Administrator)
• ·         USERADMIN (User and Role Administrator)
• ·         ORGADMIN (Organization Administrator)

 

These roles are designed to provide built-in access controls and permissions for Snowflake objects and resources. The privileges of each role are inherited by all roles above it.

The primary intended purpose of each role is:

• ·         ACCOUNTADMIN: Reserved for system configuration.
• ·         SECURITYADMIN: Used for user and role management.
• ·         USERADMIN: Used for user and role management.
• ·         SYSADMIN: Used to manage databases, schemas, and tables