英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论