英文:
Losing permissions to table on Snowflake reader account everytime dbt job runs
问题
I have a dbt job running once a day, and the dataset is shared out to a reader account in Snowflake. The dbt job is recreating the dataset as a table daily using create or replace transient table as ...
, which causes the reader account to lose permissions on the table every time the job runs. I tried grant select on future tables in schema my_db.my_schema to share my_share;
but got the following error:
Future grant on objects of type TABLE to SHARE is restricted.
What is the best practice for dealing with this scenario for reader accounts? I've seen this which is a nice solution, but I have to imagine there is a better way than adding a post-hook to every marts model in dbt.
英文:
I have a dbt job running once a day, and the dataset is shared out to a reader account in Snowflake. The dbt job is recreating the dataset as a table daily using create or replace transient table as ...
, which causes the reader account to lose permissions on the table everytime the job runs. I tried grant select on future tables in schema my_db.my_schema to share my_share;
but got the following error:
Future grant on objects of type TABLE to SHARE is restricted.
What is the best practice for dealing with this scenario for reader accounts? I've seen this which is a nice solution, but I have to imagine there is a better way than adding a post-hook to every marts model in dbt.
答案1
得分: 2
将以下内容添加到 dbt_project.yml
:
models:
+copy_grants: true
在Snowflake配置中,copy_grants
默认设置为 false
。
英文:
Add the following to dbt_project.yml
:
models:
+copy_grants: true
copy_grants
is set to false
by default in dbt for Snowflake configuration.
答案2
得分: 1
可以在“创建或替换表”命令中指定“复制授权”子句。这将保留对共享的授权。
如果现有表已与另一个帐户共享,则替换表也将共享。创建或替换表 <table_name> (
英文:
You can specify 'copy grants' clause in the 'create or replace table' command. This would retain the grants to the share.
If the existing table was shared with another account, the replacement table is also shared.
create or replace TABLE <table_name> (<column definitions>) copy grants;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论