Snowflake SQL for creating an isolated Structure.rest environment

2020-06-21 -- Structure.rest Snowflake Setup.sql

//=============================================================================
// create top level objects
//=============================================================================
USE ROLE SYSADMIN;

// Database
CREATE DATABASE STRUCTURE_WORKSPACE;

// Warehouse
CREATE WAREHOUSE STRUCTURE_WORKSPACE_WH
  COMMENT='Warehouse for powering queries in the STRUCTURE_WORKSPACE'
  WAREHOUSE_SIZE=XSMALL
  AUTO_SUSPEND=60
  INITIALLY_SUSPENDED=TRUE;
//=============================================================================


//=============================================================================
// create object access (OA) roles
//=============================================================================
USE ROLE SECURITYADMIN;

// data access
CREATE ROLE STRUCTURE_WORKSPACE_WRITE;
CREATE ROLE STRUCTURE_WORKSPACE_READ;

// warehouse access
CREATE ROLE STRUCTURE_WORKSPACE_WH_USAGE;

// grant all roles to sysadmin (always do this)
GRANT ROLE STRUCTURE_WORKSPACE_WRITE    TO ROLE SYSADMIN;
GRANT ROLE STRUCTURE_WORKSPACE_READ     TO ROLE SYSADMIN;
GRANT ROLE STRUCTURE_WORKSPACE_WH_USAGE TO ROLE SYSADMIN;
//=============================================================================
 

//=============================================================================
// grant privileges to object access roles
//=============================================================================
USE ROLE SECURITYADMIN;

// data access
GRANT USAGE, CREATE SCHEMA ON DATABASE STRUCTURE_WORKSPACE                TO ROLE STRUCTURE_WORKSPACE_WRITE;
GRANT ALL PRIVILEGES ON ALL SCHEMAS IN DATABASE STRUCTURE_WORKSPACE       TO ROLE STRUCTURE_WORKSPACE_WRITE;
GRANT USAGE ON DATABASE STRUCTURE_WORKSPACE                               TO ROLE STRUCTURE_WORKSPACE_READ;
GRANT USAGE ON ALL SCHEMAS IN DATABASE STRUCTURE_WORKSPACE                TO ROLE STRUCTURE_WORKSPACE_READ;
GRANT USAGE ON FUTURE SCHEMAS IN DATABASE STRUCTURE_WORKSPACE             TO ROLE STRUCTURE_WORKSPACE_READ;
GRANT SELECT ON FUTURE TABLES IN DATABASE STRUCTURE_WORKSPACE             TO ROLE STRUCTURE_WORKSPACE_READ;
GRANT SELECT ON FUTURE VIEWS IN DATABASE STRUCTURE_WORKSPACE              TO ROLE STRUCTURE_WORKSPACE_READ;
GRANT SELECT ON FUTURE MATERIALIZED VIEWS IN DATABASE STRUCTURE_WORKSPACE TO ROLE STRUCTURE_WORKSPACE_READ;

// warehouse access
GRANT USAGE ON WAREHOUSE STRUCTURE_WORKSPACE_WH TO ROLE STRUCTURE_WORKSPACE_WH_USAGE;
//=============================================================================


//=============================================================================
// create business function roles and grant access to object access roles
//=============================================================================
USE ROLE SECURITYADMIN;
 
// bf role
CREATE ROLE STRUCTURE_WORKSPACE_DEVELOPER;
 
// grant all roles to sysadmin (always do this)
GRANT ROLE STRUCTURE_WORKSPACE_DEVELOPER TO ROLE SYSADMIN;

// grant OA roles to other OA roles
GRANT ROLE STRUCTURE_WORKSPACE_READ TO ROLE STRUCTURE_WORKSPACE_WRITE;

// grant OA roles to BF roles
GRANT ROLE STRUCTURE_WORKSPACE_WRITE    TO ROLE STRUCTURE_WORKSPACE_DEVELOPER;
GRANT ROLE STRUCTURE_WORKSPACE_READ     TO ROLE STRUCTURE_WORKSPACE_DEVELOPER;
GRANT ROLE STRUCTURE_WORKSPACE_WH_USAGE TO ROLE STRUCTURE_WORKSPACE_DEVELOPER;

// IMPORTANT: Grant any read access you want Structure.rest to have here. 
// GRANT ROLE FIVETRAN_DB_READ TO ROLE STRUCTURE_WORKSPACE_DEVELOPER;
// GRANT ROLE FINANCE_READ TO ROLE STRUCTURE_WORKSPACE_DEVELOPER
//=============================================================================


//=============================================================================
// create service account
//=============================================================================
USE ROLE SECURITYADMIN;
 
// create service account
CREATE USER STRUCTURE_SERVICE_ACCOUNT
  PASSWORD             = 'A solid passphrase here' // Please use your own passphrase 
  DEFAULT_WAREHOUSE    = STRUCTURE_WORKSPACE_WH
  DEFAULT_ROLE         = STRUCTURE_WORKSPACE_DEVELOPER
  MUST_CHANGE_PASSWORD = FALSE;

// grant permissions to service account
GRANT ROLE STRUCTURE_WORKSPACE_DEVELOPER TO USER STRUCTURE_SERVICE_ACCOUNT;
//=============================================================================