Troubleshoot IAM permissions in BigQuery

This document shows you how to troubleshoot issues withIdentity and Access Management (IAM) permissions in BigQuery. IAMpermission issues typically result inAccess Denied errors like the following:

• Access Denied: ProjectPROJECT_ID:User does not have bigquery.jobs.create permission in projectPROJECT_ID.
• Access Denied: ProjectPROJECT_ID:User does not have bigquery.datasets.get permission on datasetDATASET.
• User does not have permission to query tablePROJECT_ID:DATASET.TABLE.
• Access Denied: TablePROJECT_ID:DATASET.TABLE:User does not have permission to query tablePROJECT_ID:DATASET.TABLE, or perhaps itdoes not exist.
• Access Denied: UserPRINCIPAL doesnot have permission to perform bigquery.tables.getData on resource'projects/PROJECT_ID/datasets/DATASET/tables/TABLE'.

Before you begin

• To troubleshoot a principal's access to a BigQuery resource,ensure that you have therequired IAM permissions.

Gather information about the issue

The first step in troubleshooting a resource access issue is to determine thepermission that is missing, the IAM principal that was deniedaccess, and the resource the principal was attempting to access.

Get information from the error or job history

To get information about the principal, the resource, and the permissions,examine the output from the bq command-line tool, the API response, or BigQuery in the Google Cloud console.

For example, if you attempt to run a query with insufficient permissions, yousee an error like the following on theJob information tab in theQuery results section of the Google Cloud console.

Examine the error to determine the principal, the resource, and the permissions.

Note: You can also view job details by using thejob history.

In some cases, you may be able to request missing permissions directly from theerror message. For more information, seePermission error messagesin the IAM documentation.

Get information from the Cloud Audit Logs

If the error message is generic, missing information, or if the action failed ina background process, use the Cloud Audit Logs Logs Explorer to getinformation about the error.

1. In the Google Cloud console, go to theLogs Explorer page.

   Go to Logs Explorer

   Alternatively, from the navigation menu, chooseMonitoring>Logs Explorer.

2. In the Logs Explorer, for the logs scope, chooseProject logs.

3. In the query window, enter the following query to get permission-relatederrors from the BigQuery data access logs:

   resource.type="bigquery_resource"ANDlogName="projects/PROJECT_ID/logs/cloudaudit.googleapis.com%2Fdata_access"ANDprotoPayload.status.message:"Access Denied"ORprotoPayload.status.message:"Permission denied"ORprotoPayload.status.code=7

   ReplacePROJECT_ID with your project ID.

4. In the query results, expand the log entry that corresponds to your failedoperation.

5. In theprotoPayload section, expand theauthorizationInfo array, andthen expand each node in theauthorizationInfo array.

   TheauthorizationInfo array shows every permission check performed duringthe API call.

6. To see the cause of the error, look for thegranted: false entry. Thegranted: false entry shows the following information:

   • permission: The IAM permission string that was checked.For example,bigquery.tables.getData.
   • resource: The fully qualified name of the resource that the principalattempted to access. For example,projects/myproject/datasets/mydataset/tables/mytable.
   • principalEmail (if available): Referenced inprotoPayload.authenticationInfo, this is the principal that attemptedthe action.

   

Note: You can find additional BigQuery audit log sample querieson the Google Cloud ObservabilitySample queries page.

Use the Policy Analyzer for allow policies

Policy Analyzer for allow policies lets you find out whichIAM principals have what accessto which BigQuery resources based on yourIAMallow policies.

Note: Policy Intelligence also provides aPolicy Troubleshooter for IAMthat lets you troubleshoot access for a specific principal.

After you gather information about the permissions error, you can use thePolicy Analyzer to understand why the principal lacks the requiredaccess. This tool analyzes all relevant policies, memberships in Google Groups,and inheritance from parent resources such as a project, a folder, and yourorganization.

To use Policy Analyzer for allow policies, you create an analysisquery, specify a scope for the analysis, and then run the query.

1. In the Google Cloud console, go to thePolicy Analyzer page.

   Go to Policy Analyzer

   Alternatively, from the navigation menu, chooseIAM & Admin>Policy Analyzer.

2. ClickCreate Custom Query.

3. On theConfigure your query page, enter the information you gatheredpreviously:

   1. In theSelect the scope section, in theSelect query scopefield, verify that your current project appears or clickBrowse tochoose another resource.

   2. In theSet the query parameters section, forParameter 1, choosePrincipal, and in thePrincipal field, enter the email of theuser, group, or service account.

   3. ClickaddAdd parameter.

   4. ForParameter 2, choosePermission, and in thePermissionfield, clickSelect, choose the BigQuery permission,and then clickAdd. For example, selectbigquery.tables.getData.

   5. ClickaddAdd parameter.

   6. ForParameter 3, chooseResource, and in theResource field,enter the fully qualified resource name. The resource name must includethe service prefix as in the following examples:

      • BigQuery project://cloudresourcemanager.googleapis.com/projects/PROJECT_ID
      • BigQuery dataset://bigquery.googleapis.com/projects/PROJECT_ID/datasets/DATASET
      • BigQuery table://bigquery.googleapis.com/projects/PROJECT/datasets/DATASET/tables/TABLE

4. In theCustom query pane, clickAnalyze> Run query.

5. Examine the query results. The result can be one of the following:

   • An empty list. No results confirm that the principal doesn'thave the required permission. You'll need togrant the principal arole that provides the correct permissions.
   • One or more results. If the analyzer finds an allow policy, someform of access exists. ClickView Binding on each result to view theroles that provide access to the resource that the principal is a memberof. The policy binding shows whether access is granted through groupmembership or inheritance, or whether access is denied by anIAM condition or anIAM deny policy.

Find the correct IAM role that grants the required permissions

After you confirm that the principal doesn't have sufficient access, the nextstep is to find the appropriate predefined or custom IAM rolethat grants the required permissions. The role you choose should adhere to theprinciple of least privilege.

If your organization uses custom roles, you can find the correct role bylisting all custom roles created in your project or organization.For example, in the Google Cloud console, on theRoles page, you canfilter the list byType:Custom to see only custom roles.

To find the correct predefined IAM role, follow these steps.

1. Open theBigQuery permissions sectionof the BigQuery IAM roles and permissionspage.

2. In theEnter a permission search bar, enter the permission you retrievedfrom the error message, job history, or audit logs. For example,bigquery.tables.getData.

   The search results show all predefined BigQuery roles thatgrant the permission.

3. Apply the principle of least privilege: in the list of roles, choose theleast permissive role that grants the required permissions. For example,if you searched forbigquery.tables.getData to grant the ability to querytable data,BigQuery Data Vieweris the least permissive role that grants that permission.

4. Grant the principal the appropriate role. For information about how to grant anIAM role to a BigQuery resource, seeControl access to resources with IAM.

What's next

• For a list of all BigQuery IAM roles andpermissions, seeBigQuery IAM roles andpermissions.
• For more information on troubleshooting allow and deny policies inIAM, seeTroubleshoot policies.
• For more information on the Policy IntelligencePolicy Analyzer, seePolicy Analyzer for allow policies.
• For more information on the Policy Troubleshooter, seeUse Policy Troubleshooter.