BigQuery access control and IAM roles

Who can read, change and bill queries in BigQuery. The IAM roles that matter, why jobUser goes on the billing project and dataViewer on the data, table-level grants, and GRANT and REVOKE in SQL.

BigQuery access is controlled by IAM roles, and a query needs two separate kinds of permission: the right to run a job in some project (bigquery.jobs.create, usually from roles/bigquery.jobUser) and the right to read the tables it touches (bigquery.tables.getData, usually from roles/bigquery.dataViewer). The first is checked on the project that runs and pays for the query, the second on each table. They can be, and often are, in different projects.

Roles are granted on a level and flow down

You grant a role on an organization, a folder, a project, a dataset, or a single table, view or routine. It applies to everything below that point, and a person’s permissions are the union of all roles they hold at every level. Grants only add. A role granted higher up can’t be narrowed by a grant lower down, so if someone can read a table, look for the grant above it.

Connections, saved queries and a few other BigQuery objects take grants too, but these levels are what you use day to day.

The roles that matter

We listed the permissions of each predefined role with gcloud iam roles describe. These are the ones that decide who can do what:

Three details in the permission lists surprise people:

• No data role can run a query. dataViewer, dataEditor and dataOwner contain no bigquery.jobs.create. A user with only dataViewer can see a table in the console and gets a permission error when they try to query it.
• bigquery.user is not a viewer. It has jobs.create, can create datasets and list tables, but has no tables.getData. jobUser is smaller: jobs.create and a few project-listing permissions.
• dataViewer also includes bigquery.tables.export and bigquery.tables.createSnapshot. Someone who can read a table can export it to Cloud Storage and snapshot it. If that is too much, use a custom role.

The admin role also holds jobs.createGlobalQuery and the permission to set IAM policies on datasets, tables and reservations. Keep it for the people who administer the project.

Why jobUser goes on the billing project and dataViewer on the data

A query runs as a job in one project. Its cost lands on that project (with on-demand pricing) or on the project’s reservation, and its quotas come from there. The tables it reads can live anywhere. That gives the usual setup for a team:

• roles/bigquery.jobUser on the project the team runs queries in.
• roles/bigquery.dataViewer on the dataset (or tables) in the project that holds the data.

The data project never sees a bill for reads, and the team can’t run jobs there. The query is written with the full name, `data-project.sales.orders`, and billed to wherever it was started. See projects, datasets and tables for the billing project, and How a query is billed.

Granting dataViewer on the whole project instead of a dataset also works, but it covers every dataset created there later.

Table-level grants

A grant on a table or view is stored in the object’s own IAM policy, not in the dataset’s. We granted dataViewer on a table and read the policy back:

bq get-iam-policy my-project:sales.orders

{
  "bindings": [
    {
      "members": ["user:[email protected]"],
      "role": "roles/bigquery.dataViewer"
    }
  ],
  "etag": "BwZdMunB/B0=",
  "version": 1
}

Only roles that fit the resource are accepted. We tried to grant roles/bigquery.jobUser on a table and got Role roles/bigquery.jobUser is not supported for this resource: a role that exists only to run jobs can’t be granted on a table. bigquery.admin was accepted on the same table.

A dataset grant works differently on the other side. After GRANT dataViewer ON SCHEMA, the dataset’s access list (bq show --format=prettyjson) shows the principal as an entry with "role": "READER". The older dataset roles OWNER, WRITER and READER are still how the dataset’s ACL is stored, and the IAM roles map onto them: dataViewer appears as READER. A table-level grant doesn’t appear in that list.

GRANT and REVOKE in SQL

You can manage access from a query. The statements need bigquery.datasets.update on a dataset, bigquery.tables.setIamPolicy on a table or view, and resourcemanager.projects.setIamPolicy on a project.

GRANT `roles/bigquery.dataViewer`
  ON SCHEMA `my-project`.sales
  TO "user:[email protected]", "group:[email protected]";

GRANT `roles/bigquery.dataEditor`
  ON TABLE `my-project`.sales.orders
  TO "serviceAccount:[email protected]";

REVOKE `roles/bigquery.dataViewer`
  ON SCHEMA `my-project`.sales
  FROM "group:[email protected]";

The resource type is SCHEMA (a dataset), TABLE, VIEW, EXTERNAL TABLE or PROJECT. Principals need their type prefix: user:, group:, serviceAccount:, domain:, specialGroup:allAuthenticatedUsers, or connection: for a BigQuery connection. Statements like these can’t run in the browser, so they are shown as plain code. In Querylab.io, the Share Table and Share Dataset SQL templates generate a GRANT for you to edit and run (see DDL operations).

We ran these against a probe table. What we saw:

• A principal without a prefix is rejected: The member [email protected] is of an unknown type. Please set a valid type prefix for the member.
• A role that doesn’t exist for the resource is rejected with the is not supported for this resource error above.
• REVOKE for a role the principal never had succeeds without an error, so a typo in a revoke script goes unnoticed. Read the policy back to be sure.
• REVOKE works inside a multi-statement script.
• After the REVOKE, bq get-iam-policy returned an empty policy with only an etag.

To audit what is granted, query INFORMATION_SCHEMA.OBJECT_PRIVILEGES. It needs the region qualifier and refuses to run without WHERE object_name = ... at the top level:

SELECT object_schema, object_name, privilege_type, grantee
FROM `region-us`.INFORMATION_SCHEMA.OBJECT_PRIVILEGES
WHERE object_schema = 'sales' AND object_name = 'orders';

For a table we had granted dataViewer, the row was roles/bigquery.dataViewer with grantee user:.... The privilege_type column holds the role name. Leaving out the object_name filter fails with All OBJECT_PRIVILEGES queries must feature a 'WHERE' clause which specifies 'object_name =' at the root level, so you can’t list every grant in a dataset in one query.

“Access denied” can also mean “doesn’t exist”

BigQuery doesn’t tell you whether a table you can’t see exists. A query against a dataset that isn’t there, in a project where you have no access, gives the same error you would get for a real table you may not read:

SELECT order_id
FROM `bigquery-public-data.orders_that_do_not_exist.orders`

The message says “or perhaps it does not exist” because it is the same answer for both. In a project where you do have access, a missing table is a plain Not found: Table ... was not found in location .... So when you see Access Denied for a table you are sure exists, check the project and the dataset’s location as well as the grants.

Sharing data without granting the tables

A role on a table gives access to every column and row in it. To give a team an aggregate or a filtered slice, create a view in another dataset and authorize the view to read the source, then grant the team the view only. See authorized views. For access that depends on who is asking, see row-level security and column-level security.