account-setup.sql

-- Create a warehouse
USE ROLE SYSADMIN;
CREATE OR REPLACE WAREHOUSE PROVISION_WH
  WAREHOUSE_SIZE = XSMALL
  WAREHOUSE_TYPE = STANDARD
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED=TRUE
  MIN_CLUSTER_COUNT = 1
  MAX_CLUSTER_COUNT = 1
  SCALING_POLICY = 'STANDARD'
  COMMENT = 'Warehouse for the phData Provision tool';

-- Create a role for the provision user
USE ROLE SECURITYADMIN;
CREATE OR REPLACE ROLE PROVISION_ADMIN
  COMMENT = 'Role for the phData Provision tool';

-- Grant the role access to create objects, users, roles, and privileges
GRANT ROLE SYSADMIN TO ROLE PROVISION_ADMIN;
GRANT ROLE SECURITYADMIN TO ROLE PROVISION_ADMIN;
GRANT ROLE USERADMIN TO ROLE PROVISION_ADMIN;

-- Create the user
USE ROLE USERADMIN;
CREATE OR REPLACE USER PROVISION_USER
  LOGIN_NAME = 'PROVISION_USER'
  DISPLAY_NAME = 'PROVISION_USER'
  DEFAULT_WAREHOUSE = PROVISION_WH
  DEFAULT_ROLE = PROVISION_ADMIN
  COMMENT = 'User for the phData Provision tool';

-- Grant the user access to the role
USE ROLE SECURITYADMIN;
GRANT ROLE PROVISION_ADMIN TO USER PROVISION_USER; 

-- Grant ACCOUNTADMIN to the provision role (required to manage RESOURCE MONITORS)
USE ROLE ACCOUNTADMIN;
GRANT ROLE ACCOUNTADMIN TO ROLE PROVISION_ADMIN;

USE ROLE ACCOUNTADMIN;
CREATE OR REPLACE RESOURCE MONITOR PROVISION_WH_MONITOR WITH 
  CREDIT_QUOTA = 15
  FREQUENCY = WEEKLY
  START_TIMESTAMP = IMMEDIATELY
  TRIGGERS 
    ON 75 PERCENT DO NOTIFY
    ON 100 PERCENT DO SUSPEND;
    
ALTER WAREHOUSE PROVISION_WH SET RESOURCE_MONITOR = PROVISION_WH_MONITOR;


-- Additional custom roles to be used by the Provision tool as object owners.
-- These must be created during initial setup in order to provision a complete stack for the first time with
-- these roles as owners. Otherwise, you would have to provision these roles first, update the stack with
-- the resources owned by these roles, and then provision again.
USE ROLE USERADMIN;

CREATE OR REPLACE ROLE POLICY_ADMIN;
CREATE OR REPLACE ROLE TAG_ADMIN;
CREATE OR REPLACE ROLE ALERT_ADMIN;
CREATE OR REPLACE ROLE SHARE_ADMIN;

USE ROLE SECURITYADMIN;

GRANT ROLE POLICY_ADMIN TO ROLE PROVISION_ADMIN;
GRANT ROLE TAG_ADMIN TO ROLE PROVISION_ADMIN;
GRANT ROLE ALERT_ADMIN TO ROLE PROVISION_ADMIN;
GRANT ROLE SHARE_ADMIN TO ROLE PROVISION_ADMIN;