begin;

-- create variables for user / password / role / warehouse / database (needs to be uppercase for objects)

set role_name = 'Verodat_SF_ROLE';

set user_name = 'Verodat_SF_USER';

set user_password = 'password123';

set warehouse_name = 'Verodat_Workspace_WAREHOUSE';

set database_name = 'Verodat_Workspace_DATABASE';

-- change role to securityadmin for user / role steps

use role securityadmin;

-- create role for Verodat

create role if not exists identifier($role_name);

grant role identifier($role_name) to role SYSADMIN;

-- create a user for Verodat

create user if not exists identifier($user_name)

password = $user_password

default_role = $role_name

default_warehouse = $warehouse_name;

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

-- change role to sysadmin for warehouse / database steps

use role sysadmin;

-- create a warehouse for Verodat

create warehouse if not exists identifier($warehouse_name)

warehouse_size = xsmall

warehouse_type = standard

auto_suspend = 60

auto_resume = true

initially_suspended = true;

-- create database for Verodat

create database if not exists identifier($database_name);

-- grant Verodat role access to warehouse

grant USAGE

on warehouse identifier($warehouse_name)

to role identifier($role_name);

-- grant Verodat access to database

grant CREATE SCHEMA, MONITOR, USAGE

on database identifier($database_name)

to role identifier($role_name);

-- change role to ACCOUNTADMIN for STORAGE INTEGRATION support (only needed for Snowflake on GCP)

use role ACCOUNTADMIN;

grant CREATE INTEGRATION on account to role identifier($role_name);

use role sysadmin;

commit;