The only way I can get dbt run to work is by connecting as an accountadmin (bad). Any other role gives me "insufficient privileges on table xxx" when executing dbt models. I am using DBT cloud connecting to snowflake. I have created the following with a role that I wanted to use, but it seems that my grants do not work, to allow running my models with this new role.
my dbt cloud "dev" target profile connects as dbt_user, and creates objects in analytics.dbt_ddumas
Below is my grant script, run by an accountadmin:
There must be an easier way than this below, which is not even working :(
Dave
use role accountadmin;
CREATE ROLE dbt_role
GRANT ROLE dbt_role TO ROLE sysadmin
GRANT USAGE ON WAREHOUSE transform_wh TO ROLE dbt_role
GRANT ALL ON database analytics TO ROLE dbt_role
grant ALL ON ALL schemas in database analytics to role dbt_role;
grant ALL ON future schemas in database analytics to role dbt_role;
grant ALL ON ALL tables in SCHEMA analytics.dbt_ddumas to role dbt_role;
grant ALL ON future tables in SCHEMA analytics.dbt_ddumas to role dbt_role;
grant ALL ON ALL views in SCHEMA analytics.dbt_ddumas to role dbt_role;
grant ALL ON future views in SCHEMA analytics.dbt_ddumas to role dbt_role;
CREATE USER dbt_user PASSWORD = 'Password123' MUST_CHANGE_PASSWORD = FALSE;
GRANT ROLE dbt_role TO USER dbt_user;
I recommend following the excellent article by Claire on the dbt Discourse that covers this exact topic.
It would be helpful to know exactly what table, schema, or database you are unable to read from. You say my dbt cloud "dev" target profile connects as dbt_user, and creates objects in analytics.dbt_ddumas, but what databases and schemas is it reading from? (Where are your sources located)? Most of your grants will be oriented to reading existing data, since dbt Cloud will create your dbt_ddumas schema, and therefore own it and every other relation that it creates.
Assuming your raw data is located in a database called raw, then I would change your script to:
use role accountadmin;
CREATE ROLE dbt_role
GRANT ROLE dbt_role TO ROLE sysadmin
GRANT USAGE ON WAREHOUSE transform_wh TO ROLE dbt_role
grant usage on database raw to role dbt_role;
grant usage on future schemas in database raw to role dbt_role;
grant select on future tables in database raw to role dbt_role;
grant select on future views in database raw to role dbt_role;
grant usage on all schemas in database raw to role dbt_role;
grant select on all tables in database raw to role dbt_role;
grant select on all views in database raw to role dbt_role;
grant usage ON database analytics TO ROLE dbt_role;
grant create schema ON database analytics TO ROLE dbt_role;
CREATE USER dbt_user PASSWORD = 'Password123' MUST_CHANGE_PASSWORD = FALSE;
GRANT ROLE dbt_role TO USER dbt_user;