Introduction to table access controls

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:

Set a table or view's IAM policy

You can set or update the access control policy on a resource in the following ways:

A view can also reference other source tables and views that you shared using BigQuery table ACL. Access with BigQuery table ACL is additive. BigQuery table ACL doesn't support a deny permission. For more information about additive access control, see Controlling access to views.

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 detailed instructions about how to set an access policy on tables and views, 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 tables, Alice can grant Bob the bigquery.tables.list permission on the dataset. If Bob is granted bigquery.tables.list on the dataset, he can then see a list of all tables in the dataset, not just those he can access. 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 principals, and this policy is attached to a resource. When a principal 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 principals 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".

Table ACL logging

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