Snowflake

The following setup allows Alvin to access your Snowflake's metadata and query history, without being able to touch the underlying data.

1. Create Alvin user and role

The first step is to create the role and user. Below we have used alvin_role and alvin_user, but they can be whatever you like. Then, enable access to Snowflake system usage tables. Alvin also needs data warehouse usage permissions for issuing certain queries. When this step is done, Alvin can access queries from the Snowflake query history.

-- CREATE ALVIN ROLE
create role if not exists alvin_role;
-- ALLOW ALVIN ROLE ACCESS TO THE USAGE / ACCOUNT DATA
grant monitor usage on account to role alvin_role;
grant imported privileges on all schemas in database snowflake to role alvin_role;
-- Allow usage on warehouse
grant usage on warehouse compute_wh to role alvin_role;
-- CREATE ALVIN USER
create user if not exists alvin_user;
-- GRANT ALVIN ROLE TO ALVIN USER
grant role alvin_role to user alvin_user;
alter user alvin_user set password = 'password';

Change snowflake, compute_wh and password and run as is.

2. Grant access to all databases of interest

The final step enables Alvin to access table, view and column names. {db} should be replaced with the database name, and this should be run for all databases of interest. future makes sure that anything created within the database at a later stage is picked up.

grant usage,monitor on database {db} to role alvin_role;
grant usage,monitor on all schemas in database {db} to role alvin_role;
grant usage,monitor on future schemas in database {db} to role alvin_role;
grant references on future tables in database {db} to role alvin_role;
grant references on all tables in database {db} to role alvin_role;
grant references on future materialized views in database {db} to role alvin_role;
grant references on all materialized views in database {db} to role alvin_role;
grant references on future views in database {db} to role alvin_role;
grant references on all views in database {db} to role alvin_role;

An important thing to note on this point is that if you have more granular grants, i.e on schemas in these given databases you will need to issue grants separately for all of them too to avoid them being overridden when tables are created, recreated or added to those schemas:

grant usage,monitor on schema {db}.{schema} to role alvin_role;
grant references on all tables in schema {db}.{schema} to role alvin_role;
grant references on future tables in schema {db}.{schema} to role alvin_role;
grant references on all materialized views in schema {db}.{schema} to role alvin_role;
grant references on future materialized views in schema {db}.{schema} to role alvin_role;
grant references on future views in schema {db}.{schema} to role alvin_role;
grant references on all views in schema {db}.{schema} to role alvin_role;