2. Snowflake Account Setup Queries

 

Step 1 :  Create Warehouse

CREATE WAREHOUSE WH_TESTING WITH WAREHOUSE_SIZE = 'XSMALL' WAREHOUSE_TYPE = 'STANDARD' AUTO_SUSPEND = 600 AUTO_RESUME = TRUE MIN_CLUSTER_COUNT = 1 MAX_CLUSTER_COUNT = 2 SCALING_POLICY = 'STANDARD';

Step 2 : Create Database

    USE ROLE SYSADMIN;

    CREATE DATABASE IF NOT EXISTS DB_TESTING;

Step 3 : Create Database Roles

       USE ROLE SECURITYADMIN;

       CREATE DATABASE ROLE IF NOT EXISTS ROLE_DB_TESTING_READ;

       CREATE DATABASE ROLE IF NOT EXISTS ROLE_DB_TESTING_WRITE;

Step 4 : Create Account Level Roles

USE ROLE SECURITYADMIN; 

CREATE ROLE DEV_APP;

CREATE ROLE DEV_APP_DEVELOPER;

CREATE ROLE DEV_READ_ALL;

Step 5 : Grant Roles to SYSADMIN

    GRANT ROLE ROLE_DB_TESTING_READ TO ROLE SYSADMIN;  

    GRANT ROLE ROLE_DB_TESTING_WRITE TO ROLE SYSADMIN;

Step 6 : Grant to DB Roles 

    USE ROLE SYSADMIN;

    GRANT USAGE ON DATABASE DB_TESTING TO ROLE ROLE_DB_TESTING_READ;

    GRANT USAGE ON DATABASE DB_TESTING TO ROLE ROLE_DB_TESTING_WRITE;

    USE DATABASE DB_TESTING;

    GRANT USAGE ON SCHEMA PUBLIC TO ROLE ROLE_DB_TESTING_READ;

    GRANT USAGE ON SCHEMA PUBLIC TO ROLE ROLE_DB_TESTING_WRITE;

    GRANT CREATE TABLE ON SCHEMA PUBLIC TO ROLE ROLE_DB_TESTING_WRITE;

    GRANT CREATE FILE FORMAT, CREATE PIPE, CREATE STAGE, CREATE SEQUENCE, MODIFY, MONITOR, USAGE ON SCHEMA PUBLIC TO ROLE ROLE_DB_TESTING_WRITE;

    GRANT USAGE ON FUTURE PROCEDURES IN DATABASE DB_TESTING TO ROLE ROLE_DB_TESTING_WRITE;

     GRANT USAGE ON FUTURE PROCEDURES IN DATABASE DB_TESTING TO ROLE ROLE_DB_TESTING_READ;

Step 7 : Create Schema and provide Grants

USE ROLE SYSADMIN;

        USE DATABASE DB_TESTING;

        CREATE SCHEMA IF NOT EXISTS SCHEMA_DEV;

        GRANT USAGE ON SCHEMA SCHEMA_DEV TO ROLE ROLE_DB_TESTING_READ;

        GRANT CREATE FILE FORMAT, CREATE PIPE, CREATE STAGE, CREATE SEQUENCE, MODIFY, MONITOR, USAGE ON SCHEMA SCHEMA_DEV TO ROLE ROLE_DB_TESTING_WRITE;    

        GRANT CREATE TABLE, CREATE FUNCTION, CREATE TEMPORARY TABLE, CREATE VIEW, MODIFY, USAGE ON SCHEMA SCHEMA_DEV TO ROLE ROLE_DB_TESTING_WRITE;

        

        USE ROLE SYSADMIN;

        GRANT SELECT ON ALL TABLES IN SCHEMA SCHEMA_DEV TO ROLE ROLE_DB_TESTING_READ;

        GRANT SELECT ON ALL VIEWS IN SCHEMA SCHEMA_DEV TO ROLE ROLE_DB_TESTING_READ;

        GRANT SELECT ON ALL MATERIALIZED VIEWS IN SCHEMA SCHEMA_DEV TO ROLE ROLE_DB_TESTING_READ;

        GRANT DELETE, INSERT, REFERENCES, SELECT, TRUNCATE, UPDATE ON ALL TABLES IN SCHEMA SCHEMA_DEV TO ROLE ROLE_DB_TESTING_WRITE;

        GRANT DELETE, INSERT, REFERENCES, SELECT, TRUNCATE, UPDATE ON ALL VIEWS IN SCHEMA SCHEMA_DEV TO ROLE ROLE_DB_TESTING_WRITE;

        GRANT DELETE, INSERT, REFERENCES, SELECT, TRUNCATE, UPDATE ON ALL MATERIALIZED VIEWS IN SCHEMA SCHEMA_DEV TO ROLE ROLE_DB_TESTING_WRITE;

        

        USE ROLE SECURITYADMIN;

        GRANT SELECT ON FUTURE TABLES IN SCHEMA DB_TESTING.SCHEMA_DEV TO ROLE ROLE_DB_TESTING_READ;

        GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON FUTURE TABLES IN SCHEMA DB_TESTING.SCHEMA_DEV TO ROLE ROLE_DB_TESTING_WRITE;

        GRANT SELECT ON FUTURE VIEWS IN SCHEMA DB_TESTING.SCHEMA_DEV TO ROLE ROLE_DB_TESTING_READ;

        GRANT SELECT, REFERENCES ON FUTURE VIEWS IN SCHEMA DB_TESTING.SCHEMA_DEV TO ROLE ROLE_DB_TESTING_WRITE;

        GRANT SELECT ON FUTURE MATERIALIZED VIEWS IN SCHEMA DB_TESTING.SCHEMA_DEV TO ROLE ROLE_DB_TESTING_READ;

        GRANT SELECT, REFERENCES ON FUTURE MATERIALIZED VIEWS IN SCHEMA DB_TESTING.SCHEMA_DEV TO ROLE ROLE_DB_TESTING_WRITE;

               

       

            USE ROLE SYSADMIN;

            GRANT CREATE FILE FORMAT, CREATE STAGE, CREATE SEQUENCE, MONITOR, USAGE ON SCHEMA SCHEMA_DEV  TO ROLE ROLE_DB_TESTING_WRITE;            

            USE SCHEMA DB_TESTING.SCHEMA_DEV; 

            CREATE TABLE IF NOT EXISTS INCR_TABLES (TABLE_NAME VARCHAR(1000) NOT NULL,PRIMARY_KEY VARCHAR(1000),SQL_SELECT VARCHAR,SQL_WHERE VARCHAR,SRC_DB_SCHEMA VARCHAR(100),LAST_MODIFIED_FIELD VARCHAR(1000),LAST_MODIFIED_FIELD_VALUE TIMESTAMP);

            CREATE TABLE IF NOT EXISTS RELOAD_TABLES (TABLE_NAME VARCHAR(1000),PRIMARY_KEY VARCHAR(1000),SQL_SELECT VARCHAR,SQL_WHERE VARCHAR,SRC_DB_SCHEMA VARCHAR(100),REFRESH_RATE NUMBER(5,0), NEXT_REFRESH_DATE TIMESTAMP);

            

        USE ROLE SECURITYADMIN;    

        GRANT ROLE ROLE_DB_TESTING_WRITE TO ROLE DEV_APP;

        GRANT ROLE ROLE_DB_TESTING_READ TO ROLE DEV_APP_DEVELOPER;

        GRANT ROLE ROLE_DB_TESTING_READ TO ROLE DEV_READ_ALL;