Grant permission for tables only in Snowflake with dbt.

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

grant permission for tables only in Snowflake with dbt

问题

I have this configuration in my dbt_project.yml:

models:
company:
marts:
materialized: table
my_schema:
+schema: my_schema
+grants:
select: [ 'REPORTER' ]
intermediate:
materialized: view

I want to grant only for tables, but this applies for tables and views (the intermediate entities).

Is there someway to apply this grant only for the tables of my_schema?
Thank you.

[EDIT]

I achieved success with this. I added the roles for all entities and overwrited the views' roles in sequence:

my_schema:
+schema: my_schema
+grants:
select: [ 'REPORTER', 'ROLE2' ]
intermediate:
materialized: view
+grants:
select: [ 'ROLE2' ]

But I though whether could exist a more straightforward way for granting only on the tables like this:

my_schema:
+schema: my_schema
+grants:
select: [ 'REPORTER' ]
type: table
intermediate:
materialized: view # ROLE2'd not be revoked in views in this case

英文:

I have this configuration in my dbt_project.yml:

models:
  company:
    marts:
      materialized: table
      my_schema:
        +schema: my_schema
        +grants:
          select: [ 'REPORTER' ]
        intermediate:
          materialized: view

I want to grant only for tables, but this applies for tables and views (the intermediate entities).

Is there someway to apply this grant only for the tables of my_schema?
Thank you.

[EDIT]

I achieved success with this. I added the roles for all entities and overwrited the views' roles in sequence:

my_schema:
  +schema: my_schema
  +grants:
    select: [ 'REPORTER', 'ROLE2' ]
  intermediate:
    materialized: view
    +grants:
      select: [ 'ROLE2' ]

But I though whether could exist a more straightforward way for granting only on the tables like this:

my_schema:
  +schema: my_schema
  +grants:
    select: [ 'REPORTER' ]
    type: table
  intermediate:
    materialized: view # ROLE2'd not be revoked in views in this case

答案1

得分: 1

Here's the translated code portion:

一个可能的解决方案是创建一个宏并在后处理中执行它

{# 宏接受一个关系 #}
{% macro setup_table_grants(rel) %}
  {% if rel.is_table %}
    grant select on table {{ rel.name }} to role REPORTER;
  {% endif %}
{% endmacro %}

dbt_project.yml 中:

models:
  company:
    marts:
      materialized: table
      my_schema:
        +schema: my_schema
        post-hook:
          - "{{ setup_table_grants(this) }}"
        intermediate:
          materialized: view
英文:

One possible solution is to create a macro and execute it on a post-hook:

{# Macro takes in a Relation #}
{% macro setup_table_grants(rel) %}
  {% if rel.is_table %}
    grant select on table {{ rel.name }} to role REPORTER;
  {% endif %}
{% endmacro %}

Inside dbt_project.yml

models:
  company:
    marts:
      materialized: table
      my_schema:
        +schema: my_schema
        post-hook:
          - "{{ setup_table_grants(this) }}"
        intermediate:
          materialized: view

huangapple
  • 本文由 发表于 2023年4月11日 05:49:45
  • 转载请务必保留本文链接:https://go.coder-hub.com/75980995.html
匿名

发表评论

匿名网友

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

确定