This document provides an overview of BigQuery Table ACL.
BigQuery Table ACL lets you set table-level permissions on resources like tables
and views. Table-level permissions determine the users, groups, and service
accounts that can access a table or view. You can give a user access to specific
tables or views without giving the user access to the complete dataset. For
example, grant the role BigQuery Data Viewer
(roles/bigquery.dataViewer
) to a user to let that user query just the table or
view without full dataset access.
Access control with Identity and Access Management policies
You can configure the access control policy on a table or view using an Identity and Access Management (IAM) policy.
View a table or view's IAM policy
Once a table or view is created, you can view the IAM policy in the following ways:
- Using the
bq get-iam-policy
command inbq
command-line tool. For examples, see Controlling access to tables and views. - Using the Google Cloud Console.
- Calling the
tables.getIamPolicy
API method.
Set a table or view's IAM policy
You can set or update the access control policy on a resource in the following ways:
- Using the
bq set-iam-policy
command in thebq
command-line tool. - Using the
bq add-iam-policy-binding
command. - Using the
bq remove-iam-policy-binding
command. - Using the Google Cloud Console.
- Calling the
tables.setIamPolicy
API method.
A view can also reference other source tables and views that you shared using BigQuery Table ACL.
Access with BigQuery Table ACL is additive. For more information about additive
access control, see
Controlling access to views.
BigQuery Table ACL doesn't support a deny
permission.
You can test whether a user has access to a specific table or view by using the
tables.testIamPermissions
method. For more information, see Testing
permissions.
For more information about setting a policy, with step-by-step instructions, see Controlling access to tables and views.
Example use case
Alice is a data owner for a company that wants to share the inventory
table
with franchise store owners. The table is in a dataset that contains other
tables that Alice doesn't want to share with franchise store owners.
Bob is a franchise store owner. Alice uses the bq
command-line tool to grant
Bob and the other franchise store owners the role BigQuery Data Viewer
(roles/bigquery.dataViewer
) on the inventory
table. Bob can now query
the inventory
table directly without needing access to the entire dataset.
To let Bob see a list of the tables that Bob has access to, Alice can grant Bob
the bigquery.tables.list
permission on the dataset. The role BigQuery
Metadata Viewer (roles/bigquery.metadataViewer
) includes the
bigquery.tables.list
permission.
IAM policies
Table-level access controls are built on top of IAM. IAM lets you control who (users) has what access (roles) to which resources by setting policies. A policy defines and enforces what roles are granted to what members, and this policy is attached to a resource. When an authenticated member attempts to access a resource, IAM checks the resource's policy to determine whether the action is permitted.
For BigQuery Table ACL, the resource is a BigQuery table, and the members are users of the table.
The following example shows a policy with the following roles:
alice@example.com
has been granted the role BigQuery Data Owner (roles/bigquery.dataOwner
).bob@example.com
has been granted the role BigQuery Data Viewer (roles/bigquery.dataViewer
).
{
"bindings":[
{
"members":[
"user:alice@example.com"
],
"role":"roles/bigquery.dataOwner"
},
{
"members":[
"user:bob@example.com"
],
"role":"roles/bigquery.dataViewer"
}
],
"etag":"ABAC",
"version":1
}
For more information about IAM policies, see Understanding policies.
BigQuery Table ACL permissions
To set or change the access to a table or view, you must have the
bigquery.tables.setIamPolicy
permission. The following
BigQuery predefined roles have the
bigquery.tables.setIamPolicy
permission.
- BigQuery Admin (
roles/bigquery.admin
) - BigQuery Data Owner (
roles/bigquery.dataOwner
)
To retrieve the access that is set on a table or view, you must have the
bigquery.tables.getIamPolicy
permission. The following
BigQuery predefined roles have the
bigquery.tables.getIamPolicy
permission.
- BigQuery Admin (
roles/bigquery.admin
) - BigQuery Data Editor (
roles/bigquery.dataEditor
) - BigQuery Metadata Viewer (
roles/bigquery.metadataViewer
) - BigQuery Data Owner (
roles/bigquery.dataOwner
) - BigQuery Data Viewer (
roles/bigquery.dataViewer
)
Policy change time lag
In general, any policy changes take effect within 60 seconds. But under certain circumstances, it might take up to 7 minutes for such changes to fully propagate across the system. For more information, see Granting, changing, and revoking access to resources. Also, in this document, see Impact on caching.
Impact on caching
If caching is enabled, it's possible for an account to see previously authorized query results after the account no longer has access to the table. Specifically, if the user previously ran the query successfully and then you removed access for the user, the user could get results from the query result cache. BigQuery caches only authorized accesses, and they are cached for only a few minutes.
Impact on time travel
The FOR SYSTEM_TIME AS OF
clause is BigQuery's "time
travel" feature that lets you retrieve data from up to 7 days ago. If you
use this feature, BigQuery applies the current table ACL to your
request. If you had access to the table in the past, but your access has been
removed, you can't access previous versions of the table.
Impact when copying tables
When you copy data to a new table, table ACLs on the source table aren't automatically copied. If you want a table ACL on a new table that you created by copying a table, you need to explicitly set a table ACL on the new table.
Comparison with authorized views
BigQuery also provides access using authorized views. An authorized view lets you share query results with particular users and groups without giving them access to the underlying tables. Authorized view access is always read-only.
For example, the authorized view dept_view
lets user joe@example.com
see the
average salary by department but not the salary of each person in the underlying
salary
dataset. If dept_view
has access to the data source, then
joe@example.com
only needs permissions on the dept_view
.
The main difference between a regular view and an authorized view is which authority is used for controlling access to the source table data. A regular view's access to source table data is checked on behalf of the end user's authority. An authorized view's access to source table data is checked using the authorized view's own authority.
With the addition of BigQuery Table ACL, you have the following options for table access:
- Share a dataset, including all of its source tables, with users. This option is IAM access control set at the dataset level.
- Create an authorized view to access source data which the user does not have IAM access to. The source data is accessed based on the authorized view's own authority, not the user's authority. This option is the authorized views feature.
- Share a table or view with specific users without sharing all of the data in the parent dataset. This option is the BigQuery Table ACL feature.
Compatibility with other BigQuery features
In the IAM access model, permissions are additive. Resource permissions are inherited from a parent resource, as described in the Resource hierarchy. Any permissions added to the resource result in additional access being granted. A table ACL can only allow more access; it can't remove any dataset or Google Cloud project access. If a BigQuery feature can't check table-level access, it falls back to the existing dataset-level access control. As a result, BigQuery Table ACL is compatible with other BigQuery features.
Compatibility with VPC Service Controls
VPC Service Controls uses IAM to control access to services such as BigQuery and Cloud Storage. BigQuery Table ACL uses IAM to provide a deeper granularity of access control on individual BigQuery tables. Because they use IAM in a complementary manner, VPC Service Controls and BigQuery Table ACL are compatible.
Audit logging
Setting a policy on a table or view is an Admin Activity. An Admin Activity is
always logged. To view the logged activity, use Cloud Audit Logs to find
occurrences of methodName
set to "google.iam.v1.IAMPolicy.SetIamPolicy"
.
Limitations
Authorized views at the table level aren't supported. Authorized views can only be granted access to entire datasets. However, you can use BigQuery Table ACL to gives users access to an individual table or view.
Currently, if a table is directly shared without its underlying dataset, the shared table doesn't appear in Data Catalog search results.
When you query wildcard tables, table-level access controls aren't checked. If you intend to share tables that users access using a table wildcard in a query, you must grant user access to the dataset containing the tables.
Similar to wildcard tables, the tables under the
INFORMATION_SCHEMA
dataset aren't subject to table-level access control. They also require dataset-level permissions.
What's next
Read the BigQuery Table ACL FAQ.
For more information about using BigQuery Table ACL, see Controlling access to tables and views.
To set access control at the dataset level, see Controlling access to datasets.