How do I give different users access to different rows without creating separate views in BigQuery?

Question

In this question: How do I use row-level permissions in BigQuery? it describes how to use an authorized view to grant access to only a portion of a table. But I'd like to give different users access to different rows. Does this mean I need to create separate views for each user? Is there an easier way?

Answer 1

Happily, if you want to give different users access to different rows in your table, you don't need to create separate views for each one. You have a couple of options.

These options all make use of the SESSION_USER() function in BigQuery, which returns the e-mail address of the currently running user. For example, if I run:

SELECT SESSION_USER()

I get back [email protected].

The simplest option, then, for displaying different rows to different users, is to add another column to your table that is the user who is allowed to see the row. For example, the schema: {customer:string, id:integer} would become {customer:string, id:integer, allowed_viewer: string}. Then you can define a view:

#standardSQL
SELECT customer, id 
FROM private.customers 
WHERE allowed_viewer = SESSION_USER()

(note, don't forget to authorize the view as described here). Then I'd be able to see only the fields where [email protected] 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. One option would be to make the allowed_viewer column a repeated field; this would let you provide a list of users for each row.

However, this is still pretty restrictive, and requires a lot of bookkeeping about which users should have access to which row. 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}, and anyone in the allowed_group would be able to see your table.

You can make this work by having another table that has your group mappings. That table would look like: {group:string, user_name:string}. The rows might look like:

{engineers, [email protected]}
{engineers, [email protected]}
{administrators, [email protected]}
{sales, [email protected]}
...

Let's call this table private.access_control. Then we can change our view definition:

#standardSQL
SELECT c.customer, c.id
FROM private.customers c
INNER JOIN (
    SELECT group 
    FROM private.access_control
    WHERE SESSION_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 records to repeat in the results).

In this way, you 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; 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).