Connecting to Snowflake

To connect to Snowflake, Sisu requires a minimum level of permissions. This is accomplished through the creation of Roles and Users within the Snowflake UI or SnowSQL. In order to follow these steps, the account being used to create this user/role combination must have access to the following roles:

• SYSADMIN
• SECURITYADMIN
• USERADMIN

begin;

set role_name ='SISU_ROLE'

set user_name = 'SISU_USER'

set user_password = 'please_replace_with_password'

set warehouse_name = 'SISU_WAREHOUSE'

set database_name = 'DATABASE_NAME'

set schema_name = 'SCHEMA_NAME'

-- Create role for Sisu

use role securityadmin;

create role if not exists identifier($role_name);

comment on identifier($role_name) is 'This role is used by Sisu to perform analysis';

grant role identifier($role_name) to role SYSADMIN;

-- Create warehouse for Sisu

use role sysadmin;

create warehouse if not exists identifier($warehouse_name)

warehouse_size = xsmall

warehouse_type = standard

auto_suspend = 60

auto_resume = true

initially_suspended = true;

-- Create user for Sisu

use role useradmin;

create user if not exists identifier($user_name)

password = $user_password

default_role = $role_name

default_warehouse = $warehouse_name

-- Grant Sisu user access to role and warehouse

use role securityadmin;

grant role identifier($role_name) to user identifier($user_name);

grant usage on warehouse identifier($warehouse_name) to role identifier($role_name)

-- Grant Sisu role access to database and all future schema/tables

grant usage on database identifier($database_name) to role identifier($role_name);

grant usage on all schemas in database MY_DB to role TEST_ROLE;

grant select on all tables in database MY_DB to role TEST_ROLE;

grant usage on future schemas in database identifier($database_name)

   to role identifier($role_name);

grant select on future tables in database identifier($database_name)

   to role identifier($role_name);

-- If using Schema based access instead of Database

grant usage on database identifier($database_name) to role identifier($role_name);

grant usage on schema identifier($schema_name) to role identifier($role_name);

grant create stage on schema identifier($schema_name) to role identifier($role_name);

grant select on future tables in schema identifier($schema_name) to role identifier($role_name)

commit;