Set grants on dataset level in BigQuery

Help
1

Hi, i am trying to set specific viewer-access in BigQuery on a dataset through dbt and dbt_project.yml file.

a_folder_in_models:
  +dataset: some_dataset
  +grants:
     roles/bigquery.dataViewer: ['group:group@company.com']

But when i execute dbt run it will set it on table/view level. At my company we wan’t to set these type of view-access to different ad-groups on dataset level. We wan’t to control these through the file dbt_project.yml and not inside every model.

Is there a solution to this? Maybe a macro that will solve it? Or am i just doing it wrong in our dbt_project.yml.

2

Hello, If you are creating datasets via terraform, then tf would be best place for this use case . If you are looking via dbt please use post hooks (defined in dbt_project.yml) or define macro with list of grant SQL statements and call macro on-run-start or on-run-end based on your use case.

I have done the extensive analysis around grants for bq. You can go thru in below link

1 Like
3

No we are creating the datasets via dbt. Looking into creating a macro for setting the grant on dataset level instead.

4

were you able to find any solution to this @chricko ? I have a very similar type of use-case I would like to pursue.

5

dbt doesn’t have a first-class concept of datasets so there is nowhere to hang dataset-level config like grants.

We handled this by removing dbt’s ability to manage datasets (taking away its bigquery.datasets.create permission) and creating datasets with Terraform instead

Note: @Mike Stanley originally posted this reply in Slack. It might not have transferred perfectly.

1 Like