Snowflake

-- Use a role with sufficient privileges to grant the below permissions
USE ROLE ACCOUNTADMIN;

-- Configuration
set revefi_username='REVEFI_USER';
set revefi_password='<strong_password_for_revefi_read_only_access>';
set revefi_warehouse_size='XSMALL';
set revefi_warehouse_name='REVEFI_WH';
set revefi_role_name='REVEFI_ROLE';

-- 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
CREATE USER IF NOT EXISTS identifier($revefi_username) PASSWORD=$revefi_password  DEFAULT_ROLE=$revefi_role_name; 
GRANT ROLE identifier($revefi_role_name) TO USER identifier($revefi_username);
 
-- Grant permissions to use the new warehouse
-- OPERATE allows Revefi to start and stop the 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);

-- Use a role with sufficient privileges to grant the below permissions
USE ROLE ACCOUNTADMIN;

-- Configuration
set revefi_role_name='REVEFI_ROLE';
-- NOTE: If your database name has special characters, please double quote the name:
-- https://docs.snowflake.com/en/sql-reference/identifiers-syntax#double-quoted-identifiers
-- E.g. set database_name = '"prod-orders"';
set database_name = '<db_to_monitor>';

-- Grant permission to run the USE command on database and schemas
GRANT USAGE on database identifier($database_name) to role identifier($revefi_role_name);
GRANT USAGE ON ALL SCHEMAS IN DATABASE identifier($database_name) TO ROLE identifier($revefi_role_name);
GRANT USAGE ON FUTURE SCHEMAS IN DATABASE identifier($database_name) TO ROLE identifier($revefi_role_name);

-- Grant permission to view tables and their schemas
GRANT REFERENCES ON ALL TABLES IN DATABASE identifier($database_name) TO ROLE identifier($revefi_role_name);
GRANT REFERENCES ON FUTURE TABLES IN DATABASE identifier($database_name) TO ROLE identifier($revefi_role_name);

-- Grant permission to view views and their schemas
GRANT REFERENCES ON ALL VIEWS IN DATABASE identifier($database_name) TO ROLE identifier($revefi_role_name);
GRANT REFERENCES ON FUTURE VIEWS IN DATABASE identifier($database_name) TO ROLE identifier($revefi_role_name);

USE DATABASE identifier($database_name);
-- By default the below procedure is created in the PUBLIC schema of the specified database.
-- If a PUBLIC schema does not exist, please un-comment the below line and specify any schema name of your choice.
-- USE SCHEMA <schema_name>;

-- Snowflake allows granting privileges either for a database or a schema. However, schema privileges take precedence.
-- This has an unfortunate side-effect for future grants, explained here: https://community.snowflake.com/s/article/DB-Level-Future-Grants-Overridden-by-Schema-Level-Future-Grants
-- Any schema level FUTURE grants to one role, will cause Snowflake to dis-regard all db level FUTURE grants to other roles.
-- This below stored procedure checks whether any FUTURE grants exists for a schema. If they do, it grants Revefi FUTURE grants for that schema.
-- If no FUTURE grants exist for any schema, the below stored procedure is a no-op.
CREATE OR REPLACE PROCEDURE GRANT_SCHEMA_REFERENCES_TO_REVEFI()
  RETURNS VARCHAR
  LANGUAGE javascript
  EXECUTE AS CALLER
  AS
  $$
  // Check for existing future grants to each schema. If they exist, grant future privileges to Revefi for that schema. 
  var schemas_to_grant = snowflake.createStatement({ sqlText:`select * from information_schema.SCHEMATA where SCHEMA_NAME <> 'INFORMATION_SCHEMA'`}).execute();
  var discovered_schemas = "";
  var granted_schemas = "";
  while(schemas_to_grant.next()) {
    table_schema = schemas_to_grant.getColumnValue("SCHEMA_NAME");
    discovered_schemas += table_schema + ","
    var show_future_grants_result = snowflake.createStatement({sqlText: `SHOW FUTURE GRANTS IN SCHEMA "${table_schema}"`}).execute();
    if (show_future_grants_result.getRowCount() > 0) {
        snowflake.createStatement({ sqlText:`GRANT REFERENCES ON ALL TABLES IN SCHEMA "${table_schema}" TO ROLE identifier($revefi_role_name)`}).execute();
        snowflake.createStatement({ sqlText:`GRANT REFERENCES ON FUTURE TABLES IN SCHEMA "${table_schema}" TO ROLE identifier($revefi_role_name)`}).execute();
        snowflake.createStatement({ sqlText:`GRANT REFERENCES ON ALL VIEWS IN SCHEMA "${table_schema}" TO ROLE identifier($revefi_role_name)`}).execute();
        snowflake.createStatement({ sqlText:`GRANT REFERENCES ON FUTURE VIEWS IN SCHEMA "${table_schema}" TO ROLE identifier($revefi_role_name)`}).execute();


        granted_schemas += table_schema + ","
    }
  }
      
  return `Discovered schemas: [${discovered_schemas}]. Granted references for schemas: [${granted_schemas}] (if blank, no schema grants were needed)`;
  $$;
 
