BigQuery’s IAM permissions are surprisingly granular, allowing you to grant access not just to entire datasets, but down to individual tables and even columns.
Let’s see this in action. Imagine you have a dataset named sales_data in your my-project project. You want to give a data analyst, analyst@example.com, read-only access to the transactions table but restrict them from seeing the customer_id column.
First, you’d grant the analyst the roles/bigquery.dataViewer role at the dataset level. This gives them broad read access.
gcloud bigquery datasets add-iam-policy-binding \
--project=my-project \
--dataset=sales_data \
--member=user:analyst@example.com \
--role=roles/bigquery.dataViewer
This command effectively says, "analyst@example.com, you can read any data within sales_data for now."
Next, to restrict access to specific tables or columns, you need to use a more targeted approach. BigQuery IAM supports resource-level permissions. You can bind IAM policies directly to tables.
To deny access to the transactions table for the analyst@example.com (this is a bit counter-intuitive, we’ll refine this), you’d do:
gcloud bigquery tables remove-iam-policy-binding \
--project=my-project \
--dataset=sales_data \
--table=transactions \
--member=user:analyst@example.com \
--role=roles/bigquery.dataViewer
Wait, that’s not right. We wanted to grant access to most tables but deny it to transactions. And we also want to restrict columns. This is where the real power, and complexity, lies.
BigQuery IAM uses a hierarchical model. Permissions are inherited down the tree: project > dataset > table > column. When you grant a role at a higher level, it applies to all resources below it. To override this, you use conditional IAM policies or denial policies.
Let’s backtrack. The analyst needs read access to sales_data except for the transactions table, and within the tables they can access, they should not see customer_id.
The most effective way to achieve this is by granting the roles/bigquery.dataViewer role at the project level, and then using denial policies to exclude specific resources. However, denial policies are a more advanced feature and can be tricky to manage.
A more common and manageable approach is to grant permissions at the most granular level required. Instead of granting roles/bigquery.dataViewer at the dataset level and then trying to deny access to a specific table, you grant access to each table individually that you want them to access.
So, let’s say sales_data has tables orders, customers, and transactions. The analyst should see orders and customers, but not transactions.
# Grant access to the orders table
gcloud bigquery datasets add-iam-policy-binding \
--project=my-project \
--dataset=sales_data \
--table=orders \
--member=user:analyst@example.com \
--role=roles/bigquery.dataViewer
# Grant access to the customers table
gcloud bigquery datasets add-iam-policy-binding \
--project=my-project \
--dataset=sales_data \
--table=customers \
--member=user:analyst@example.com \
--role=roles/bigquery.dataViewer
Now, the analyst has read access to orders and customers, but not transactions because no explicit permission was granted for it.
To restrict column access, you’d use a roles/bigquery.dataMasker role, which is applied at the table level and specifies which columns are masked. However, this is typically for masking data (e.g., showing only the last 4 digits of a credit card), not for denying access to entire columns to specific users. For true column-level denial, you’d often need to create separate tables or views.
A more practical approach for column restriction is to leverage BigQuery Views. You can create a view that excludes sensitive columns and grant the analyst access to the view instead of the base table.
-- Create a view excluding customer_id from the customers table
CREATE VIEW my-project.sales_data.customers_public AS
SELECT
customer_name,
email
FROM
my-project.sales_data.customers;
Then, grant the analyst access to this view:
gcloud bigquery datasets add-iam-policy-binding \
--project=my-project \
--dataset=sales_data \
--table=customers_public \
--member=user:analyst@example.com \
--role=roles/bigquery.dataViewer
This way, the analyst can query customers_public and see customer_name and email, but they cannot access the original customers table to see customer_id.
The key takeaway is that IAM policies in BigQuery are additive. You grant permissions, and if a resource isn’t explicitly granted access, it’s denied. Overrides and exclusions are handled through more specific bindings or by creating intermediary resources like views. The roles/bigquery.dataViewer role is your workhorse for read access, while roles like roles/bigquery.dataEditor and roles/bigquery.dataOwner grant write and administrative capabilities, respectively, all of which can be scoped down to the table level.
The most surprising thing about BigQuery IAM is how it interacts with the underlying data structure. You can grant a user the roles/bigquery.dataViewer role on a dataset, and they can see all tables within it. However, if you then create a new table in that dataset, they don’t automatically get access to it. Each table is a distinct resource that needs its own IAM binding if you’re not granting permissions at the project or dataset level for all tables.
Understanding the IAM hierarchy and how to apply permissions at the project, dataset, and table levels is crucial for effective access control. The next step often involves managing permissions for service accounts that applications use to access BigQuery data.