dbt Core should grant usage on schemas it creates

[jeff-skoldberg-gmds]

problem statement

The grants config in dbt does not completely solve the problem it was intended to solve. grants are meant to be a declarative way to give users access to data, however it is incomplete.

dbt creates schemas, tables, and views. When it creates tables and views, it can grant select or all or whatever <permission> on the table or view it just created.

However, this is not enough for data platforms that also require a user/role/group to have USAGE privilege on the schema in which the model was built. This permission is typically issued with grant usage on schema.

solution

when a dbt run executes on models with SELECT grants, it should also ensure that the granted users have USAGE on schemas containing said models.

This would make dbt's grants actually useful and allow for them to be the "one-stop shop" for managing access to data. Without this functionality, we are stuck with custom alternatives and non-standard workarounds.

background

I recently came across this issue on the Snowflake adaptor:
dbt-labs/dbt-snowflake#715

But this issue is more universal.
Snowflake has "future grants" so that could be one solve for Snowflake. But not all databases are as awesome as Snowflake; most databases do not have "future grants". Even with an awesome workaround for Snowflake, it is still a workaround, and not a single place to declaratively grant users access to data.

questions and unknowns

implementation details

• When should usage on schema be granted? Schema creation time? Before / after table and view grants? On run end?
  • dbt needs to check for changes in config on every run.
• should it inherit the same additional clobber/merge behavior that grants have (e.g. with +)?
  • I think yes!
• Will all users want this behavior? Will anyone want to disable automatic granting of usage on schema?
  • Honestly, I cannot imagine someone wants to grant select without grant usage on schema, but maybe there are edge cases.
  • What should be the default behavior?
  • What config exists to toggle it on / off?

alternatives

dbt_project.yml:

vars:
  dbt_reporter_schemas: ['reporting_schmea1', 'reporting_schema2', 'etc,etc,etc']
  dbt_transformer_schemas: ['staging','intermediate', 'etc,etc,ect']

on-run-end:
  - "{{ grant_schema_permissions(var('dbt_reporter_schemas'), '<role>') }}"
  - "{{ grant_schema_permissions(var('dbt_transformer_schemas'), '<role>') }}"
  - etc, etc, etc

macro:

{% macro grant_schema_permissions(schemas, group) %}

... macro logic omitted for brevity !!
  
    {% do run_query("grant usage on schema " ~ adapter.quote(schema_name) ~ " to group " ~ adapter.quote(group)) %}
{% endmacro %}

This is super duper hacky, but the only way I have found accomplish what I need.

[benc-db]

This same issue comes up in dbt-databricks too.