Permission bigquery.tables.updateData denied when querying INFORMATION_SCHEMA.COLUMNS

Question

I'm querying bigquery (via databricks) with a service account with the following roles:

BigQuery Data Viewer
BigQuery Job User
BigQuery Metadata Viewer
BigQuery Read Session User

The query is:

SELECT distinct(column_name) FROM `project.dataset.INFORMATION_SCHEMA.COLUMNS` where data_type = "TIMESTAMP" and is_partitioning_column = "YES"

I'm actually querying via Azure Databricks:

  spark.read.format("bigquery")
  .option("materializationDataset", dataset)
  .option("parentProject", projectId)
  .option("query", query)
  .load()
  .collect()

But I'm getting:

  "code" : 403,
  "errors" : [ {
    "domain" : "global",
    "message" : "Access Denied: Table project:dataset._sbc_f67ac00fbd5f453b90....: Permission bigquery.tables.updateData denied on table project:dataset._sbc_f67ac00fbd5f453b90.... (or it may not exist).",
    "reason" : "accessDenied"
  } ],

After adding BigQuery Data Editor the query works. Why I need write permissions to view this metadata? Any lower permissions I can give?

In the docs I see that only data viewer is required, so I'm not sure what I'm doing wrong.

In the first part of your question, you share the granted role. But on which resource are granted this role? On the table or on the dataset? — guillaume blaquiere

dikesh dikesh · Accepted Answer · 2021-06-24T11:51:19

BigQuery saves all query results to a temporary table if a specific table name is not specified.

From the document, following permissions are required.

bigquery.tables.create permissions to create a new table

bigquery.tables.updateData to write data to a new table, overwrite a table, or append data to a table

bigquery.jobs.create to run a query job

Since the service account already have BigQuery Job User role, it is able to run the query, it needs BigQuery Data Editor role for bigquery.tables.create and bigquery.tables.updateData permissions.

Permission bigquery.tables.updateData denied when querying INFORMATION_SCHEMA.COLUMNS

1 Answers