Control access with IAM Conditions

This document describes how to use IAM Conditions to control access to BigQuery resources.

IAM Conditions let you grant access to BigQuery resources only if specified conditions are met. For example, you can grant access to a resource for a limited duration or periodically for certain hours of the day. IAM Conditions are supported at the project, folder, and organization level and can be applied on BigQuery datasets, tables, views, routines, and models.

IAM Conditions are useful for granting Identity and Access Management (IAM) permissions to many related resources at the same time, including resources that don't exist yet. To grant permissions to unrelated groups of BigQuery resources, consider using IAM tags.

Before you begin

Enable the IAM API and grant IAM roles that give users the necessary permissions to perform each task in this document.

Enable the IAM API

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

gcloud services enable iam.googleapis.com

Required permissions

To get the permission that you need to apply IAM Conditions to BigQuery resources, ask your administrator to grant you the Project IAM Admin (roles/resourcemanager.projectIamAdmin) IAM role. For more information about granting roles, see Manage access to projects, folders, and organizations.

This predefined role contains the resourcemanager.projects.setIamPolicy permission, which is required to apply IAM Conditions to BigQuery resources.

You might also be able to get this permission with custom roles or other predefined roles.

For more information about IAM roles and permissions in BigQuery, see Introduction to IAM.

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 a BigQuery resource. For more details and examples, see Date/time attribute.
• resource.name: the path of the BigQuery resource. For the format, see the tables in Attribute formats.
• resource.type: the type of the BigQuery resource. For the format, see the tables in Attribute formats.
• resource.service: the Google Cloud service that the BigQuery resource uses. For the format, see the tables in Attribute formats.
• resource.tags: the tags attached to the BigQuery resource. Tags are supported only on BigQuery datasets, tables, and views resources. For the format, see the tables in Attribute formats and in IAM docs.

Attribute formats

When you create conditions for BigQuery datasets, use the following formats:

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

When you create conditions for BigQuery routines, use the following formats:

When you create conditions for BigQuery models, use the following formats:

Replace the following:

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

Add conditions to a resource

To add a condition to a dataset, table, view, routine, or model in BigQuery, see Allow policies with conditions. When you build your conditions, reference the attribute format tables.

Conditions best practices

When you build conditions in BigQuery, use the following best practices:

• Don't use negative conditions for resource.type, resource.name, or resource.service, because unsupported types use the empty string and match nearly all negative conditions. For more details, see negative conditions.
• Include resource.type, resource.name, and resource.service in your condition, even when that level of specificity isn't necessary. This practice helps sustain your conditions as resources in your workflow change so that other resources are not unintentionally included in the future.
• When granting permissions, include the narrowest possible set of permissions to ensure that you don't unintentionally give overly permissive access.
• Use resource.name.startsWith with caution. BigQuery table and view paths are prefixed by their parent project ID and dataset ID. Insufficiently specific conditions might grant too much access. However, you can use the resource.name.startsWith attribute to let users run wildcard queries on tables. For example, access granted using the resource.name.startsWith("projects/my_project/datasets/my_dataset/tables/table_prefix") condition lets users run the SELECT * FROM my_dataset.table_prefix* query.
• Don't add conditions for BigQuery resources other than datasets, tables, views, routines, and models.
• Verify that you are granting the correct permissions on the correct resource. For example, the permission to list resources (bigquery.RESOURCE.list) must be granted at the parent level, but the permission to delete resources (bigquery.RESOURCE.delete) must be granted at the resource level. Dataset deletion, where all contained resources are also deleted, requires table, model, and routine deletion permissions on the dataset.
• Be aware that table snapshots and time travel have no effect on permissions.

Negative conditions

Negative conditions like resource.name != resource can inadvertently grant overly permissive access. Unsupported BigQuery resources have empty resource attributes, meaning they match all negative conditions. Resources in services outside of BigQuery might match negative conditions as well.

Additionally, negative conditions create problems when users run queries with wildcards. For example, consider the negative condition resource.name != /projects/my_project/datasets/my_dataset/tables/secret. This condition appears to grant access to all resources, except a table named secret. However, the user is still able to query that table using a wildcard query, such as SELECT * from my_project.my_dataset.secre*;.

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

Limitations

• You can't add authorized view, authorized routine, or authorized dataset grants with IAM Conditions.
• When a user has conditional access to a dataset or table, they can't modify permissions to that resource through the Google Cloud console. Only the bq tool and API are supported.
• Row-level and column-level access control are not supported directly through IAM Conditions. However, a user with conditional access can grant themselves the BigQuery Admin role (roles/bigquery.admin) on the table, and then modify row and column access policies.
• Changes to IAM policies can take up to five minutes to take effect.
• Users with conditional access might not be able to query INFORMATION_SCHEMA views.
• Users with only conditional table access can't run table wildcard functions.

Examples

The following are examples of use cases for IAM Conditions in BigQuery.

Grant read access to a specific table

This example grants cloudysanfrancisco@gmail.com the BigQuery Data Viewer role for the table_1 table in the dataset_1 dataset. With this role, the user can query the table and access it through the bq tool. The user can't view the table in the Google Cloud console because they don't have the bigquery.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 grants cloudysanfrancisco@gmail.com the BigQuery Metadata Viewer role on the dataset_2 dataset. With this role, the user can list all the resources 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 grants cloudysanfrancisco@gmail.com the BigQuery Data Owner role on all tables in all datasets that start with the public_ 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 grants cloudysanfrancisco@gmail.com the BigQuery Data Owner role on all tables, models, and routines in all datasets that start with the general_ 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 about configuring temporary access by using IAM Conditions.
• Learn more about configuring resource-based access by using IAM Conditions.