CALL GRANT_SCHEMA_REFERENCES_TO_REVEFI();

-- Use any existing database name that your current role has access to, this is only used to temporarily create the stored procedure below
SET db_for_procedure='<any_database>';

-- Manually grant permissions to one warehouse at a time using:
-- GRANT MONITOR ON WAREHOUSE <warehouse_name> TO ROLE identifier($revefi_role_name)
-- GRANT USAGE ON WAREHOUSE <warehouse_name> TO ROLE identifier($revefi_role_name)
--
-- To grant the permission for all your warehouses together:
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}]`;
$$;

-- Invoke the procedure and clean up
CALL GRANT_WAREHOUSE_PRIVILEGES_TO_REVEFI();
DROP PROCEDURE GRANT_WAREHOUSE_PRIVILEGES_TO_REVEFI();

-- Allow Revefi to view all database names and the configuration settings of all 
-- current and future warehouses
GRANT MONITOR USAGE ON ACCOUNT TO ROLE identifier($revefi_role_name);

SET revefi_db='REVEFI_DB';

-- Create a dedicated database for REVEFI to use to create tasks and procedures needed for automangement
CREATE DATABASE identifier($revefi_db);

-- Grant usage on all schemas and db
GRANT USAGE ON ALL SCHEMAS IN DATABASE identifier($revefi_db) TO ROLE identifier($revefi_role_name);
GRANT USAGE on database identifier($revefi_db) to role identifier($revefi_role_name);

USE DATABASE identifier($revefi_db);

-- Grant permissions to create procedure and tasks in revefi_db
GRANT CREATE PROCEDURE ON SCHEMA PUBLIC TO ROLE identifier($revefi_role_name);
GRANT CREATE TASK ON SCHEMA PUBLIC TO ROLE identifier($revefi_role_name);
GRANT EXECUTE TASK ON ACCOUNT TO ROLE identifier($revefi_role_name);

-- To give permissions to selected warehouses one at a time, run the following command with warehouse name replaced
-- GRANT MODIFY ON WAREHOUSE <warehouse_name> TO ROLE identifier($revefi_role_name);

-- To grant the permission for all your warehouses together:
CREATE OR REPLACE PROCEDURE GRANT_WAREHOUSE_AUTO_MANAGEMENT_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 MODIFY ON WAREHOUSE "${warehouse_name}" TO ROLE identifier($revefi_role_name)`}).execute();
  granted_warehouses += warehouse_name + ", "
}

return `Granted operate on warehouses: [${granted_warehouses}]`;
$$;

-- Invoke the procedure and clean up
CALL GRANT_WAREHOUSE_AUTO_MANAGEMENT_PRIVILEGES_TO_REVEFI();
DROP PROCEDURE GRANT_WAREHOUSE_AUTO_MANAGEMENT_PRIVILEGES_TO_REVEFI();