BigQuery Authorized View over multiple projects

1
votes

I'm trying to setup the following permissions:

  • All analysts have access to [Project X]:[Dataset A] which contains views pointing at [Project Y]:[Dataset B].

I followed the tutorial on how to setup an authorized view here https://cloud.google.com/bigquery/docs/share-access-views and gave the analysts the permission bigquery.jobUser on [Project X] and I shared the dataset [Project X]:[Dataset A] with them as viewer.

This setup fails with the error message that the user doesn't have access to the underlying table in [Project Y]:[Dataset B]. If I share the dataset [Project Y]:[Dataset B] as viewer it works. I thought that's not really necessary due to the Authorized view.

Thanks for clarifying this.

2
google-bigquery
you should try again - if you would do all correct it would definitely work! To say something more specific is hard as you just referenced fact that you did all as in that tutorial! so again - the whole point of view authorization is that you will not need to give access to actual tables. hope this clarifies things for you - Mikhail Berlyant
Sorry that I can't be more specific. I thought it might be due to the fact that the tutorial talks about authorized views in the same project and mine are in different projects as described above. - jimmy
I think, different projects should not be a problem. I can verify later today when have chance - Mikhail Berlyant

2 Answers

4
votes

I have double check and "cross-projects" is not a problem - you should try again - maybe from scratch

Meantime, I can share how i deal with this - I have extra layer, so not only users do not have access to underlying table but you can also set that these users do not even know which tables are involved - which is a case in above mentioned "simple" setting where user still have access to View and can see underlying tables

So, the "trick" is to introduce yet another dataset - ProjectX:Dataset0 where you create simple view like below - let's name it ProxyView 

SELECT *
FROM [ProjectX:DatasetA.YourInitialView]

Now,
1. You give users View access to ProjectX:Dataset0
2. You Authorize ProjectX:Dataset0.ProxyView with View access to ProjectX:DatasetA
3. and finally you Authorize ProjectX:DatasetA.YourInitialView with View access to ProjectY:DatasetB

works for me for since Authorized View was introduced

0
votes

What you need to do is to go to the old Bigquery UI, click on the triangle button next to [Project Y]:[Dataset B] and choose share dataset. Add a share with authorised view, select [Project X]:[Dataset A].[View 1] in the dialog, then add and save the share settings.

This will allow [Project X]:[Dataset A].[View 1] to access [Project Y]:[Dataset B] for the underlining data. If you have many views in [Project X]:[Dataset A], it is a painful process to add all of them.