Controlling access to datasets

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

You can also do the following:

• Control access at the table and view level.
• Control access at a higher level in the IAM resource hierarchy.

Overview

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 Identity and Access Management (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 can't be applied during dataset creation in the Google Cloud console, the bq command-line tool, or data definition language (DDL) statements.

You can apply access controls to a dataset after it is created in the following ways:

• Using the console.
• Using the GRANT and REVOKE DCL statements.
• Using the bq update command in the bq command-line tool.
• Calling the datasets.patch API method.
• Using the client libraries.

Before you begin

Grant IAM roles that give users the necessary permissions to perform each task in this document.

Required permissions

To control access to a dataset, you need the following IAM permissions:

• bigquery.datasets.update
• bigquery.datasets.get
• bigquery.datasets.getIamPolicy (lets you control access to a dataset using console)
• bigquery.datasets.setIamPolicy (lets you control access to a dataset using console)

The predefined IAM role roles/bigquery.dataOwner includes the permissions that you need in order to control access to a dataset.

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

Granting access to a dataset

To grant access to a dataset:

Console

1. In the Explorer panel, expand your project and select a dataset.

2. In the details panel, click Sharing > Permissions.

3. Click Add principal.

4. In the New principals field, enter the entity that you want to add. You can add any of the following entities:

   • Google account email: 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 all service accounts and all users on the internet who have authenticated with a Google Account. For example, [e-mail].

5. For Select a role, select BigQuery and choose an appropriate predefined 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.

6. Click Done.

SQL

The following example uses the GRANT DCL statement to grant the BigQuery Data Viewer (roles/bigquery.dataViewer) role to user [e-mail] on the dataset mydataset:

1. In the console, go to the BigQuery page.

   Go to BigQuery

2. In the query editor, enter the following statement:

   GRANT `roles/bigquery.dataViewer`
   ON SCHEMA mydataset
   TO 'user:[e-mail]';
   

3. Click Run.

For more information about how to run queries, see Running interactive queries.

bq

1. 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
   

   Replace the following:

   • 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
   

2. Make your changes to the "access" section of the JSON file. You can add any of the specialGroup entries: projectOwners, projectWriters, projectReaders, and allAuthenticatedUsers. You can also add 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": "WRITER",
      "userByEmail": "service_account_email"
     },
     {
      "role": "READER",
      "groupByEmail": "group_email"
     }
    ],
    ...
   }
   

3. 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
   

   Replace the following:

   • 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
   

4. 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.

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:     "[e-mail]"},
		),
	}

	// 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
}

Java

Before trying this sample, follow the Java setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Java API reference documentation.

View on GitHub Feedback

import com.google.cloud.bigquery.Acl;
import com.google.cloud.bigquery.Acl.Role;
import com.google.cloud.bigquery.Acl.User;
import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.Dataset;
import java.util.ArrayList;

public class UpdateDatasetAccess {

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    // Create a new ACL granting the READER role to "[e-mail]"
    // For more information on the types of ACLs available see:
    // https://cloud.google.com/storage/docs/access-control/lists
    Acl newEntry = Acl.of(new User("[e-mail]"), Role.READER);

    updateDatasetAccess(datasetName, newEntry);
  }

  public static void updateDatasetAccess(String datasetName, Acl newEntry) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      Dataset dataset = bigquery.getDataset(datasetName);

      // Get a copy of the ACLs list from the dataset and append the new entry
      ArrayList<Acl> acls = new ArrayList<>(dataset.getAcl());
      acls.add(newEntry);

      bigquery.update(dataset.toBuilder().setAcl(acls).build());
      System.out.println("Dataset Access Control updated successfully");
    } catch (BigQueryException e) {
      System.out.println("Dataset Access control was not updated \n" + e.toString());
    }
  }
}

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

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

# TODO(developer): Set entity_id to the ID of the email or group from whom
# you are adding access. Alternatively, to the JSON REST API representation
# of the entity, such as a view's table reference.
entity_id = "[e-mail]"

from google.cloud.bigquery.enums import EntityTypes

