Snowflake Setup Script Generator

-- Use a role with sufficient privileges
USE ROLE ACCOUNTADMIN;

-- =============================================================================
-- CONFIGURATION — update these values before running
-- =============================================================================
SET revefi_username = 'REVEFI_USER';
SET revefi_warehouse_size = 'MEDIUM';
SET revefi_warehouse_name = 'REVEFI_WH';
SET revefi_role_name = 'REVEFI_ROLE';
SET revefi_rsa_public_key = '<paste_RSA_public_key_here>';
-- Any existing database your current role can access (used to create a temp procedure)
SET db_for_procedure = '<any_database>';

-- =============================================================================
-- STEP 1: Create role, user, warehouse and grant account permissions
-- =============================================================================

CREATE WAREHOUSE IF NOT EXISTS identifier($revefi_warehouse_name)
  WAREHOUSE_SIZE = $revefi_warehouse_size
  INITIALLY_SUSPENDED = TRUE
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE
  MAX_CONCURRENCY_LEVEL = 32
  STATEMENT_TIMEOUT_IN_SECONDS = 1200
  STATEMENT_QUEUED_TIMEOUT_IN_SECONDS = 1200;

CREATE ROLE IF NOT EXISTS identifier($revefi_role_name);

CREATE USER IF NOT EXISTS identifier($revefi_username)
  RSA_PUBLIC_KEY = $revefi_rsa_public_key
  DEFAULT_ROLE = $revefi_role_name;
GRANT ROLE identifier($revefi_role_name) TO USER identifier($revefi_username);

GRANT OPERATE, USAGE, MONITOR ON WAREHOUSE identifier($revefi_warehouse_name) TO ROLE identifier($revefi_role_name);

GRANT IMPORTED PRIVILEGES ON DATABASE "SNOWFLAKE" TO ROLE identifier($revefi_role_name);

-- =============================================================================
-- STEP 2: Grant metadata access to databases you want to monitor
-- =============================================================================

USE DATABASE identifier($db_for_procedure);
CREATE OR REPLACE PROCEDURE GRANT_DB_PRIVILEGES(db_name STRING)
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
snowflake.execute({sqlText: `GRANT USAGE ON DATABASE identifier('${DB_NAME}') TO ROLE identifier($revefi_role_name)`});
snowflake.execute({sqlText: `GRANT USAGE ON ALL SCHEMAS IN DATABASE identifier('${DB_NAME}') TO ROLE identifier($revefi_role_name)`});
snowflake.execute({sqlText: `GRANT USAGE ON FUTURE SCHEMAS IN DATABASE identifier('${DB_NAME}') TO ROLE identifier($revefi_role_name)`});

snowflake.execute({sqlText: `GRANT REFERENCES ON ALL TABLES IN DATABASE identifier('${DB_NAME}') TO ROLE identifier($revefi_role_name)`});
snowflake.execute({sqlText: `GRANT REFERENCES ON FUTURE TABLES IN DATABASE identifier('${DB_NAME}') TO ROLE identifier($revefi_role_name)`});
snowflake.execute({sqlText: `GRANT REFERENCES ON ALL VIEWS IN DATABASE identifier('${DB_NAME}') TO ROLE identifier($revefi_role_name)`});
snowflake.execute({sqlText: `GRANT REFERENCES ON FUTURE VIEWS IN DATABASE identifier('${DB_NAME}') TO ROLE identifier($revefi_role_name)`});

snowflake.execute({sqlText: `GRANT MONITOR ON ALL DYNAMIC TABLES IN DATABASE identifier('${DB_NAME}') TO ROLE identifier($revefi_role_name)`});
snowflake.execute({sqlText: `GRANT MONITOR ON FUTURE DYNAMIC TABLES IN DATABASE identifier('${DB_NAME}') TO ROLE identifier($revefi_role_name)`});

return `Granted DB-level privileges on: ${DB_NAME}`;
$$;
;

