Control access with IAM Conditions

This document describes how to useIAM Conditions to control access toBigQuery resources.

IAM Conditions let you grant access to BigQueryresources only if specified conditions are met. For example, you can grantaccess to a resource for a limited duration or periodically for certain hours ofthe day. You can add IAM Conditions at the organization, folder,project, and dataset levels of resources. Allow policies with conditions areinherited by child resources. For more information about resource levels, seeresource hierarchies.

IAM Conditions are useful for granting Identity and Access Management (IAM)permissions to many related resources simultaneously, including resourcesthat don't exist yet. To grant permissions to unrelated groups ofBigQuery resources, consider usingIAM tags.

Before you begin

1. Grant users IAM rolesthat contain the necessary permissions to perform each task in thisdocument.
2. Enable the IAM API.

Required roles

To get the permissions that you need to apply IAM Conditions to BigQuery resources, ask your administrator to grant you the following IAM roles:

• For projects:Project IAM Admin (roles/resourcemanager.projectIamAdmin)
• For datasets:BigQuery Data Owner (roles/bigquery.dataOwner)

For more information about granting roles, seeManage access to projects, folders, and organizations.

These predefined roles contain the permissions required to apply IAM Conditions to BigQuery resources. To see the exact permissions that are required, expand theRequired permissions section:

You might also be able to get these permissions withcustom roles or otherpredefined roles.

For more information about IAM roles and permissions inBigQuery, seeIntroduction to IAM.

Enable the IAM API

To enable the IAM API, select one of the following options:

gcloudservicesenableiam.googleapis.com

View conditional access policy on a dataset

Select one of the following options:

bqshow--format=prettyjsonPROJECT_ID:DATASET>PATH_TO_FILE

Modify conditional access on resources

The following sections describe how to add or remove conditional access todifferent resources.

Add conditions to an organization, folder, or project

To add conditional access to an organization, folder, or project inBigQuery, seeAllow policies with conditions.When building your conditions, refer to theattribute format tables.

Add conditional access to dataset

To add conditions to a dataset, select one of the following methods. When youbuild your conditions, reference theattribute format tables.

"access":[{"role":"roles/bigquery.dataViewer","userByEmail":"cloudysanfrancisco@gmail.com","condition":{"title":"Grant roles/bigquery.dataViewer until 2033","description":"Role expires on December 31, 2032.","expression":"request.time< timestamp('2032-12-31T12:00:00Z')"}}]

Remove conditional access from datasets

To remove conditions from a dataset, select one of the following methods. Whenyou build your conditions, reference theattribute format tables.

Condition attributes

You can set IAM Conditions on your BigQuery resources,based on the following attributes:

• request.time: the time at which the user attempts to access aBigQuery resource. For more details and examples, seeDate/time attribute.
• resource.name: the path of the BigQuery resource. For theformat, see the tables inAttribute formats.
• resource.type: the type of the BigQuery resource. For theformat, see the tables inAttribute formats.
• resource.service: the Google Cloud service that the BigQueryresource uses. For the format, see the tables inAttribute formats.
• resource.tags: the tags attached to the BigQueryresource. Tags are supported only on BigQuery datasets, tables, and views resources. For the format, see the tables inAttribute formats and inIAM docs.

Attribute formats

When you create conditions for BigQuery datasets, use thefollowing formats:

When you create conditions for BigQuery tables and views, use thefollowing formats:

When you create conditions for BigQuery routines, use thefollowing formats:

When you create conditions for BigQuery models, use the followingformats:

Replace the following:

• PROJECT_ID: the ID of the project that contains theresources that you are granting access to
• DATASET_ID: the ID of the dataset that you aregranting access to
• TABLE_ID: the ID of the table or view that you aregranting access to
• ROUTINE_ID: the ID of the routine that you aregranting access to
• MODEL_ID: the ID of the model that you aregranting access to

Conditions best practices

When you build conditions in BigQuery, use the following bestpractices:

• We recommend using positive conditions forresource.type,resource.name,andresource.service for greater accuracy. Since unsupported types arerepresented by an empty string, negative conditions might match a wide rangeof resources. For more information, seenegative conditions.
• Dataset-level IAM conditions should only be used for roles thatapply to resources within the dataset, such as tables, views, models, androutines.They shouldn't be used to grant roles that operate at the dataset or projectlevel, likebigquery.user orbigquery.jobUser.
• Don't use the conditionresource.type == 'bigquery.googleapis.com/Dataset'for dataset-level policies, because it has no effect on authorization. Thisattribute is intended to control access to sub-dataset resources like tables,views, routines, and models.
• Includeresource.type,resource.name, andresource.service in yourcondition, even when that level of specificity isn't necessary. This practicehelps sustain your conditions as resources in your workflow change so thatother resources are not unintentionally included in the future.
• When granting permissions, include the narrowest possible set of permissionsto ensure that you don't unintentionally give overly permissive access.
• Useresource.name.startsWith with caution. BigQuery table andview paths are prefixed by their parent project ID and dataset ID.Insufficiently specific conditions might grant too much access. However, youcan use theresource.name.startsWith attribute to let users run wildcardqueries on tables. For example, access granted using theresource.name.startsWith("projects/my_project/datasets/my_dataset/tables/table_prefix")condition lets users run theSELECT * FROM my_dataset.table_prefix* query.
• Don't add conditions for BigQuery resources other thandatasets, tables, views, routines, and models.
• Verify that you are granting the correct permissions on the correctresource. For example, the permission to list resources(bigquery.RESOURCE.list) must be granted at the parentlevel, but the permission to delete resources(bigquery.RESOURCE.delete) must be granted at theresource level. Dataset deletion, where all contained resources are alsodeleted, requires table, model, and routine deletion permissions on thedataset.
• Be aware thattable snapshots andtime travel have no effect on permissions.

