Controlling access to tables and views
This document describes how to use BigQuery Table ACL to control access to tables and views. For an overview of BigQuery Table ACL, see Introduction to table access controls.
After you create a table or view, you can set its policy in the following ways:
- using the console
- using the
bq set-iam-policy
command - calling the
tables.setIamPolicy
method - using the
GRANT
orREVOKE
data control language statements
You can use BigQuery Table ACL to set access on both views and dataset-level authorized views. A view can also reference other source tables and views that you shared using BigQuery Table ACL.
Before you begin
You can use BigQuery Table ACL on BigQuery resources such as tables and views. To learn how to create tables, see Create a table, and for views, see Create a view.
Required permissions
To create a table access policy, you need the bigquery.tables.setIamPolicy
Identity and Access Management (IAM) permission.
Each of the following predefined IAM roles includes the permissions that you need in order to create access policies on a table or view:
bigquery.dataOwner
bigquery.admin
For more information about BigQuery IAM, see Access control with IAM.
Required permission to query tables and views
Users need thebigquery.tables.getData
permission
on all tables and views that their query references. In addition, when querying
a view users need this permission on all underlying tables and views.
However, if you are using authorized views
or authorized datasets, you don't need to
give users access to the underlying source data.
Each of the following predefined IAM roles includes the permission that users need on all tables and views that the query references:
roles/bigquery.admin
roles/bigquery.dataOwner
roles/bigquery.dataEditor
roles/bigquery.dataViewer
Create an access policy
To create an access policy on a table or view:
Console
Go to the BigQuery page.
In the Explorer panel, expand your project and select a dataset.
Expand the dataset and select a table or view.
Click
Share.On the Share page, to add a user (or principal), click
Add principal.On the Add principals page, do the following:
- For New principals, enter a user. You can add individual users, groups, service accounts, and workspace domains.
- From the Select a role drop-down list, select the role that you want to grant to the user.
- Click Add condition to add conditional access for the user.
Click Save to save the changes for the new user.
Click Close to close the Share page.
SQL
To grant roles to users, use the
GRANT
DCL statement:
In the console, go to the BigQuery page.
In the query editor, enter the following statement:
GRANT `ROLE_LIST` ON TABLE DATASET.TABLE_OR_VIEW TO 'USER_LIST';
Replace the following:
ROLE_LIST
: a role or a comma-separated list of roles that contains the permissions that you want to give, such asroles/bigquery.dataViewer
DATASET
: the name of the dataset that the resource is inTABLE_OR_VIEW
: the table or view that you are revoking access fromUSER_LIST
: a comma-separated list of users that the role is granted to, such asuser:joe@example.com
Click
Run.
For more information about how to run queries, see Running interactive queries.
bq
Retrieve the existing policy to a local file.
bq get-iam-policy \ project-id:dataset.table_or_view \ > policy.json
where:
- project-id is your project ID.
- dataset is the name of the dataset that contains the resource (table or view) that you are updating.
- table_or_view is the name of the resource that you are updating.
More examples of identifying a table or view, and redirecting the policy output to a file:
bq get-iam-policy dataset1.table1 > policy.json
bq get-iam-policy --project_id=project1 -t dataset1.table1 > policy.json
bq get-iam-policy project1:dataset1.table1 > policy.json
If you haven't yet added any members to the policy, the
policy.json
file will contain anetag
value and no other fields. See For more information about how to format thepolicy.json
file, see Understanding policies.To add the first member, add a
bindings
field to the policy. For example, to grant the BigQuery Data Viewer (roles/bigquery.dataViewer
) role tojoe@example.com
:"bindings": [ { "members": [ "user:joe@example.com" ], "role": "roles/bigquery.dataViewer" } ]
If you need to add more members to an existing binding, just add the member. This example shows granting
jane@example.com
the BigQuery Data Viewer (roles/bigquery.dataViewer
) role, for a binding that already exists."members": [ "user:joe@example.com", "user:jane@example.com" ], "role": "roles/bigquery.dataViewer" }
Update the policy.
bq set-iam-policy \ project-id:dataset.table_or_view \ policy.json
For information about IAM policy schema versions, see Policy versions.
API
Call
tables.getIamPolicy
to retrieve the current policy.Edit the policy to add members and/or bindings. See the
bq
example for the format of the policy.Call
tables.setIamPolicy
to write the new policy.
For information about IAM policy schema versions, see Policy versions.
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.
Update an access policy
To update an access policy on a table or view:
Console
Go to the BigQuery page.
In the Explorer panel, expand your project and select a dataset.
Expand the dataset and select a table or view.
Click
Share.On the Share page, do the following:
To add new users, follow the steps as shown in Creating an access policy.
To remove access for a user, either search for the user by using the Search field or expand the role to which the user belongs. For the user you want to remove, click Delete
.To modify access for a user, click Edit
.
Click Save when you have made the changes.
Click Close to close the Share page.
SQL
To remove roles from users on a table or view in your dataset, use the
REVOKE
DCL statement:
In the console, go to the BigQuery page.
In the query editor, enter the following statement:
REVOKE `ROLE_LIST` ON TABLE DATASET.TABLE_OR_VIEW FROM 'USER_LIST';
Replace the following:
ROLE_LIST
: a role or a comma-separated list of roles that contains the permissions that you want to give, such asroles/bigquery.dataViewer
DATASET
: the name of the dataset that the resource is inTABLE_OR_VIEW
: the table or view that you are revoking access fromUSER_LIST
: a comma-separated list of users that the role is granted to, such asuser:joe@example.com
Click
Run.
For more information about how to run queries, see Running interactive queries.
bq
Retrieve the existing policy to a local file.
bq get-iam-policy --format=prettyjson \ project-id:dataset.table_or_view \ > policy.json
where:
- project-id is your project ID.
- dataset is the name of the dataset that contains the table that you are updating.
- table_or_view is the name of the table or view you are updating.
More examples of identifying a table or view, and redirecting the policy output to a file:
bq get-iam-policy dataset1.table1 > policy.json
bq get-iam-policy --project_id=project1 -t dataset1.table1 > policy.json
bq get-iam-policy project1:dataset1.table1 > policy.json
Modify policy.json as needed.
For more information about how to format the
policy.json
file, see Understanding policies.For information about IAM policy schema versions, see Policy versions.
Update the policy.
bq set-iam-policy \ project-id:dataset.table_or_view \ policy.json
API
Call
tables.getIamPolicy
to retrieve the current policy.Edit the policy to add members and/or bindings.
For the format required for the policy, see the Policy reference topic.
Call
tables.setIamPolicy
to write the updated policy.
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.
For more information about Identity and Access Management policies, see Understanding policies and the Policy reference topic.
What's next
- Read the FAQ.
- Learn about audit logging of BigQuery Table ACL admin activities at Audit logging.