USE DATABASE identifier($db_for_procedure);
CREATE OR REPLACE PROCEDURE GRANT_SCHEMA_REFERENCES_FOR_DB(db_name STRING)
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
snowflake.execute({sqlText: `USE DATABASE identifier('${DB_NAME}')`});

var schema_rows = snowflake.execute({
    sqlText: `SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME <> 'INFORMATION_SCHEMA'`
});

var discovered = [];
var granted = [];

while (schema_rows.next()) {
    var schema = schema_rows.getColumnValue(1);
    discovered.push(schema);

    var futureGrants = snowflake.execute({sqlText: `SHOW FUTURE GRANTS IN SCHEMA "${schema}"`});

    if (futureGrants.getRowCount() > 0) {
        snowflake.execute({sqlText:`GRANT REFERENCES ON ALL TABLES IN SCHEMA "${schema}" TO ROLE identifier($revefi_role_name)`});
        snowflake.execute({sqlText:`GRANT REFERENCES ON FUTURE TABLES IN SCHEMA "${schema}" TO ROLE identifier($revefi_role_name)`});
        snowflake.execute({sqlText:`GRANT REFERENCES ON ALL VIEWS IN SCHEMA "${schema}" TO ROLE identifier($revefi_role_name)`});
        snowflake.execute({sqlText:`GRANT REFERENCES ON FUTURE VIEWS IN SCHEMA "${schema}" TO ROLE identifier($revefi_role_name)`});

        granted.push(schema);
    }
}

return `discovered_schemas=[${discovered}], granted_schemas=[${granted}]`;
$$
;

DECLARE
    db STRING;
    results ARRAY;
    db_result STRING;
    schema_result STRING;
BEGIN
    results := ARRAY_CONSTRUCT();
    SHOW DATABASES;
    LET c CURSOR FOR SELECT "name" FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
        WHERE "name" NOT IN ('SNOWFLAKE', 'SNOWFLAKE_SAMPLE_DATA');
    FOR row IN c DO
        db := row."name";
        USE DATABASE identifier($db_for_procedure);
        db_result := (CALL GRANT_DB_PRIVILEGES(:db));
        schema_result := (CALL GRANT_SCHEMA_REFERENCES_FOR_DB(:db));
        results := ARRAY_APPEND(results, OBJECT_CONSTRUCT(
            'database', :db,
            'db_privileges', db_result,
            'schema_privileges', schema_result
        ));
    END FOR;

    RETURN 'Privilege grant summary:\n' || ARRAY_TO_STRING(results, ';\n');
END
;

-- =============================================================================
-- STEP 3: Grant warehouse monitoring permissions (all warehouses)
-- =============================================================================

USE DATABASE identifier($db_for_procedure);
CREATE OR REPLACE PROCEDURE GRANT_WAREHOUSE_PRIVILEGES_TO_REVEFI()
    RETURNS VARCHAR
    LANGUAGE javascript
    EXECUTE AS CALLER
AS
$$
var warehouses_to_grant = snowflake.createStatement({ sqlText:`SHOW WAREHOUSES`}).execute();
var granted_warehouses = "";
while(warehouses_to_grant.next()) {
  warehouse_name = warehouses_to_grant.getColumnValue("name");
  snowflake.createStatement({ sqlText:`GRANT USAGE ON WAREHOUSE "${warehouse_name}" TO ROLE identifier($revefi_role_name)`}).execute();
  snowflake.createStatement({ sqlText:`GRANT MONITOR ON WAREHOUSE "${warehouse_name}" TO ROLE identifier($revefi_role_name)`}).execute();
  granted_warehouses += warehouse_name + ", "
}

return `Granted monitor and usage on warehouses: [${granted_warehouses}]`;
$$;

CALL GRANT_WAREHOUSE_PRIVILEGES_TO_REVEFI();
DROP PROCEDURE GRANT_WAREHOUSE_PRIVILEGES_TO_REVEFI();