Introduction to table access controls

This page provides an overview of BigQuery Table ACL.

BigQuery Table ACL lets you set table-level permissions. 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 let a user query the table or view without dataset access.

To set access control on a table or view, use an Identity and Access Management (IAM) policy. After you create a table or view, you can set its policy in the following ways:

  • Using the bq set-iam-policy command (bq command-line tool version 2.0.50 or later).
  • Using the Google Cloud Console.
  • Calling the tables.setIamPolicy method.

A view can also reference other source tables and views that you shared using BigQuery Table ACL.

You can test whether a user has access to a specific table by using the tables.testIamPermissions method. For more information, see Testing permissions.

Access with BigQuery Table ACL is additive. BigQuery Table ACL doesn't support a deny permission.

For more information about setting a policy, 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 logical view and an authorized view is which authority is used for access control. A regular view's access is checked on behalf of the end user's authority. An authorized view's access is checked using the authorized view's own authority.

With the addition of BigQuery Table ACL, you now have the following options for table access:

  • Share a dataset, including all of its underlying tables, with users. This option already exists.

  • Authorize a view, within a dataset that users can access, to access data from another dataset on the view's own authority, not the user's authority. This option is the existing authorized views feature.

  • Share a table or view with specific users without sharing the parent dataset. This option is the BigQuery Table ACL feature.

BigQuery doesn't support authorizing a view to access data from another table on the view's own authority.

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

  • Table-level authorized views aren't supported. But you can use BigQuery Table ACL to share an existing dataset-level authorized 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. Don't set table-level access control on an individual wildcard table. Instead, set access at the dataset level.

  • Similar to wildcard tables, the tables under the INFORMATION_SCHEMA dataset aren't subject to table ACLs. They also require dataset-level permissions.

What's next