IAM, authorized views and row/column security

Analytics data is often sensitive, and BigQuery offers fine-grained controls without copying data.

Access in BigQuery is IAM: roles are granted to principals (users, groups, service accounts) on a project, dataset, table or view.

roles/bigquery.dataViewer reads data; dataEditor also writes; dataOwner manages the dataset.
roles/bigquery.jobUser lets a principal run jobs (queries) in a project. Reading data and running queries are separate permissions, often in different projects: an analyst may run jobs in their team's project while reading datasets in a central one, and the job's project is billed.
roles/bigquery.user combines job running with creating datasets; roles/bigquery.admin does everything.

Sharing a subset without granting access to the base tables:

Authorized views: a view in a shared dataset is authorized on the source dataset, so users who can read the view see its results without permissions on the underlying tables. Authorized datasets authorize all views in a dataset at once, and authorized routines do the same for functions.
Row-level security: CREATE ROW ACCESS POLICY ... GRANT TO (...) FILTER USING (expr). Users see only rows matching a policy granted to them; users with no matching policy see no rows.
Column-level security: policy tags from a taxonomy are attached to sensitive columns; only principals with the Fine-Grained Reader role on the tag can query them. Dynamic data masking returns hashed, nullified or partial values instead of an error.

Other tools include VPC Service Controls and customer-managed encryption keys (CMEK), and Analytics Hub for sharing datasets across organizations.

Example

-- Share only aggregated, non-PII data with marketing
CREATE VIEW marketing_share.daily_signups AS
SELECT DATE(created_at) AS day, country, COUNT(*) AS signups
FROM core.users
GROUP BY day, country;
-- Then authorize the view on the core dataset (console: Dataset > Sharing >
-- Authorize views) and grant marketing dataViewer on marketing_share only.

-- Row-level security: EU analysts only see EU rows
CREATE ROW ACCESS POLICY eu_only
ON core.orders
GRANT TO ('group:eu-analysts@example.com')
FILTER USING (region = 'EU');

-- Admins see everything
CREATE ROW ACCESS POLICY all_rows
ON core.orders
GRANT TO ('group:data-admins@example.com')
FILTER USING (TRUE);

Remember

Read data (dataViewer) and run jobs (jobUser) are separate IAM permissions
Authorized views expose results without granting base-table access
Row access policies filter rows; policy tags protect columns

Watch out for

Adding a first row access policy to a table and locking out every user it doesn't name.

Check yourself

A user has dataViewer on a dataset but can't run any query. What is likely missing?
Once a table has a row access policy, what does a user who matches no policy see?

Official docs: docs.cloud.google.com/bigquery/docs/row-level-security-intro

Example

Remember

Watch out for

Check yourself

Builds on

Unlocks