[Feature] Keep grants for authorized views in sync when using the `grant_access_to` config about dbt-bigquery

[salimmoulouel]

currently i've done it with a for i in range(1,1000000) not very convenient

[dbeatty10]

Thanks for reaching out @salimmoulouel salimmoulouel!

Can you share more about the scenarios in which this would be necessary?

I seem to recall a similar proposal a while back, but I can't find it at the moment. If I recall correctly, we ended up choosing not to pursue it as being out of scope for dbt-core.

[salimmoulouel]

My requirement involves removing outdated grants from a BigQuery authorized view during its recreation. To accomplish this, I utilize a materialization process that iteratively updates a dataset multiple times. To ensure compliance with Google's constraints, particularly the limit on dataset update operations per dataset per 10 seconds, I aim to introduce a delay. This delay helps regulate the frequency of updates and prevents exceeding the specified quota.

I am also open to exploring alternative solutions for removing outdated grants from BigQuery authorized views, especially if they can seamlessly manage multiple projects. Any suggestions in this regard would be greatly appreciated.

[dbeatty10]

Thanks for sharing your particular use-case!

Did you already try configuring grants in dbt, by any chance? I haven't tried this feature with BigQuery authorized views, so I'm not sure how well they work together or not.

[salimmoulouel]

i tried it, to grant access it's easy, but to update them it's a whole other thing.

[dbeatty10]

The grants feature is supposed to update the grants by issuing revoke statements when necessary [1] [2].

When you update your grants config in dbt to remove a user and you check your debug logs, are you finding that it isn't issuing a revoke statement?

[salimmoulouel]

To clarify my issue further, I apologize for any lack of clarity. My objective is to provide authorized views with access to a specific dataset. In subsequent versions of the view, I aim to modify the dataset to which access is granted. To achieve this, I utilize the "grant_access_to" function rather than "grant." However, in the current implementation, when updating the list of datasets granted access, the previous access permissions are not revoked.
https://docs.getdbt.com/reference/resource-configs/bigquery-configs#authorized-views

[dbeatty10]

Thanks for the clarification @salimmoulouel 👍

We don't plan on adding a sleep() function into dbt-core, but we could definitely consider keeping grants in sync within BigQuery authorized views when using the grant_access_to config (originally added within dbt-labs/dbt-core#2517).

I'm going to update the issue title and transfer this to the dbt-bigquery repo for further consideration.