Grant access to a specific future table in Snowflake

Question

I'm currently using Fivetran to pipe data into Snowflake. Once the data has landed, every 6 hours DBT runs some simple saved queries, which appear as tables in a schema. However, the permissions for various roles keep being reset and they can no longer access the tables in that schema that I gave them permission to see. I suspect this is because DBT is dropping and then re-creating the tables in question.

One possible solution is to grant access to future tables in the schema, e.g.:

grant select on future tables in schema myschema to role some_role;

However, I just want to give access to a single table, not all. Is that possible?

Answer 1

This is totally possible with post-hooks! Used in concert with {{ this }} (docs), they're a powerful and flexible tool.

You have two options for providing access on a single table using post-hooks:

• as a config block at the top of the model definition file (below), or
• in the dbt_project.yml

{{ config(
    post_hook=[
      "grant select on {{ this }} to role some_role;"
    ]
) }}

select ...

Here's a more in-depth guide to granting access in a dbt project

p.s. love your username!