Script to grant Dataiku read access to all tables in a given schema in Snowflake

dataiku_snowflake_schema_read_access.sql

begin;

  -- create variables for dataiku role and for database / schema where the tables are (needs to be uppercase for objects)
  set role_name = 'DATAIKU_ROLE';
  set database_name = 'FIVETRAN_DATABASE';
  set schema_name = 'STRIPE';

  use role sysadmin;

  -- grant dataiku access to database
  grant USAGE
  on database identifier($database_name)
  to role identifier($role_name);

  -- grant dataiku access to schema
  use database identifier($database_name);
  grant USAGE
  on schema identifier($schema_name)
  to role identifier($role_name);

  -- grant dataiku access to tables in schema
  grant SELECT
  on all tables in schema identifier($schema_name)
  to role identifier($role_name);
  
commit;