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
andREVOKE
DCL statements. - Using the
bq update
command in thebq
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
In the Explorer panel, expand your project and select a dataset.
In the details panel, click Sharing > Permissions.
Click Add principal.
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,user@gmail.com
.
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.
Click Done.
SQL
The following example uses the
GRANT
DCL statement
to grant the BigQuery
Data Viewer (roles/bigquery.dataViewer
) role to user joe@example.com
on the dataset mydataset
:
In the console, go to the BigQuery page.
In the query editor, enter the following statement:
GRANT
`roles/bigquery.dataViewer`
ON SCHEMA mydataset TO 'user:joe@example.com';Click
Run.
For more information about how to run queries, see Running interactive queries.
bq
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 inmyotherproject
.bq show --format=prettyjson \ myotherproject:mydataset > /tmp/mydataset.json
Make your changes to the
"access"
section of the JSON file. You can add any of thespecialGroup
entries:projectOwners
,projectWriters
,projectReaders
, andallAuthenticatedUsers
. You can also add or modify any of the following:userByEmail
,groupByEmail
, anddomain
.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" } ], ... }
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
.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 inmyotherproject
.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.
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.
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.
Revoking access to a dataset
To revoke access to a dataset:
Console
In the Explorer panel, expand your project and select a dataset.
In the details panel, click Sharing > Permissions.
In the Dataset permissions dialog, expand the role whose membership you want to change.
For the user account that you want to remove, click Delete
.In the Remove member? dialog, click Remove.
Click Done.
SQL
The following example uses the
REVOKE
DCL statement
to remove the BigQuery
Data Viewer (roles/bigquery.dataViewer
) role from user joe@example.com
on the dataset mydataset
:
In the console, go to the BigQuery page.
In the query editor, enter the following statement:
REVOKE
`roles/bigquery.dataViewer`
ON SCHEMA mydataset FROM 'user:joe@example.com';Click
Run.
For more information about how to run queries, see Running interactive queries.
bq
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 inmyotherproject
.bq show --format=prettyjson \ myotherproject:mydataset > /tmp/mydataset.json
Make your changes to the
"access"
section of the JSON file. You can remove any of thespecialGroup
entries:projectOwners
,projectWriters
,projectReaders
, andallAuthenticatedUsers
. You can also remove any of the following:userByEmail
,groupByEmail
, anddomain
.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
.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 inmyotherproject
.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.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.
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.
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.