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 = 5 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);

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();
    
        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();