2
votes

I'm trying to setup customer access to some of my BigQuery data. I'll start off with my requirements, then what I think the solution needs to be, though I'm not sure how to execute.

Requirements

  1. Separate billing per customer for queries
  2. I don't want to make my dataset public
  3. Read only access to specific datasets
  4. Accessible via Excel connector
  5. No access rights to my main project
  6. They manage their own access privileges, I don't want to have to add and remove individual users from direct dataset access on behalf of all our clients.
  7. Nice to have - Web UI access

What I've Done

  • Created a new Google Developer Project
  • Added a view-only user on that project
  • Added a service account
  • Granted access to my BigQuery dataset to the service account

Here are the options for granting dataset access from the documentation:

Google BigQuery Dataset Permissions Settings I imagine that I need to setup some sort of special group, but I can't figure out how to do it.

Thanks in advance!

1

1 Answers

5
votes

In BigQuery there are two different concepts:

  1. The first one is billing (for queries and any other billable activity) that is linked with a Google Cloud Project.
  2. The second one is access to a dataset.

Having said that, to fulfil your requirements you'd create a separate project for each of the customers, and grant access to the datasets in the granularity that you would want.

That way you would have the costs for each of the projects separated but billed to you. Be careful to give them only read access to the project, unless you want them to be able to create other services like VM or deploy GAE apps, as they'd be billed to you as well.

For example dataset [MyDatasetA] to users X and Y in projects Project1 and Project2, but access to [MyDatasetB] to users Y and Z in projects Project2 and Project3.

Thus, each project is accountable for the queries their users run, and you have your access control on each dataset without it being public.

  1. Separate billing per customer for queries. Done with the independent projects.
  2. I don't want to make my dataset public. Done with fine grained control access.
  3. Read only access to specific datasets. Same as above.
  4. Accessible via Excel connector. It should work without problems as they'd be first class BQ users.
  5. No access rights to my main project. Again possible if they are restricted to their own projects.
  6. They manage their own access privileges. This is trickier. I think they'd need more than read access to the datasets or more than read access to the projects to be able to add new users, if you use the project groups as access control.
  7. Nice to have - Web UI access. Check out https://bigquery.cloud.google.com/

The project groups are groups that allow to select members with Viewer, Developer or Owner roles in one click, without the hassle of adding each member manually.

You get already three groups set-up for you to use: Viewers, Editors and Owners of the original project.

But you may create your own Google Groups and give those groups the permission you want.

The hint when doing so, is that new users will usually need to Display your project so that it appears in the BQ online browser. This is done by clicking on the arrow to the side of the project name in the BQ online browser followed by Switch to project then Display project with the project name that the Dataset belongs to.

Edit: Improved the explanation about Group access