每次运行 dbt 作业时,Snowflake 读取帐户都会丢失对表的权限。

huangapple go评论81阅读模式
英文:

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> () copy grants;

英文:

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 &lt;table_name&gt; (&lt;column definitions&gt;) copy grants;

huangapple
  • 本文由 发表于 2023年5月24日 20:48:58
  • 转载请务必保留本文链接:https://go.coder-hub.com/76323721.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定