I am new to dbt and I am trying to grant my tables select privileges after they have been created in my dbt code. So I am trying to create a SHARE between my snowflake account(my_SF) and another snowflake account(A_SF). when I create dbt models and materialize them as tables, they are created successfully. My objective is to share these tables in my snowflake account with another snowflake account. I created a SHARE(123) manually, which is configured to use my database (abc)and Schema(def). The code below is used to grant access to a table (deposit) in the schema
Grant select on table abc.def.deposit to share 123
All these seem to work. and the table was easily shared with the second snowflake account I added to the SHARE.
The problem is, when I run dbt run, the tables are created in my Snowflake account but not visible to the second snowflake account because the SELECT GRANT to the table has been deleted.
Is there a way I can GRANT SELECT privilege to all tables after creation in DBT? I use dbt version 1.0
I tried to use this post-hook but it didnt work
post-hook: |
{% set schema = abc.def%}
{% set share = '123' %}
{% for table in adapter.list_relations(schema=schema, materialized='table') %}
GRANT SELECT ON {{ schema }}.{{ table }} TO SHARE {{ share }};
{% endfor %}
@lee, The Answer to your question no longer needs a post hook except you need soms fine-grained control or something complex outside what dbt provided as of July 2022. You should do these grants with configs at either the project , subfolder or specific model level as documented here docs.getdbt.com/reference/resource-configs/grants This was addressed in the issue specific to snowflake here too github.com/dbt-labs/dbt-snowflake/issues/168
Hope this fixes your issue or at least helps others moving forward.