CODEWITHSUNDEEP
snowflake-cloud-data-platformdbtdatabase-permissionsfivetran
LittleBobbyTables
LittleBobbyTables

Reputation: 4473

Grant access to a specific future table in Snowflake

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?

Upvotes: 4

Views: 2070

Answers (1)

Anders Swanson
Anders Swanson

Reputation: 3961

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!

Upvotes: 4

Related Questions