Is it possible to limit a Google service account to specific BigQuery datasets within a project?

4
votes

I've set up a service account using the GCP UI for a specific project Project X. Within Project X there are 3 datasets:

Dataset 1

Dataset 2

Dataset 3

If I assign the role BigQuery Admin to Project X this is currently being inherited by all 3 datasets.

Currently all of these datasets inherit the permissions assigned to the service account at the project level. Is there any way to modify the permissions for the service account such that it only has access to specified datasets? e.g. allow access to Dataset 1 but not Dataset 2 or Dataset 3.

Is this type of configuration possible?

I've tried to add a condition in the UI but when I use the Name resource type and set the value equal to Dataset 1 I'm not able to access any of the datasets - presumably the value is not correct. Or a dataset is not a valid name resource.

UPDATE

Adding some more detail regarding what I'd already tried before posting, as well as some more detail on what I'm doing.

For my particular use case, I'm trying to perform SQL queries as well as modifying tables in BigQuery through the API (using Python).

Case A: I create a service account with the role 'BigQuery Admin'.

This role is propagated to all datasets within the project - the property is inherited and I can not delete this service account role from any of the datasets.

In this case I'm able to query all datasets and tables using the Python API - as you'd expect.

Case B: I create a service account with no default role.

No role is propagated and I can assign roles to specific datasets by clicking on the 'Share dataset' option in the UI to assign the 'BigQuery Admin' role to them.

In this case I'm not able to query any of the datasets or tables and get the following error if I try:

*Forbidden: 403 POST https://bigquery.googleapis.com/bq/projects/project-x/jobs: Access Denied: Project X: User does not have bigquery.jobs.create permission in project Project X.*

Even though the permissions required (bigquery.jobs.create in this case) exist for the dataset I want, I can't query the data as it appears that the bigquery.jobs.create permission is also required at a project level to use the API.

3
pythongoogle-cloud-platformgoogle-bigquery
As @rmesteves mentioned the documentation explains how to limit access to specific datasets. Isn't that what you're looking for? Another approach that might be worthwhile to consider is the use of Authorized Views, but that is meant for restricting access on an even more granular level (query results), albeit it does allow you to create separate datasets for which only specific users could access by assigning them the right permissions. – Maxim
I had previously tried the approach @rmesteves highlighted. I've updated the question to add some more detail on what I'm trying to do and how. In my case allowing access only to authorized views isn't enough - I'd like a user to be able to create/modify/delete any object within a specified dataset. – Fab Dot
Thank you for the explanation. The BQ Admin role does have jobs.create permission. I was wondering how you are setting the permission since as per the error message, I would think that the role was not set to your service account. Please elaborate on the command/steps you use to assign such role. You can also confirm the permissions assigned to your service account in the 'Share Dataset' screen in the text field "Filter by name or role". – rsantiago

3 Answers

3
votes

I'm posting the solution that I found to the problem in case it is useful to anyone else trying to accomplish the same.

Assign the role "BigQuery Job User" at a project level in order to have the permission bigquery.jobs.create assigned to the service account for that project.

You can then manually assign specific datasets the role of "BigQuery Data Editor" in order to query them through the API in Python. Do this by clciking on "Share dataset" in the BigQuery UI. So for this example, I've "Shared" Dataset 1 and Dataset 2 with the service account.

You should now be able to query the datasets for which you've assigned the BigQuery Data Editor role in Python.

However, for Dataset 3, for which the "BigQuery Data Editor" role has not been assigned, if you attempt to query a table this should return the error:

Forbidden: 403 Access Denied: Table Project-x:dataset_1.table_1: User does not have permission to query table Project-x:dataset_1.table_1.

As described above, we now have sufficient permissions to access the project but not the table within Dataset 3 - by design.

0
votes

As you can see here, you can grant access in your dataset to some entities, including service accounts:

Google account e-mail: Grants an individual Google account access to the dataset

Google Group: Grants all members of a Google group access to the dataset Google Apps

Domain: Grants all users and groups in a Google domain access to the dataset

Service account: Grants a service account access to the dataset

Anybody: Enter "allUsers" to grant access to the general public

All Google accounts: Enter "allAuthenticatedUsers" to grant access to any user signed in to a Google Account

I suggest that you create a service account without permissions in BigQuery and then grant the access for a specific dataset.

I hope it helps you.

0
votes

Please keep in mind that access to BigQuery can be granted at project level or dataset level.

The dataset is the lowest level you can assign permissions, so that accounts can access all the resources in the dataset, e.g. tables, views, columns and rows. Permissions at project level permissions, as you have already noticed, are propagated (heritage) for all the datasets in the project.

Regarding your service account, by default Google Cloud assigns it a structure like [email protected], and during the process of sharing the dataset, as commented by @rmesteves, you will need this email address to grant it the desired permissions.

It seems that the steps you described "Name resource type" are not the correct ones. In the BigQuery UI please try:

  1. Click on the dataset name (e.g. Dataset1 in your example) you want to share.

  2. Then, at the right on the screen you will see the option "Share Dataset", click on it.

  3. Follow instructions to set up to your service account a BigQuery role like BigQuery Admin, BigQuery Data Owner, BigQuery User, among others. Check the previous link to be aware of what kind of things the roles can perform.