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 didn’t 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 %}