Using Grants in a target-specific way

[minhajpasha]

Hello ,
Dbt version :1.2
We have 3 git branches
develop - pointing to GCP dev project
uat - pointing to GCP uat project
prod - pointing to GCP prod project.
If we use the Grant in models/schema.yml file as below , when the models gets executed in dev,uat and prod the required access will granted in all the 3 environments.

• name: student
  description: “student table”
  config:
  grants:
  roles/bigquery.dataViewer: [‘user:someone@yourcompany.com’]

I want this role to be granted in specific target . For eg: i want above grant to be applied in uat environment only.

Any thoughts on this and how to achieve the same.

Thanks in Advance
mp

[joellabes]

@minhajpasha

For eg: i want above grant to be applied in uat environment only.

Do you mean that there are certain users who should only have dataViewer access to the UAT versions of the tables?

[minhajpasha]

Yes, User should have view access to UAT environment only. The same user should not have access to dev and prod environment even if we ran the models in dev and prod.

[joellabes]

OK I spent some time looking at the documentation today and I don’t think that’s currently supported with the grants out of the box, so you have a few options:

• continue to use post hooks to do grants as described in this blog post: Updating our permissioning guidelines: grants as configs in dbt Core v1.2 | dbt Developer Blog
• open an issue on the dbt core repo describing your use case, as it could be a future enhancement (you should do this no matter what)
• consider creating a custom version of the macro that handles the grant logic. You can override almost all of dbt’s functionality (see Did you know? dbt ships with its own project!), so if you made a custom version of the grants macro you could make it do different things based on the target. Hand-wavy but an option to consider.

[minhajpasha]

Hello ,

Is this option possible in sql model??

{{ config(materialized=‘table’ }}

{% if target.name == ‘uat’ %}
{{ config(grants = {‘roles/bigquery.dataViewer’: [‘user:someone@yourcompany.com’]}) }}
{% endif %}

select *
from …

I think we can multiple config in sql model. Please correct me if am wrong.? if yes, think Above should should work?

Regards

[joellabes]

That’s a really good idea! I don’t know if it would work offhand, but please give it a go and report back!

[minhajpasha]

Hello,

It work’s , I have defined it as below.

{% if target.name == 'dev' %}
    {{ config(grants = {'roles/bigquery.dataViewer': ['user:someone@yourcompany.com']}) }}

{% elif target.name == 'uat' %}
    {{ config(grants = {'roles/bigquery.dataViewer': ['user:sometwo@yourcompany.com']}) }}

{% elif target.name == 'prod' %}
    {{ config(grants = {'roles/bigquery.dataViewer': ['user:somethree@yourcompany.com']}) }}
{% endif %} 

But we don’t want this to be added in SQL Models.

We need it to be part of models/schema.yml file.

Can you please share the Git dbt core repo details so that i can raise ticket.

Regards
Minhaj Pasha

[joellabes]

@minhajpasha

It work’s , I have defined it as below.

This is good to know! Thanks for reporting back.

@minhajpasha

But we don’t want this to be added in SQL Models.

We need it to be part of models/schema.yml file.

Can you please share the Git dbt core repo details so that i can raise ticket.

Understood - the issues page for the Core repo is Issues · dbt-labs/dbt-core · GitHub.

I’m going to mark your post as the solution - although it doesn’t work for your use case, it might be useful for other people who come across this in the future