Negative conditions

Negative conditions likeresource.name != resource can inadvertently grantoverly permissive access. Unsupported BigQuery resources haveempty resource attributes, meaning they match all negative conditions. Resourcesin services outside of BigQuery might match negative conditionsas well.

Additionally, negative conditions create problems when users run queries withwildcards. For example, consider the negative conditionresource.name != /projects/my_project/datasets/my_dataset/tables/secret. Thiscondition appears to grant access to all resources, except a table namedsecret. However, the user is still able to query that table using a wildcardquery, such asSELECT * from my_project.my_dataset.secre*;.

Also, negative conditions on tables, routines, and models might give overlypermissive access to their parent datasets. Users might then be able to deletethose resources because deletion permissions are managed at the dataset level.

Limitations

• You can't addauthorized view,authorized routine, orauthorized datasetgrants with IAM Conditions.
• If an incompatibleaccessPolicyVersion is used while viewing a resourcewith conditions, bindings might containwithcond followed by a hash value.For more information, seeTroubleshootingwithcond in policies and role bindings.
• Users with conditional access to a dataset or table cannot modify permissionsfor that resource through the Google Cloud console. Permission modificationsare only supported through the bq tool and the BigQuery API.
• Row-level and column-level access control are not supported directly throughIAM Conditions. However, a user with conditional access can grantthemselves the BigQuery Admin role (roles/bigquery.admin) on the table, andthen modify row and column access policies.
• Changes to IAM policies can take up to five minutes to takeeffect.
• Users with conditional access might not be able to queryINFORMATION_SCHEMA views.
• Users with only conditional table access cannot runtable wildcard functions.

Examples

The following are examples of use cases for IAM Conditions inBigQuery.

Grant read access to a specific table

This example grantscloudysanfrancisco@gmail.com the BigQuery Data Viewer rolefor thetable_1 table in thedataset_1 dataset. With this role, the user canquery the table and access it through the bq tool. The user can't viewthe table in the Google Cloud console because they don't have thebigquery.tables.list permission on the dataset.

{"members":[cloudysanfrancisco@gmail.com],"role":roles/bigquery.dataViewer,"condition":{"title":"Table dataset_1.table_1","description":"Allowed to read table with name table_1 in dataset_1 dataset","expression":resource.name==projects/project_1/datasets/dataset_1/tables/table_1&&resource.type==bigquery.googleapis.com/Table}}

Grant list access to a specific dataset

This example grantscloudysanfrancisco@gmail.com the BigQuery Metadata Viewerrole on thedataset_2 dataset. With this role, the user can list all theresources in the dataset, but they can't perform any queries on those resources.

{"members":[cloudysanfrancisco@gmail.com],"role":roles/bigquery.metadataViewer,"condition":{"title":"Dataset dataset_2","description":"Allowed to list resources in dataset_2 dataset","expression":resource.name==projects/project_2/datasets/dataset_2&&resource.type==bigquery.googleapis.com/Dataset}}

Grant owner access to all tables in all datasets with a specific prefix

This example grantscloudysanfrancisco@gmail.com the BigQuery Data Owner roleon all tables in all datasets that start with thepublic_ prefix:

{"members":[cloudysanfrancisco@gmail.com],"role":roles/bigquery.dataOwner,"condition":{"title":"Tables public_","description":"Allowed owner access to tables in datasets with public_ prefix","expression":resource.name.startsWith("projects/project_3/datasets/public_")&&resource.type==bigquery.googleapis.com/Table}}

Grant owner access to all tables, models, and routines in all datasets that have a specific prefix

This example grantscloudysanfrancisco@gmail.com the BigQuery Data Owner roleon all tables, models, and routines in all datasets that start with thegeneral_ prefix:

{"members":[cloudysanfrancisco@gmail.com],"role":roles/bigquery.dataOwner,"condition":{"title":"Tables general_","description":"Allowed owner access to tables in datasets with general_ prefix","expression":resource.name.startsWith("projects/project_4/datasets/general_")&&resource.type==bigquery.googleapis.com/Table}},{"members":[cloudysanfrancisco@gmail.com],"role":roles/bigquery.dataOwner,"condition":{"title":"Models general_","description":"Allowed owner access to models in datasets with general_ prefix","expression":resource.name.startsWith("projects/project_4/datasets/general_")&&resource.type==bigquery.googleapis.com/Model}},{"members":[cloudysanfrancisco@gmail.com],"role":roles/bigquery.dataOwner,"condition":{"title":"Routines general_","description":"Allowed owner access to routines in datasets with general_ prefix","expression":resource.name.startsWith("projects/project_4/datasets/general_")&&resource.type==bigquery.googleapis.com/Routine}}

What's next

• Learn more aboutconfiguring temporary access byusing IAM Conditions.
• Learn more aboutconfiguring resource-based accessby using IAM Conditions.