Snowflake Permissions Script for Sigma

Below is a series of SQL commands that can be executed in Snowflake to prepare your environment to be connected to Sigma.

This script will create a SIGMA_USER, SIGMA_ROLE, SIGMA_WH, SIGMA_WRITE_DB and will grant all proper permissions to your READ_DATABASE of choice.

Instructions:

  1. Copy the below script to a new Snowflake worksheet
  2. Update all necessary variables in the ‘VARIABLES TO CHANGE’ section. (READ_DATABASE and SIGMA_USER_PASSWORD at minimum)
  3. Check the ‘All Queries’ checkbox
  4. Hit ‘Run’

Note:

  • All users, roles, warehouses, etc. can be pre-existing.
  • To grant permissions to multiple databases, re-execute the entire script for every database you’d like Sigma to have access to.
---------------------VARIABLES TO CHANGE---------------------

set READ_DATABASE = '<db_name>'; --database for Sigma to analyze

set SIGMA_USER = 'SIGMA_USER'; 
set SIGMA_USER_PASSWORD = '<password>'; --will not update if user already exists
set SIGMA_ROLE = 'SIGMA_ROLE'; 

set SIGMA_WAREHOUSE = 'SIGMA_WH'; 
set SIGMA_WAREHOUSE_SIZE = 'SMALL'; --will not update if warehouse already exists

set SIGMA_WRITE_DATABASE = 'SIGMA_WRITE_DB';
set SIGMA_WRITE_SCHEMA = 'SIGMA_WRITE';

-------------------------------------------------------------


-- change role to ACCOUNTADMIN
use role ACCOUNTADMIN;


-- create role for Sigma
create role if not exists identifier($SIGMA_ROLE);
grant role identifier($SIGMA_ROLE) to role SYSADMIN;
    -- grant SYSADMIN modify permissions to Sigma Role


-- create a service account user for Sigma
create user if not exists identifier($SIGMA_USER)
password = $SIGMA_USER_PASSWORD;
grant role identifier($SIGMA_ROLE) to user identifier($SIGMA_USER);


-- create a warehouse for Sigma if needed.  See docs for more options: https://docs.snowflake.com/en/sql-reference/sql/create-warehouse.html
create warehouse if not exists identifier($SIGMA_WAREHOUSE) 
warehouse_size = $SIGMA_WAREHOUSE_SIZE;
grant usage on warehouse identifier($SIGMA_WAREHOUSE) to role identifier($SIGMA_ROLE);


-- grant read access for analytics database to sigma role
Grant usage on database identifier($READ_DATABASE) to role identifier($SIGMA_ROLE);

Grant usage on all schemas in database identifier($READ_DATABASE) to role identifier($SIGMA_ROLE);
Grant select on all tables in database identifier($READ_DATABASE) to role identifier($SIGMA_ROLE);
Grant select on all views in database identifier($READ_DATABASE) to role identifier($SIGMA_ROLE);

Grant usage on future schemas in database identifier($READ_DATABASE) to role identifier($SIGMA_ROLE);
Grant select on future tables in database identifier($READ_DATABASE) to role identifier($SIGMA_ROLE);
Grant select on future views in database identifier($READ_DATABASE) to role identifier($SIGMA_ROLE);


-- create a database and schema for Sigma to write back
create database if not exists identifier($SIGMA_WRITE_DATABASE);
use database identifier($SIGMA_WRITE_DATABASE);
create schema if not exists identifier($SIGMA_WRITE_SCHEMA);


-- grant write permissions to the write back schema:
Grant usage on database identifier($SIGMA_WRITE_DATABASE) to role identifier($SIGMA_ROLE); 
grant all on schema identifier($SIGMA_WRITE_SCHEMA) to role identifier($SIGMA_ROLE);