Grant & revoke in Big query

Help
, ,
1

Hello @joellabes

dbt core:1.2
dwh: Bigquery
Model type: Incremental

The access is not getting revoked via DBT.Below are the steps which i have followed and could see issue in the dbt.

case one:
I have applied the grants as below so that user someone can access the table.

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

result

user (someone@yourcompany.com) is able to access the table. I can see grant statement in the log

table to check metadata of roles/principles.
.INFORMATION_SCHEMA.OBJECT_PRIVILEGES
where object_schema = “dataset_name”
and object_name = “table_name”

Case two

Now i need to revoke access to user :someone@yourcompany.com and provide access to user :sometwo@yourcompany.com. I have updated the code in model as below.

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

result
after dbt run the revoke got triggered for someone@yourcompany.com and grants got triggered someone@yourcompany.com. The user:someone is unable to access the table and user:sometwo is able to access the table. I can see the revoke and grants statements in log.

use case three
I need to revoke access to the user sometwo@yourcompany.com and as per dbt documentation (Updating our permissioning guidelines: grants as configs in dbt Core v1.2 | dbt Developer Blog) I have left it blank as below in the model.

{%if target.name ==‘dev’%}
{{ config(grants = {‘roles/bigquery.dataViewer’: []}) }}
{%endif%}

Result
user sometwo@yourcompany.com is still able to access the tables. Also i can’t find revoke is getting triggered in logs.

Expectation: The dbt should trigger revoke to remove access to user:sometwo@yourcompany.com

When i ran below query(taken from log) i can’t find the same.
select privilege_type, grantee from gcp-project.region-EU.INFORMATION_SCHEMA.OBJECT_PRIVILEGES
where object_schema = “dataset_name” and object_name = “table_name”
– filter out current user
and split(grantee, ‘:’)[offset(1)] != session_user()

I have changed the query as below and i could find the entry in metadata table.

select privilege_type, grantee from gcp-project.region-EU.INFORMATION_SCHEMA.OBJECT_PRIVILEGES where object_schema = “dataset_name” and object_name = “table_name”

Reference to my previous topic for easy understanding: Using Grants in a target-specific way

Please help me on this?

Regards
Minhaj Pasha

2

Hi @minhajpasha, thanks for the extensive writeup!

I’m not super familiar with grants, but since you’re following the instructions described on the documentation site and the permissions aren’t being revoked as expected, I suspect you’re running into some sort of bug.

Could you instead open an issue on GitHub so the maintainers can look into this for you?

3

Perhaps the best practice to handle your case @minhajpasha, is to use Google Groups instead of users, granting access to your models to specific groups.
Therefore, if you need to revoke the access of someone to a model, you just remove them from the group without changing the code of your dbt project.

1 Like
4

@hans.lemm , Hey agree with you, we are currently using groups to provide access, but the issue persistent if you are using groups too…

Regards
Minhaj