How to GRANT Permissions in BigQuery

Galaxy Glossary

How do I grant permissions in BigQuery?

GRANT assigns roles or fine-grained privileges to users, groups, or service accounts on BigQuery resources.

Welcome to Galaxy!
You'll be receiving a confirmation email.

In the meantime, follow us on Twitter

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What does GRANT do in BigQuery?<\/h2>GRANT adds a role or specific privilege to one or more principals on a dataset, table, or view. The command updates the resource’s access control list (ACL) immediately.<\/p>

Which privilege types can I grant?<\/h2>BigQuery supports two models: IAM roles (OWNER, WRITER, READER) at the dataset level and fine-grained privileges (SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES) at the table or view level.<\/p>

How do I grant dataset-level access?<\/h2>Use GRANT … ON SCHEMA to assign roles on a dataset. Choose READER for analysts, WRITER for ETL jobs, or OWNER for admins.<\/p>

Dataset GRANT example<\/h3>

The following gives analysts read-only access to the Ecommerce dataset:<\/p>

GRANT READER ON SCHEMA `project.ecommerce` TO "group:analysts@example.com";\n<\/code><\/pre>

How do I grant table-level access?<\/h2>
Use GRANT … ON TABLE when you need tighter control. This lets you expose only the data a team needs.<\/p>

Table GRANT example<\/h3>
The query below lets the billing service read from Orders<\/code> without touching other tables:<\/p> GRANT SELECT ON TABLE `project.ecommerce.Orders`\nTO "serviceAccount:billing@appspot.gserviceaccount.com";\n<\/code>

`How do I revoke permissions?<\/h2>`

Use the REVOKE command with the same syntax to remove a role or privilege. Always test revokes in a staging project first.<\/p>

What are best practices for GRANT?<\/h2>Grant the least privilege required, prefer Google Groups over individual accounts, and audit your ACLs quarterly using `INFORMATION_SCHEMA.<\/code> views.<\/p>`

`Common mistakes and fixes<\/h2>See the section below for two frequent errors and how to resolve them.<\/p>`

Why How to GRANT Permissions in BigQuery is important

How to GRANT Permissions in BigQuery Example Usage


-- Allow the shipping microservice to insert new OrderItems\nGRANT INSERT ON TABLE `project.ecommerce.OrderItems`\nTO "serviceAccount:shipping@appspot.gserviceaccount.com";

How to GRANT Permissions in BigQuery Syntax


-- Dataset-level roles\nGRANT { OWNER | WRITER | READER }\nON SCHEMA `project.dataset`\nTO { "user:user@example.com" | "group:team@example.com" | "serviceAccount:sa@project.iam.gserviceaccount.com" };\n\n-- Table-level privileges\nGRANT { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES }\nON TABLE `project.dataset.table`\nTO { principal_list };\n\n-- Example (ecommerce)\nGRANT SELECT ON TABLE `project.ecommerce.Customers`\nTO "group:marketing@example.com";

Common Mistakes

Granting at the dataset level when only one table is needed exposes extra data. Fix by using GRANT ... ON TABLE for fine-grained control.
Granting to individual users creates maintenance overhead. Fix by creating a Google Group, adding users there, and granting the group instead.

Frequently Asked Questions (FAQs)

Can I grant multiple privileges in one command?

Yes. Separate privileges with commas: GRANT SELECT, INSERT ON TABLE project.dataset.Products TO "group:devs@example.com";

Does GRANT overwrite existing permissions?

No. GRANT appends the new role or privilege to the ACL. Use REVOKE to remove existing entries.

How do I list current grants?

Query project.region-us.INFORMATION_SCHEMA.TABLE_OPTIONS or the dataset’s INFORMATION_SCHEMA.SCHEMATA_OPTIONS views.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams