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 allowed to 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, if you grant the BigQuery Data Viewer (roles/bigquery.dataViewer) role, then the user can perform queries on the table or view, even if the user doesn't have 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 (version 2.0.50 and later)
  • using the BigQuery web UI
  • 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 does not support a "deny" permission.

For information on setting a policy, see Using access control on tables or 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 does not 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 BigQuery Data Viewer (roles/bigquery.dataViewer) role on the inventory table. Bob will then be able to query the inventory table directly, without requiring Bob to have access to the entire dataset.

If Alice also wants Bob to be able see a list of tables that he has access to, Alice could grant the bigquery.tables.list permission on the dataset to Bob. The BigQuery Metadata Viewer (roles/bigquery.metadataViewer) role includes the bigquery.tables.list permission.

Cloud IAM policies

Table-level access controls are built on top of Cloud IAM. Cloud 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 which members, and this policy is attached to a resource. When an authenticated member attempts to access a resource, Cloud 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 where:

  • alice@example.com has been granted the BigQuery Data Owner (roles/bigquery.dataOwner) role

  • bob@example.com has been granted the BigQuery Data Viewer (roles/bigquery.dataViewer) role

{
  "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 info about Cloud 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 will take effect within 60 seconds. However, under certain circumstances, it may 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. Additionally, see Impact of caching.

Impact on caching

If caching is enabled, it is possible for an account to see previously authorized query results after the account is no longer granted 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 ("time travel" feature) allows users to retrieve data up to 7 days prior to the current time. If you use this feature, BigQuery will apply the current table ACL to your request. If you previously had access to the table in the past, but that access has been removed, you will not be able to access previous versions of the table.

Impact when copying tables

When you copy data to a new table, any table ACLs on the source table are not automatically copied. If you want a table ACL on a new table created via copy, you need to explicitly set a table ACL on the new table.

Comparison with authorized views

BigQuery also provides access via authorized views. An authorized view allows you to share query results with particular users and groups without giving them access to the underlying tables. Access to an authorized view access is always read-only.

For example, an authorized view, dept_view, allows user joe@example.com to see the average salary by department without having access to the individual pay of each person in the underlying salary dataset. If dept_view is authorized to access 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 in 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 with users, including all of its underlying tables. This option already exists.

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

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

Note that BigQuery does not support authorizing a view, on its own authority, to access data from another table.

Compatibility with other BigQuery features

In the Cloud IAM access model, permissions are additive. Resource permissions are inherited from a parent resource, as described in the Policy hierarchy. Any permissions added to the resource result in additional access being granted. A table ACL can only allow more access, and it cannot remove any dataset or Google Cloud project access. If a BigQuery feature cannot check table-level access, it will simply fallback 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 leverages Cloud IAM to control access to services such as BigQuery and Cloud Storage. BigQuery Table ACL uses Cloud IAM to provide a deeper granularity of access control on individual BigQuery tables. Since they use Cloud 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 are not supported. However, you can use BigQuery Table ACL to share an existing dataset-level authorized view.

  • Currently, shared tables are not exposed in Data Catalog search. If a table is directly shared, without its underlying dataset, it will not appear in Data Catalog search results.

  • Table-level access controls are not checked when querying wildcard tables. You should not set table-level access control on an individual wildcard table. Instead, you should set access at the dataset level.

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

What's next