How to Configure BigQuery User Roles

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up bigquery user roles by separating job rights from data rights. Grant project roles to run queries and dataset roles to read tables. Most teams start by giving broad project roles to every data user. This fast approach lets anyone read or change all tables in a project. Broad roles create big risks when an engineer runs a bad query. They also expose private customer fields like emails and phone numbers to everyone. How bigquery user roles work in Google Cloud Google Cloud splits war

Your coding agents run with your access.

Claude Code reaches prod with a developer's credentials, and approval prompts get clicked through. hoop.dev checks every query, masks sensitive data, and holds risky changes for a human.

You set up bigquery user roles by separating job rights from data rights. Grant project roles to run queries and dataset roles to read tables. Most teams start by giving broad project roles to every data user. This fast approach lets anyone read or change all tables in a project. Broad roles create big risks when an engineer runs a bad query. They also expose private customer fields like emails and phone numbers to everyone.

How bigquery user roles work in Google Cloud

Google Cloud splits warehouse permissions into two main parts. A user needs permission to spend compute money and permission to read data. Project roles give a user the right to start and run jobs. Dataset roles give that same user the right to read or write tables. If you only grant a dataset role, the user cannot run any queries. If you only grant a project role, the user sees no data tables.

Good role design pairs a narrow project role with specific dataset roles. You assign the job runner role at the project level for your team. Then you assign data viewer roles on the exact datasets each team needs. Google Cloud also lets you set rules down to the table or column. Policy tags help you lock down columns that hold card numbers or emails. Groups make this setup much easier to manage over time. You map team groups to roles instead of adding single user emails. When someone joins the team, you add them to the right group.

What native roles solve and what they miss

Native roles are great for locking down who can reach each dataset. They stop an app or analyst from touching projects they do not own. They also help you separate compute billing across different business units. You can block whole teams from editing production tables with simple role rules.

Still, static cloud roles only check permissions when a user logs in. They do not inspect what a query does while it runs in flight. A user with viewer rights on a table can still dump every row by default. Static roles stay active all day and night once you grant them. Engineers often keep high roles for weeks after fixing one production bug. If an attacker steals an active key, they get all those standing rights. Also, column tags take a lot of manual work to keep updated. New tables often ship without tags, leaving national IDs or phone numbers exposed. Masking tools that rewrite matched fields in results help close this data gap.

User Provisioning (SCIM) + BigQuery IAM: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Steps to keep warehouse permissions tight

Start by removing broad project owner and editor roles from daily user accounts. Give human users a basic job runner role at the project level. Create separate groups for data readers, data writers, and pipeline service accounts. Grant read or write roles on datasets only to those specific groups.

Next, separate your production data into its own dedicated cloud project. Do not let developers run ad-hoc test queries inside the production project. Require peer checks for any script that changes production schemas or tables. Review your group memberships every month to drop permissions nobody uses anymore. For temporary fixes, grant write access for an hour and then revoke it.

Check database and API queries on the wire

Identity tools, roles, and network rules decide who opens a session. Setup alone cannot check what each query does or mask sensitive values in a response once a session starts. When workflows touch Postgres, MySQL, SQL Server, MongoDB, ClickHouse, Kubernetes APIs, SSH, or internal HTTP APIs, hoop.dev sits inline in the wire protocol beside those services as an open-source proxy, a sidecar for your Postgres or API. On every query an AI agent or engineer runs against production, hoop.dev decides to pass, mask, block, or hold it for human review.

Your login details and client tools stay as they are while hoop.dev guardrails refuse bad deletes or drops before they run, and hoop.dev data masking rewrites matched sensitive values in responses in memory. Furthermore, hoop.dev holds SOC 2 Type II, is MIT licensed, and writes every query and decision to an audit trail that generates evidence for HIPAA, GDPR, and PCI DSS. See the source on GitHub.

Frequently asked questions

Why can a user with dataset viewer rights not run a query?

Dataset viewer roles only allow a user to read table data. The user also needs a project role that allows starting query jobs.

Should I assign roles to single users or groups?

Always assign roles to groups rather than single user accounts. Groups let you add or remove team members in one central place.

How do I protect private fields like emails in query results?

You can apply column policy tags inside your cloud project for known tables. You can also use runtime masking that rewrites matched fields in results.

The query runs. The emails stay hidden.