Using external tables without granting access to GCS

Question

We have 2 GCP projects project-a and project-b. And we want to give access to some external tables in the first project to users in second project using Authorized Views.

Here's what we've done so far:

create a couple of BigQuery tables in project-a (private dataset) as external tables from GCS parquet files
create a dataset (public dataset) in project project-b in which we created authorized views on the external tables from project project-a

However if we give access to users in the project project-b to query the public views they receive this error:

Access Denied: BigQuery BigQuery: Permission denied while globbing file pattern.

I know this means they should also have read permission on GCS buckets of project-a but we can't grant this permission in GCS.

Is there a way to achieve this? Or maybe another way of doing?

score 1 · Answer 1 · answered Mar 23 '22 at 22:42

1

AFAIK, you must have the permission to access to the external data location to access the data (GCS, Google Sheet, or whatever are located the external data). There is no trick for that

answered Mar 23 '22 at 22:42

guillaume blaquiere

66,369
2
47
76

Thanks! I was hoping that I just missed something in the docs... Do you know if there a possibility to use some service account in order to avoid granting read permission in GCS for every user who runs queries in BigQuery ? I'm trying to avoid replicating data or having to load parquet data in BigQuery storage. – blackbishop Mar 24 '22 at 09:23
1

The requester must have access to the data. If you use an app that has a service account as credential and that perform the query, no problem, the service account must have access to the data (and you can grant it). If you use the external data in interactive mode (use the BigQuery console to query the data), the connected user must have access to the data. This time, no possibility to use a service account... – guillaume blaquiere Mar 24 '22 at 09:42
Other option could be load the external data to bigquery with a [batch job](https://cloud.google.com/bigquery/docs/batch-loading-data) and then create the view from this source instead the external one – Latra Apr 15 '22 at 09:59

blackbishop · Accepted Answer · 2022-10-10T07:37:46.817

This is now possible using BigLake tables. We simply need to create a connection resource in BigQuery then use it to define an external table. Users now only require access to BigQuery tables, no need to set permissions in data location (GCS here).

Create the connection using cloud shell bq command

bq mk --connection --location=REGION --project_id=PROJECT_ID \
    --connection_type=CLOUD_RESOURCE CONNECTION_ID

Show the connection and get the associated service account

bq show --connection PROJECT_ID.REGION.CONNECTION_ID

Grant the resource connection service account access to Cloud Storage bucket
Create a BigLake table using the resource connection:

CREATE EXTERNAL TABLE `PROJECT_ID.DATASET.EXTERNAL_TABLE_NAME`
WITH CONNECTION `PROJECT_ID.REGION.CONNECTION_ID`
OPTIONS (
    format ="TABLE_FORMAT",
    uris = ['FILE_PATH']
);

Using external tables without granting access to GCS

2 Answers2

Linked