Skip to content
Tutorials Center
My learning

Topics

Lesson 27 of 37 ยท Security and Cost

IAM Roles and Dataset Access

Understand BigQuery IAM: predefined roles, who needs jobUser versus dataViewer, dataset-level grants, authorized views, and least-privilege patterns.

In BigQuery, access control answers two separate questions: who may run jobs (and therefore pay for compute), and who may see which data. Mixing them up is the most common source of "Access Denied" errors and of overly broad permissions. Google Cloud's Identity and Access Management (IAM) handles both with roles granted to principals at different levels of a resource hierarchy.

This lesson maps the hierarchy, explains the predefined roles you will actually use, and shows how to share a slice of a table safely with an authorized view. The IAM and SQL statements are read-only because they need a real Google Cloud project; the runnable examples model the permission checks with SQL tables so you can practice the logic.

The resource hierarchy

IAM grants flow downward. A role granted on a project applies to every dataset and table inside it, so grant at the lowest level that works.

flowchart TB
  O["Organization"] --> F["Folder"]
  F --> P["Project (billing, jobs)"]
  P --> D["Dataset (location, default access)"]
  D --> T["Table or view"]
  D --> R["Routine / model"]

A role on a higher node is inherited by everything below it.

Remember that a project holds the compute and the bill: running a query is a job in a project. A dataset holds the data and is the usual unit for permissions. Table-, view-, row- and column-level controls refine it further (see row-level and column-level security).

The roles you will use

Role What it allows
roles/bigquery.jobUser Run jobs and queries in a project. Cannot read data by itself.
roles/bigquery.user Run jobs and create datasets; list datasets.
roles/bigquery.dataViewer Read table data and metadata.
roles/bigquery.dataEditor Read and write tables and routines in a dataset.
roles/bigquery.dataOwner Everything on data, including granting access.
roles/bigquery.metadataViewer See table and dataset metadata, not rows.
roles/bigquery.admin Full control. Keep it for a very small group.

A typical analyst needs two grants: jobUser on the project that will be billed, and dataViewer on the dataset or datasets they read. With only dataViewer, a query fails with "does not have bigquery.jobs.create permission". With only jobUser, it fails with "Access Denied: table".

Granting uses the console, gcloud or SQL. Read-only examples:

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

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

Prefer groups over individual users, and service accounts for pipelines, so that access follows team membership rather than personal accounts.

Modelling "who can do what"

A permission check is a join between a principal's grants and the action required. The example models this and shows which principals can run a query on the sales dataset: they need both a job permission on the billing project and a read permission on the data.

SQL Who can run a query on sales?

Press Run (or Ctrl + Enter) to see the result here.

Interns can read data but cannot run jobs, and finance can run jobs but has no data access: both are denied. This is exactly the split you should expect in the real service.

Dataset access and sharing without copying

Sometimes a team needs part of a table but not all of it. Instead of copying data, which goes stale and doubles storage, use one of these:

  • Authorized view. Create a view in a separate dataset that selects the permitted columns and rows, then authorize that view to read the source dataset. Users get access to the view's dataset only; they never need access to the raw tables.
  • Authorized dataset and authorized routine work the same way for every view in a dataset or for a function or procedure.
  • Analytics Hub shares datasets across organizations as listings, so consumers query a linked dataset without a copy.
SQL
CREATE VIEW `my-project.sales_shared.orders_eu` AS
SELECT order_id, order_date, country, total
FROM `my-project.sales_raw.orders`
WHERE country IN ('DE', 'FR', 'ES');

After authorizing sales_shared.orders_eu on the sales_raw dataset, grant the EU team dataViewer on sales_shared only. The view runs with the view owner's access to the source, so the viewers need none.

A view hides columns and rows

You can see the effect with a plain SQLite view: the shared view exposes only the permitted columns and rows.

SQL A view that shares only part of a table

Press Run (or Ctrl + Enter) to see the result here.

Least-privilege habits

  • Grant roles to groups, not users, and review membership regularly.
  • Split duties: pipelines use service accounts with dataEditor on the dataset they write, nothing more.
  • Separate projects for compute and storage if you want to bill teams separately or hide raw data.
  • Set a default table expiration and access on each dataset at creation.
  • Turn on audit logs (Cloud Audit Logs) to see who ran which queries; INFORMATION_SCHEMA.JOBS shows the same inside BigQuery.
  • Do not hand out bigquery.admin for convenience.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. An analyst has dataViewer on a dataset but gets 'does not have bigquery.jobs.create permission'. What is missing?

    Reading data and running jobs are separate permissions; the analyst needs a role that includes bigquery.jobs.create on a project.
  2. 2. What is an authorized view for?

    The view is authorized to read the source, so users of the view need no direct access to the raw tables.
  3. 3. Which grant level is usually best for data access?

    Granting at the lowest level that works follows least privilege, since roles are inherited downward.
  4. 4. Which role lets a user see table schema but not read rows?

    metadataViewer exposes metadata only, not table data.

Summary

  • Running jobs (project) and reading data (dataset) are separate permissions.
  • Analysts usually need jobUser plus dataViewer; pipelines use service accounts with dataEditor.
  • Roles inherit downward; grant at the lowest level and to groups.
  • Authorized views, datasets and routines share data without copying it.
  • Audit logs and INFORMATION_SCHEMA.JOBS show who ran what.

Next: Row-Level and Column-Level Security