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';
-- Comma-separated list of databases to monitor
-- NOTE: Double-quote names with special characters, e.g. 'DB1, DB2, "prod-orders"'
SET database_list = '<comma_separated_db_list>';

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

-- Create warehouse for Revefi's monitoring workload
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 the role Revefi will use
CREATE ROLE IF NOT EXISTS identifier($revefi_role_name);

-- Create Revefi's user and grant access to role
-- Note: provide the RSA public key with the '-----BEGIN PUBLIC KEY-----' header removed
CREATE USER IF NOT EXISTS identifier($revefi_username)
  RSA_PUBLIC_KEY = '<RSA_PUBLIC_KEY>'
  DEFAULT_ROLE = $revefi_role_name;
GRANT ROLE identifier($revefi_role_name) TO USER identifier($revefi_username);

-- Grant permissions to use the new warehouse
GRANT OPERATE, USAGE, MONITOR ON WAREHOUSE identifier($revefi_warehouse_name) TO ROLE identifier($revefi_role_name);

-- Grant privileges to allow access to query history and other account level metadata
GRANT IMPORTED PRIVILEGES ON DATABASE "SNOWFLAKE" TO ROLE identifier($revefi_role_name);

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

-- Helper procedure: GRANT all database-level privileges for one database
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}`;
$$;
;

-- Procedure: Fix schema-level FUTURE grants that override database-level grants
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}]`;
$$
;

-- Loop through all databases and grant privileges
DECLARE
    db STRING;
    dbs ARRAY;
    procedure_db STRING;
    results ARRAY;
    db_result STRING;
    schema_result STRING;
BEGIN
    dbs := SPLIT($database_list, ',');
    procedure_db := CURRENT_DATABASE();
    results := ARRAY_CONSTRUCT();

    FOR i IN 0 TO ARRAY_SIZE(dbs) - 1 DO
        db := TRIM(dbs[i]::STRING);

        db_result := (CALL GRANT_DB_PRIVILEGES(:db));
        schema_result := (CALL GRANT_SCHEMA_REFERENCES_FOR_DB(:db));

        USE DATABASE identifier(:procedure_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;
;