# TODO(developer): Set entity_type to the type of entity you are granting access to.
# Common types include:
#
# * "userByEmail" -- A single user or service account. For example "[e-mail]"
# * "groupByEmail" -- A group of users. For example "[e-mail]"
# * "view" -- An authorized view. For example
#       {"projectId": "p", "datasetId": "d", "tableId": "v"}
#
# For a complete reference, see the REST API reference documentation:
# https://cloud.google.com/bigquery/docs/reference/rest/v2/datasets#Dataset.FIELDS.access
entity_type = EntityTypes.GROUP_BY_EMAIL

# TODO(developer): Set role to a one of the "Basic roles for datasets"
# described here:
# https://cloud.google.com/bigquery/docs/access-control-basic-roles#dataset-basic-roles
role = "READER"

from google.cloud import bigquery

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

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

entries = list(dataset.access_entries)
entries.append(
    bigquery.AccessEntry(
        role=role,
        entity_type=entity_type,
        entity_id=entity_id,
    )
)
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)
)

Note: For a user to be able to query the tables in a dataset, it is not sufficient for the user to have access to the dataset. A user must also have permission to run a query job in a project. If you want to give a user permission to run a query from your project, give the user the bigquery.jobs.create permission for the project. You can do this by assigning the user the roles/bigquery.jobUser role for your project. For more information, see Access control examples.

Revoking access to a dataset

To revoke access to a dataset:

Console

1. In the Explorer panel, expand your project and select a dataset.

2. In the details panel, click Sharing > Permissions.

3. In the Dataset permissions dialog, expand the role whose membership you want to change.

4. For the user account that you want to remove, click Delete.

5. In the Remove member? dialog, click Remove.

6. Click Done.

SQL

The following example uses the REVOKE DCL statement to remove the BigQuery Data Viewer (roles/bigquery.dataViewer) role from user [e-mail] on the dataset mydataset:

1. In the console, go to the BigQuery page.

   Go to BigQuery

2. In the query editor, enter the following statement:

   REVOKE `roles/bigquery.dataViewer`
   ON SCHEMA mydataset
   FROM 'user:[e-mail]';
   

3. Click Run.

For more information about how to run queries, see Running interactive queries.

bq

1. 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
   

   Replace the following:

   • 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
   

2. Make your changes to the "access" section of the JSON file. You can remove any of the specialGroup entries: projectOwners, projectWriters, projectReaders, and allAuthenticatedUsers. You can also remove 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"
     }
    ],
    ...
   }
   

3. 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
   

   Replace the following:

   • 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
   

4. 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.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.

View on GitHub Feedback

import (
	"context"
	"fmt"

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

// revokeDatasetAccess updates the access control on a dataset to remove all
// access entries that reference a specific entity.
func revokeDatasetAccess(projectID, datasetID, entity string) error {
	// projectID := "my-project-id"
	// datasetID := "mydataset"
	// entity := "[e-mail]"
	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
	}

	var newAccessList []*bigquery.AccessEntry
	for _, entry := range meta.Access {
		if entry.Entity != entity {
			newAccessList = append(newAccessList, entry)
		}
	}

	// Only proceed with update if something in the access list was removed.
	// Additionally, we use the ETag from the initial metadata to ensure no
	// other changes were made to the access list in the interim.
	if len(newAccessList) < len(meta.Access) {

		update := bigquery.DatasetMetadataToUpdate{
			Access: newAccessList,
		}
		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

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

# TODO(developer): Set entity_id to the ID of the email or group from whom you are revoking access.
entity_id = "[e-mail]"

from google.cloud import bigquery

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

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

entries = list(dataset.access_entries)
dataset.access_entries = [
    entry for entry in entries if entry.entity_id != entity_id
]

dataset = client.update_dataset(
    dataset,
    # Update just the `access_entries` property of the dataset.
    ["access_entries"],
)  # Make an API request.

full_dataset_id = f"{dataset.project}.{dataset.dataset_id}"
print(f"Revoked dataset access for '{entity_id}' to ' dataset '{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.