BigQuery Access Denied: Permission Issues

[GreenWave1234]

Hey all, I'm trying to set up a data transfer in BigQuery and have continuously ran into the same issue for multiple hours while trying to move my data from a GCS bucket into BigQuery.

Here's the censored version of the error:

Failed to start job for table AR with error PERMISSION_DENIED: Access Denied: Connection ##########.us-central1.#######: User does not have bigquery.connections.delegate permission for connection #######.us-central1.#######.

I have repeatedly added the bigquery.connection.delegate permission as well as other permissions to the service account performing the data transfer, the service account handling the external data connection, as well as the users attempting to execute the data transfer. I have still gotten the same exact error, every single time.

Here's what the service account doing the transfer looks like, minus a custom role that is essentially for BigQuery and GCS admin as well as the bigquery.connection.delegate permission.

Here's a user account, again minus the custom role.

And here is the service account roles responsible for the data connection

Any help would be much appreciated!

[learn2skills]

Hello @GreenWave1234 

BigQuery permissions control access to datasets and tables. Users or services need appropriate roles (e.g., BigQuery Data Editor, BigQuery Data Viewer) to read, write, or manage data. Access is often granted through IAM (Identity and Access Management).

To resolve permission issues when moving data from a Google Cloud Storage (GCS) bucket into BigQuery, follow these steps:

Step 1: Verify IAM Roles

• Check IAM Roles: Ensure that the Google Cloud Identity and Access Management (IAM) roles assigned to your user or service account have the necessary permissions.
• Required Roles: Typically, you need the following roles:
  • roles/storage.objectViewer on the GCS bucket to read the data.
  • roles/bigquery.dataEditor or roles/bigquery.dataOwner on the BigQuery dataset to load data.

Step 2: Grant Necessary Permissions

• Access GCS Bucket: Go to the Google Cloud Console, navigate to the GCS bucket, and check the permissions.
• Add Role: If the necessary roles are missing, add them by clicking on "Add" and selecting the appropriate role for the user or service account.

Step 3: Verify BigQuery Permissions

• Access BigQuery Dataset: In the Google Cloud Console, navigate to the BigQuery section and select the dataset where you want to load data.
• Add Role: Ensure the user or service account has the bigquery.dataEditor or bigquery.dataOwner role for the dataset.

Step 4: Check Service Account Permissions

• Service Account: If using a service account, ensure it has the necessary permissions on both GCS and BigQuery.
• Edit Permissions: Go to IAM & Admin > Service Accounts, select the service account, and adjust permissions as needed.

Step 5: Test the Data Load

• Run Load Job: Attempt to load the data again from GCS to BigQuery.
• Monitor Logs: Check the logs for any specific error messages if the issue persists.

Final Answer

Ensure that the user or service account has roles/storage.objectViewer on the GCS bucket and roles/bigquery.dataEditor or roles/bigquery.dataOwner on the BigQuery dataset. Adjust permissions in the Google Cloud Console as needed.

If the reply provided solves your issue, please mark it as Accepted & UPVOTE to help others easily identify in the community members