# Grant & revoke in Big query

**URL:** <https://discourse.getdbt.com/t/grant-revoke-in-big-query/5057>\
**Category:** Help\
**Tags:** incremental, bigquery, grants\
**Created:** [October 13, 2022, 2:47pm UTC](https://discourse.getdbt.com/t/grant-revoke-in-big-query/5057 "2022-10-13T14:47:02Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![minhajpasha](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/minhajpasha/32/1159_2.png) [@minhajpasha](https://discourse.getdbt.com/u/minhajpasha)\
**Post date:** [October 13, 2022, 2:47pm UTC](https://discourse.getdbt.com/t/grant-revoke-in-big-query/5057/1 "2022-10-13T14:47:02Z")

</div>

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](mailto: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](mailto:someone@yourcompany.com) and grants got triggered [someone@yourcompany.com](mailto: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](mailto:sometwo@yourcompany.com) and as per dbt documentation ([Updating our permissioning guidelines: grants as configs in dbt Core v1.2 | dbt Developer Blog](https://docs.getdbt.com/blog/configuring-grants)) 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](mailto: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](mailto: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](https://discourse.getdbt.com/t/using-grants-in-a-target-specific-way/4981)

Please help me on this?

Regards  
Minhaj Pasha

---

<div class="post-metadata">

**Author:** ![joellabes](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/joellabes/32/3980_2.png) [@joellabes](https://discourse.getdbt.com/u/joellabes)\
**Post date:** [October 13, 2022, 6:00pm UTC](https://discourse.getdbt.com/t/grant-revoke-in-big-query/5057/2 "2022-10-13T18:00:01Z")

</div>

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](https://github.com/dbt-labs/dbt-core/issues) so the maintainers can look into this for you?

---

<div class="post-metadata">

**Author:** ![hans.lemm](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/hans.lemm/32/1597_2.png) [@hans.lemm](https://discourse.getdbt.com/u/hans.lemm)\
**Post date:** [January 11, 2023, 6:18pm UTC](https://discourse.getdbt.com/t/grant-revoke-in-big-query/5057/3 "2023-01-11T18:18:38Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![minhajpasha](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/minhajpasha/32/1159_2.png) [@minhajpasha](https://discourse.getdbt.com/u/minhajpasha)\
**Post date:** [January 11, 2023, 7:58pm UTC](https://discourse.getdbt.com/t/grant-revoke-in-big-query/5057/4 "2023-01-11T19:58:03Z")

</div>

@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