Controlling access to datasets

This document describes how to control access to datasets in BigQuery.

You can also configure BigQuery permissions at a higher level in the Cloud IAM resource hierarchy. For more information on the Cloud IAM resource hierarchy, see Using resource hierarchy for access control in the Cloud IAM documentation.

Overview

Currently, you cannot grant permissions on tables, views, or rows. You can set access controls at the dataset level, and you can restrict access to columns with BigQuery Column-level security. This topic provides information about setting access controls at the dataset level.

Dataset-level permissions determine the users, groups, and service accounts allowed to access the tables, views, and table data in a specific dataset. For example, if you grant the bigquery.dataOwner Cloud IAM role to a user on a specific dataset, that user can create, update, and delete tables and views in the dataset.

You can apply access controls during dataset creation by calling the datasets.insert API method.

Access controls cannot be applied during dataset creation in the Cloud Console, the classic BigQuery web UI, or the command-line tool.

You can apply access controls to a dataset after it is created by:

Using the Cloud Console or classic BigQuery web UI
Using the bq update CLI command
Calling the datasets.patch API method
Using the client libraries

Required permissions

At a minimum, to assign or update dataset access controls, you must be granted bigquery.datasets.update and bigquery.datasets.get permissions. The following predefined Cloud IAM roles include bigquery.datasets.update and bigquery.datasets.get permissions:

bigquery.dataOwner
bigquery.admin

In addition, if a user has bigquery.datasets.create permissions, when that user creates a dataset, they are granted bigquery.dataOwner access to it. bigquery.dataOwner access gives users the ability to update datasets they create.

For more information on Cloud IAM roles and permissions in BigQuery, see Predefined roles and permissions.

Controlling access to a dataset

To assign access controls to a dataset:

Console

Select a dataset from Resources, then click Share dataset near the right side of the window.
In the Share dataset panel, in the Dataset permissions tab, enter the entity you want to add into the Add members text box. You can add any of the following entities:
- Google account e-mail: Grants an individual Google account access to the dataset
- Google Group: Grants all members of a Google group access to the dataset
- Google Apps Domain: Grants all users and groups in a Google domain access to the dataset
- Service account: Grants a service account access to the dataset
- Anybody: Enter "allUsers" to grant access to the general public
- All Google accounts: Enter "allAuthenticatedUsers" to grant access to any user signed in to a Google Account
Note: To add an authorized view to a dataset's access controls, you must use the classic web UI. For more information on authorized views, see Creating authorized views.
For Select a role, select BigQuery and choose an appropriate pre-defined IAM role for the new members. For more information on the permissions assigned to each predefined BigQuery role, see the Roles section of the access control page.
Click Done.

Classic UI

Click the drop-down arrow to the right of the dataset and choose Share Dataset.
In the Share Dataset dialog, for Add People, click the drop-down to the left of the field, and choose the appropriate option. When you apply access controls to a dataset by using the classic web UI, you can grant access to the following users and groups:
- User by e-mail - Gives an individual Google account access to the dataset
- Group by e-mail - Gives all members of a Google group access to the dataset
- Domain - Gives all users and groups in a Google domain access to the dataset
- All Authenticated Users - Gives all Google account holders access to the dataset (makes the dataset public)
- Project Owners - Gives all project owners access to the dataset
- Project Viewers - Gives all project viewers access to the dataset
- Project Editors - Gives all project editors access to the dataset
- Authorized View - Gives a view access to the dataset
  
  Note: Owner (projectOwners), Viewer (projectReaders), and Editor (projectWriters) are primitive project-level roles. The preferred option is to use predefined Cloud IAM roles instead.
Type a value in the text box. For example, if you choose User by e-mail or Group by e-mail, type the user or group's email address.
To the right of the Add People field, click Can view and choose the appropriate role from the list.
- "Can view" (READER) grants bigquery.dataViewer access to the dataset.
- "Can edit" (WRITER) grants bigquery.dataEditor access to the dataset.
- "Is owner" (OWNER) grants bigquery.dataOwner access to the dataset.
  
  For more information on these dataset roles, see Primitive roles for datasets.
Click Add and then click Save changes.

CLI

Write the existing dataset information (including access controls) to a JSON file using the show command. If the dataset is in a project other than your default project, add the project ID to the dataset name in the following format: project_id:dataset.
```
bq show \
--format=prettyjson \
project_id:dataset > path_to_file
```
Where:
- project_id is your project ID.
- dataset is the name of your dataset.
- path_to_file is the path to the JSON file on your local machine.
Examples:

Enter the following command to write the access controls for mydataset to a JSON file. mydataset is in your default project.
```
  bq show --format=prettyjson mydataset > /tmp/mydataset.json
```
Enter the following command to write the access controls for mydataset to a JSON file. mydataset is in myotherproject.
```
  bq show --format=prettyjson \
  myotherproject:mydataset > /tmp/mydataset.json
```

