Issue with configuration of google bigquery dataset access

I need to create a servive account with restrictive permissions to connect Power BI to Bigquery. People who connect to Bigquery with this SA should have read access to only one dataset but the views in this dataset should have access to multiples other dataset views and tables. So far I was able to do it by giving to the SA the following permissions:

• Custom Role with permissions ["bigquery.jobs.create","resourcemanager.projects.get", "bigquery.readsessions.create","bigquery.readsessions.getData"] at project-level
• DataViewer at dataset-level for the dataset I want them to have read-access

I also had to add access block for this read-access dataset views to the others dataset using Terraform. If I don't add that it doesn't work. Here is how it looks like

locals { datasets = toset([ google_bigquery_dataset.source_1.dataset_id, google_bigquery_dataset.source_2.dataset_id, google_bigquery_dataset.source_3.dataset_id, google_bigquery_dataset.source_4.dataset_id ]) } resource "google_bigquery_dataset_iam_member" "sa_dataset_viewer"{ dataset_id = google_bigquery_dataset.final_dataset.dataset_id role = "roles/bigquery.dataViewer" member = "serviceAccount:${google_service_account.powerbi_sa.email}" } resource "google_project_iam_member" "sa_bigquery_job_user" { role = "projects/${var.project}/roles/${google_project_iam_custom_role.bqJobSA.role_id}" project = var.project member = "serviceAccount:${google_service_account.powerbi_sa.email}" } resource "google_bigquery_dataset_access" "dataset_access_for_views" { for_each = local.datasets dataset_id = each.value dataset { dataset { project_id = var.project dataset_id = google_bigquery_dataset.final_dataset.dataset_id } target_types = ["VIEWS"] } } 

But now that I set up this configuration I have a problem. If I want to create a view in my_dataset dataset with a TABLE in a dataset in the for_each above I've got this error:dataset.update error

In the screenshot I'm using dbt to compile my code but I have the same issue, using my credentials, creating and updating this view manually. I need the permissions to update the reference dataset. I know the problem comes from the access block I added because when I remove it I was able to do it again. The solution would be to give me the good permissions to do it again but I don't know the least privileged role I should add to myself and at which level (project or dataset). I initially have Editor role at project-level and I granted myself dataEditor role at the project level but it doesn't work. And I don't want to add too much restrictive permissions at the dataset-level.

1 Answer

Finally, I was able to find a solution by adding dataOwner role for all the datasets

resource "google_bigquery_dataset_access" "dataset_access_for_dev" { for_each = local.datasets dataset_id = each.value role = "OWNER" user_by_email = "[email protected]" } 

while dataEditor can only update table, dataOwner is the only basic role which grant dataset.update permissions. I still don't understand why I need this permission after adding dataset_access_for_views to Terraform but the problem is resolved.