Database Preparation - Snowflake

This section contains scripts that will create the necessary roles and users on a Snowflake database.

Prerequisites

  • The target database must be running and accessible

  • You must have the necessary privileges on the database to run the scripts (see each script for details)

Scripts

The scripts must be run in the listed order.

Replace placeholders such as <yotilla_execute_user_name> in the scripts with your actual values.

Script 1 - Create roles and role hierarchy

Execute this script with the USERADMIN role, or with a role that has the CREATE ROLE privilege.

-----------------------------------------------------------------
-- create roles
-----------------------------------------------------------------
create role yotilla_admin_role;
create role yotilla_execute_templates_role;
create role yotilla_superuser_role;
create role yotilla_user_role;
create role yotilla_consumer_role;

-----------------------------------------------------------------
-- grant roles to roles
-----------------------------------------------------------------
grant role yotilla_admin_role to role sysadmin;

grant role yotilla_consumer_role to role yotilla_admin_role;
grant role yotilla_consumer_role to role yotilla_execute_templates_role;
grant role yotilla_consumer_role to role yotilla_superuser_role;
grant role yotilla_consumer_role to role yotilla_user_role;
 
grant role yotilla_user_role to role yotilla_admin_role;
grant role yotilla_user_role to role yotilla_execute_templates_role;
grant role yotilla_user_role to role yotilla_superuser_role;
 
grant role yotilla_superuser_role to role yotilla_admin_role;
grant role yotilla_superuser_role to role yotilla_execute_templates_role;
 
grant role yotilla_execute_templates_role to role yotilla_admin_role;

Script 2 - Grant global privileges

Execute this script with the ACCOUNTADMIN role, or with a role that has the MANAGE GRANTS privilege.

grant create role on account to role yotilla_admin_role;
grant create user on account to role yotilla_admin_role;

Script 3 - Create a user to define the target system connection

Execute this script with the USERADMIN role, or with a role that has the CREATE USER privilege.

create user <yotilla_execute_user_name> identified by <password>;
grant role yotilla_execute_templates_role to user <yotilla_execute_user_name>;

Script 4 - Grant usage of the Snowflake warehouse to the Yotilla user role

Execute this script with the ACCOUNTADMIN role or with a role that has the MANAGE WAREHOUSES privilege.

grant usage on warehouse <warehouse_name> to role yotilla_execute_templates_role;

Script 5 - Create a database or select an existing database

Choose one of the following three scenarios:

Scenario 1:   Create a new database using the role yotilla_execute_templates_role. 

create database <target_database>;
grant usage on database <target_database> to role yotilla_superuser_role;
grant usage on database <target_database> to role yotilla_user_role;
grant usage on database <target_database> to role yotilla_consumer_role;

Scenario 2:   Create a new database not using the role yotilla_execute_templates_role.

create database <target_database>;
grant all on database <target_database> to role yotilla_execute_templates_role;
grant usage on database <target_database> to role yotilla_superuser_role;
grant usage on database <target_database> to role yotilla_user_role;
grant usage on database <target_database> to role yotilla_consumer_role;

Scenario 3:   Use an existing database.

Execute this script with the role that owns the existing database.

grant all on database <target_database> to role yotilla_execute_templates_role;
grant usage on database <target_database> to role yotilla_superuser_role;
grant usage on database <target_database> to role yotilla_user_role;
grant usage on database <target_database> to role yotilla_consumer_role;

Script 6 - Create a user to manage the external stage schema

Execute this script with the USERADMIN role, or with a role that has the CREATE USER privilege.

create user <user_name> identified by <password>;
grant role yotilla_user_role to user <user_name>;

Script 7 - Create and grant privileges on the external stage schema

To create a new external schema, execute the following script with the user that was created in the previous step and use the role that should own the schema.

create schema <schema_name>;
grant usage on schema <schema_name> to role yotilla_user_role;

If the schema already exists, execute the following script with the role that owns the schema:

grant usage on schema <schema_name> to role yotilla_user_role;
grant select on all tables in schema <schema_name> to role yotilla_user_role;
grant select on all views in schema <schema_name> to role yotilla_user_role;

Execute this script with the ACCOUNTADMIN role or with a role that has the MANAGE GRANTS privilege.

grant all on future tables in schema <schema_name> to role yotilla_user_role;
grant all on future views in schema <schema_name> to role yotilla_user_role;