Skip to content
This repository has been archived by the owner on Nov 11, 2022. It is now read-only.

BigQueryIO need underlying "bigquery.tables.get permission" access to query a view #557

Open
wli600 opened this issue Mar 14, 2017 · 11 comments

Comments

@wli600
Copy link

wli600 commented Mar 14, 2017

I am running a BigQueryIO like

BigQueryIO.Read.usingStandardSql().fromQuery("SELECT * FROM foo-bar-123456.category_view.markets LIMIT 1000")

but seeing this error,
"The user [email protected] does not have bigquery.tables.get permission for table foo-bar-123456.category.markets"

which category is the base table for view category_view. And with perm opened for user [email protected] to the underlying table, this query works.

Also, without opening up the perm, when I run on console with the same query against the view, it works too

Can you help to take a look what might be the issue or maybe an usage error?

Thanks

PS. for the SDK version, I am running with the latest commit as of March 1,

commit c4bff0b
Merge: fc5fee2 4a9f164
Author: Daniel Halperin [email protected]
Date: Wed Mar 1 20:12:01 2017 -0800

@aaltay
Copy link
Contributor

aaltay commented Mar 16, 2017

@ThatRfernand could you please take a look?

@ThatRfernand
Copy link
Contributor

Hi wli600,

I believe you need to give this user ([email protected]) the right IAM permissions to interact with BigQuery. There is more information in https://cloud.google.com/bigquery/docs/access-control.

You can set the permissions via IAM in Cloud Console: https://console.cloud.google.com/iam-admin/iam/iam-zero. There is more information about IAM in https://cloud.google.com/iam/ .

Hope that helps!

@wli600
Copy link
Author

wli600 commented Mar 16, 2017

Thanks @ThatRfernand for looking at it,

The issue here is that this user does have perm to access the view, as I can use the user's account to query the view from console.

But running with the BigQuery against the view shows error message saying the account is lack of perm to access the table itself, but not the view, which is strange.

@criccomini
Copy link

Further clarification: the view has been granted auth view access. So user has access to view, and view has access to underlying table. Query works fine via UI. Query fails via dataflow because it seems to be directly executing some metadata query directly against the underlying table.

@dhalperi
Copy link
Contributor

dhalperi commented Mar 23, 2017

We're investigating this internally -- the key issue right now is a mismatch between what the BigQuery UI does and what Dataflow does. The BigQuery UI can get information via internal side channels that Dataflow cannot (since we only call public BigQuery APIs).

Will keep you updated.

@kennethmac2000
Copy link

Any news on this @dhalperi?

bigquery.table.get enables the reading of table/view metadata - what table/view metadata does Dataflow need to read?

@criccomini
Copy link

Yea, this is really annoying.

@chamikaramj
Copy link
Contributor

Can you try using the withQueryLocation() property: https://github.com/apache/beam/blob/master/sdks/java/io/google-cloud-platform/src/main/java/org/apache/beam/sdk/io/gcp/bigquery/BigQueryIO.java#L967

Currently we need extra permissions to determine the location of the query but if you set the above, extra permissions should not be required.

@ThatRfernand
Copy link
Contributor

Note that .withQueryLocation() was added to Apache Beam last April, so be sure to use Beam 2.5.0 or above.

@xpat
Copy link

xpat commented Jan 5, 2019

Access Denied: Table bigquery-public-data:san_francisco_bikeshare.bikeshare_trips: The user [email protected] does not have permission to query table bigquery-public-data:san_francisco_bikeshare.bikeshare_trips.

IAM permissions:
pinchepoutine.com.mx
BigQuery Admin
BigQuery Job User
BigQuery User
Billing Account Creator
Owner
Project Creator

[email protected]
BigQuery Admin
BigQuery Job User
BigQuery User
Organization Administrator

@Oliveirakun
Copy link

I had the same issue and solved adding these permissions to the service account used by the compute engine that runs the dataflow job

Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Projects
None yet
Development

No branches or pull requests

9 participants