Make your changes to the "access" section of the JSON file. You can add or remove any of the specialGroup entries: projectOwners, projectWriters, projectReaders, and allAuthenticatedUsers. You can also add, remove, or modify any of the following: userByEmail, groupByEmail, and domain.

For example, the access section of a dataset's JSON file would look like the following:

{
 "access": [
  {
   "role": "READER",
   "specialGroup": "projectReaders"
  },
  {
   "role": "WRITER",
   "specialGroup": "projectWriters"
  },
  {
   "role": "OWNER",
   "specialGroup": "projectOwners"
  },
  {
   "role": "READER",
   "specialGroup": "allAuthenticatedUsers"
  },
  {
   "role": "READER",
   "domain": "domain_name"
  },
  {
   "role": "WRITER",
   "userByEmail": "user_email"
  },
  {
   "role": "READER",
   "groupByEmail": "group_email"
  }
 ],
 ...
}

When your edits are complete, use the update command and include the JSON file using the --source flag. If the dataset is in a project other than your default project, add the project ID to the dataset name in the following format: project_id:dataset.

Caution: When you apply the JSON file that contains the access controls, the existing access controls are overwritten.
```
bq update \
--source path_to_file \
project_id:dataset
```
Where:
- path_to_file is the path to the JSON file on your local machine.
- project_id is your project ID.
- dataset is the name of your dataset.
Examples:

Enter the following command to update the access controls for mydataset. mydataset is in your default project.
```
    bq update --source /tmp/mydataset.json mydataset
```
Enter the following command to update the access controls for mydataset. mydataset is in myotherproject.
```
    bq update --source /tmp/mydataset.json myotherproject:mydataset
```
To verify your access control changes, enter the show command again without writing the information to a file.
```
bq show --format=prettyjson dataset
```
or
```
bq show --format=prettyjson project_id:dataset
```

API

Call datasets.insert with a defined dataset resource to apply access controls when the dataset is created. Call datasets.patch and use the access property in the dataset resource to update your access controls.

Because the datasets.update method replaces the entire dataset resource, datasets.patch is the preferred method for updating access controls.

Go

Before trying this sample, follow the Go setup instructions in the BigQuery Quickstart Using Client Libraries. For more information, see the BigQuery Go API reference documentation.

Set the dataset.access_entries property with the access controls for a dataset. Then call the client.update_dataset() function to update the property.

View on GitHub Feedback

import (
	"context"
	"fmt"

	"cloud.google.com/go/bigquery"
)

// updateDatasetAccessControl demonstrates how the access control policy of a dataset
// can be amended by adding an additional entry corresponding to a specific user identity.
func updateDatasetAccessControl(projectID, datasetID string) error {
	// projectID := "my-project-id"
	// datasetID := "mydataset"
	ctx := context.Background()
	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}
	defer client.Close()

	ds := client.Dataset(datasetID)
	meta, err := ds.Metadata(ctx)
	if err != nil {
		return err
	}
	// Append a new access control entry to the existing access list.
	update := bigquery.DatasetMetadataToUpdate{
		Access: append(meta.Access, &bigquery.AccessEntry{
			Role:       bigquery.ReaderRole,
			EntityType: bigquery.UserEmailEntity,
			Entity:     "sample.bigquery.dev@gmail.com"},
		),
	}

	// Leverage the ETag for the update to assert there's been no modifications to the
	// dataset since the metadata was originally read.
	if _, err := ds.Update(ctx, update, meta.ETag); err != nil {
		return err
	}
	return nil
}

Python

Before trying this sample, follow the Python setup instructions in the BigQuery Quickstart Using Client Libraries. For more information, see the BigQuery Python API reference documentation.

Set the dataset.access_entries property with the access controls for a dataset. Then call the client.update_dataset() function to update the property.

View on GitHub Feedback

from google.cloud import bigquery

# Construct a BigQuery client object.
client = bigquery.Client()

# TODO(developer): Set dataset_id to the ID of the dataset to fetch.
# dataset_id = 'your-project.your_dataset'

dataset = client.get_dataset(dataset_id)  # Make an API request.

entry = bigquery.AccessEntry(
    role="READER",
    entity_type="userByEmail",
    entity_id="sample.bigquery.dev@gmail.com",
)

entries = list(dataset.access_entries)
entries.append(entry)
dataset.access_entries = entries

dataset = client.update_dataset(dataset, ["access_entries"])  # Make an API request.

full_dataset_id = "{}.{}".format(dataset.project, dataset.dataset_id)
print(
    "Updated dataset '{}' with modified user permissions.".format(full_dataset_id)
)

Next steps

For more information on creating datasets, see Creating datasets.
For more information on listing datasets in a project, see Listing datasets.
For more information on dataset metadata, see Getting information about datasets.
For more information on changing dataset properties, see Updating datasets.
For more information on creating and managing labels, see Creating and managing labels.