Different users access to different rows without creating separate views in BigQuery
To give different users access to different rows in your table, you don't need to create separate views for each one. These options all make use of the CURRENT_USER() function in BigQuery, which returns the e-mail address of the currently running user.
SELECT CURRENT_USER(); example, we get back tigani@google.com.
The simplest option for displaying different rows to different users is to add a column to your table that is the user who is allowed to see the row.
For example: the schema before : {customer:string, id:integer}
would become
After schema {customer:string, id:integer, allowed_viewer: string}.
Then I'd be able to see only the fields where CURRENT_USER() was the value in the allowed_viewer column.
This approach has its own drawbacks. However, You can only grant access to a single user at a time and One option would be to make the allowed_viewer column a repeated field that would let you provide a list of users which has access each row.
However, this is still pretty restrictive, and requires a lot of information to be stored about which users should have access to which row and chances are, what you'd really like to do is specify a group. So your schema would look like: {customer: string, id: integer, allowed_group: string}. Anyone in the allowed_grouplist would be able to see your table.
To make this work by having another table that has group mappings. That table would look like: {group:string, user_name:string}. The rows might look like:
{engineers, tigani@google.com}
{engineers, some_engineer@google.com}
{administrators, some_admin@google.com}
{sales, some_salesperson@google.com}
...
Let's call this table private.access_control. Then we can change our view definition:
SELECT c.customer, c.id
FROM private.customers c
INNER JOIN (
SELECT group
FROM private.access_control
WHERE CURRENT_USER() = user_name) g
ON c.allowed_group = g.group
Note: you will want to make sure that there are no duplicates in private.access_control, otherwise, it could record to repeat in the results.
In this way, we can manage the groups in the private.access_control separately from the data table (private.customers).
There is still one piece missing that you might want which is the ability for groups to contain other groups. You can get this by doing a more complex join to expand the groups in the access control table (you might want to consider doing this only once and saving the results to save the work each time the main table is queried).
SELECT CURRENT_USER(); example, we get back tigani@google.com.
The simplest option for displaying different rows to different users is to add a column to your table that is the user who is allowed to see the row.
For example: the schema before : {customer:string, id:integer}
would become
After schema {customer:string, id:integer, allowed_viewer: string}.
Then I'd be able to see only the fields where CURRENT_USER() was the value in the allowed_viewer column.
This approach has its own drawbacks. However, You can only grant access to a single user at a time and One option would be to make the allowed_viewer column a repeated field that would let you provide a list of users which has access each row.
However, this is still pretty restrictive, and requires a lot of information to be stored about which users should have access to which row and chances are, what you'd really like to do is specify a group. So your schema would look like: {customer: string, id: integer, allowed_group: string}. Anyone in the allowed_grouplist would be able to see your table.
To make this work by having another table that has group mappings. That table would look like: {group:string, user_name:string}. The rows might look like:
{engineers, tigani@google.com}
{engineers, some_engineer@google.com}
{administrators, some_admin@google.com}
{sales, some_salesperson@google.com}
...
Let's call this table private.access_control. Then we can change our view definition:
SELECT c.customer, c.id
FROM private.customers c
INNER JOIN (
SELECT group
FROM private.access_control
WHERE CURRENT_USER() = user_name) g
ON c.allowed_group = g.group
Note: you will want to make sure that there are no duplicates in private.access_control, otherwise, it could record to repeat in the results.
In this way, we can manage the groups in the private.access_control separately from the data table (private.customers).
There is still one piece missing that you might want which is the ability for groups to contain other groups. You can get this by doing a more complex join to expand the groups in the access control table (you might want to consider doing this only once and saving the results to save the work each time the main table is queried).
0 Comments