BigQuery data access to two different users without duplicating data

0
votes

We have two types of users and two projects.

users:

  • Admin who should access all the data on a Table
  • Users who should be able to query just some columns of a Table As you see in the following diagram, the access data should be in this way without duplicating data: enter image description here

As it is not possible to give access to users based on a Column (and not even based on a Table because access control on BigQuery is just on the Dataset level), the easiest solution which comes to mind is to:

  • Duplicate data in two different datasets or project, one with all data, and another one with those columns which second group can query.

How we can find a solution without duplicating data?

1
google-bigquerygoogle-cloud-platform
Views are actually the perfect solution. Why do you think users can still query the whole table? You restrict the columns they see with each view in the underlying SQL. cloud.google.com/bigquery/docs/share-access-views - Graham Polley
Why do you say VIEW is not the correct solution? You grant permission only to VIEW, and this way they are not able to query other columns. - Pentium10
I wrote view is not correct solution, because i was thinking I should add directly the user into the table to be able to query, as the error was saying that the user has not enough grant on the table. Than I have read the link that @GrahamPolley put in his comment and I have found out that i had to share the table with the view and not sharing directly with user. - Majico
@GrahamPolley: please provide your comment as an answer , then i accept it as the correct answer, if not, let me know to do it. Thanks! - Majico
@Majico - check also stackoverflow.com/a/45991950/5221944 for additional "trick" :o) - Mikhail Berlyant

1 Answers

1
votes

Views are actually the perfect solution to your problem. You simply restrict the columns each group sees with each view in the underlying SQL of the view.

For more details, see: