How can I give access to my customers, so they can access big query

[Alex Bjørlig]

I am co-founder and developer at 21RISK, a small SAAS startup. We authenticate our users with Auth0 and they do their stuff in our web-application.

We build a rather nice data warehouse in Big Query, both for internal and external BI. My objective is now to give access, so certain users can access Big Query, with some row-level security. I found this question on SO that is pretty much identical, and the suggested solution is to create a subdomain and use the Identity Platform to create "access" accounts for the needed users.

Is that something you would recommend? Is it possible - or do you have other ideas/insights?

Please let me know if I should provide some better examples, or clarify details ☺️ 

[Fady Abdallah]

I believe John’s answer on Stackoverflow addresses how to authenticate users without Google accounts using Identity Platform (not to be confused with Cloud Identity). He meant that users can authenticate (sign in) to the application using Identity Platform as a frontend. As for access (roles and permissions and not authentication), you need the Application to use a Google account (service account) with proper IAM roles/permissions on the project to be able to execute BigQuery operations.

User > authentication to app > app executes operations with SA. 

Theoretically, if you already have an authentication mechanism for your users, you just need to code the application to use a service account that would execute BigQuery operations. Here is a quickstart guide using Client libraries that you may try.

If you meant that you want to give direct access to Bigquery such as using it in console or direct API executions, each user would need a Google Account (Gmail, Workspace, or Cloud Identity), and each account should have IAM roles/permissions assigned. You can further limit access through dataset level permissions as per this document. I believe the same can be implemented with the app service account.

Here is also a document about different ways to authenticate to Bigquery API that might help. I hope the above helps.

[Alex Bjørlig]

Thanks for the help. I will try to clarify some things.

Our application already has an authentication mechanism (auth0). We already use service accounts to access various Google Producs server-side. However, my question is about our users, accessing Big Query directly. The goal is for our our end-users to open Power BI and click "connect" and start building a dashboard in minutes.

If I understand the last part of the reply, each user would need a Google Account. Gmail and Workspace are off the tabel - for many reasons. Then there is Cloud Identity left. I imagine the solution would work by syncing our users (with roles/permissions) to Cloud Identity - is that right?. But let's take a specific user in our system "example...@21risk.com".

• We write some server code that "syncs" this user to Cloud Identity
• When "example...@21risk.com" then tries to login to Google when connecting to Big Query the first time, what email should he use? Can Cloud Identity "redirect" the user to Auth0 for the authentication part - or how does that work?
• Is the Cost of Cloud Identity not 6$ / month? (if the price is really 6$ / user / month, then I guess we will have to build a different solution).

I don't understand the last part saying "I believe the same can be implemented with the app service account". What? What is the app service account?

A different approach could maybe be to create somewhere in out front-end application where a user can download a service-account, generated by our server code. I would prefer something where each user could sign in - but it seems like it's not possible.

[Fady Abdallah]

You are correct about direct access. You can implement either Cloud Identity accounts or use service accounts. For the latter I am not sure if it is possible with Power Bi. Maybe as a test create a service account, grant it the necessary roles, and try to connect directly.

As for cloud Identity, and depending on your user-base, you can use the free edition. Here is a document about the differences that might help. Here is a document on how to sign up. You may also check those documents for increasing the user cap and requesting additional licenses

Now it is hard to advise how to implement it with auth0 as it is not my area of expertise. Perhaps reaching the Auth0 community might help. I found this document about using Auth0 as an identity provider with Workspace but not sure if it would help you with your use-case especially with Cloud Identity. 

As for my comment about the service account in my past message, I meant that you can also limit access to datasets with the indirect access scenario just like direct access with user accounts.  I hope the above helps.

[Alex Bjørlig]

Thanks for the follow up. I tried enabling Cloud Identity, but there is one thing I don't understand about this solution.

Let's say that one of our existing users actually used a gmail email address when signing up for his account. Can I then create an identity in "Cloud Identity" with that e-mail?

I think it sounds more and more like service accounts is the only feasible way 🧐

[Fady Abdallah]

Cloud Identity is pretty much a stripped version of Workspace. You create accounts with it with your domain name “ ab...@domainname.com”  Now if the member already has a gmail account, you can grant them direct access without Cloud Identity. My assumption is that they won’t need to authenticate through Auth0 as they are already authenticated with Google. The idea initially was that if a user does not have any Google Account, they use Cloud Identity.