Permission Denied Error When Accessing Google Drive via BigQuery Service Account

Question

I am encountering a persistent issue when trying to access data stored in Google Drive through a BigQuery external table using a service account. Despite having the Google Drive API enabled, the service account added to the Google Sheet and the service account set up, I keep receiving a permission denied error related to Drive credentials.

Issue Description:

When executing queries from an external application using the service account, I receive the following error:

"Access Denied: BigQuery BigQuery: Permission denied while getting Drive credentials."

This error occurs consistently across different queries attempting to access the external table linked to a Google Shet source.

Steps Taken:

• Service Account Setup: The service account is created and used for querying.
• API Enablement: Google Drive API is enabled in the project.
• IAM Permissions: I attempted to add relevant permissions to the service account. However, I couldn't find roles specifically related to Google Drive (like roles/drive.file) in the IAM "Add permissions" window. -API Restrictions: I am unsure how to check for any API restrictions that might be applied to the service account or the project.

Questions:

• Missing Roles: Why can't I find the roles/drive.file or similar roles in the IAM permissions list? Is there a specific way to add Drive-related roles to a service account?

• API Restrictions: How can I check if there are any API restrictions at the project or organization level that might be affecting the service account's access to Google Drive?

• Alternative Solutions: If direct access continues to be an issue, what are the best practices for setting up a synchronization mechanism between BigQuery and an external Google Drive table?

Any insights or suggestions from the community would be greatly appreciated, as I've hit a roadblock with this issue.

Answer 1

You need to provide scope with the credentials -

Using python bigquery client

SCOPES = [
"https://www.googleapis.com/auth/bigquery",
"https://www.googleapis.com/auth/drive.readonly"]

from google.cloud import bigquery
from google.auth import default
from google.auth.transport.requests import Request

credentials, project = default(scopes=SCOPES)
credentials.refresh(Request())
bq_client = bigquery.Client(credentials=credentials, project=<GCP_PROJECT>)

Using Airflow composer (BigQueryHook)

• Modify connection configuration (In my case it was default connection i.e google_cloud_default) via Airflow composer web UI's Admin > connections tab to provide scope.