如何让不同的用户访问不同的行而不在 BigQuery 中创建单独的视图? [英] How do I give different users access to different rows without creating separate views in BigQuery?

问题描述

在这个问题中:我如何使用行级BigQuery 中的权限?它描述了如何使用授权的视图来仅授予对表的一部分的访问权限.但我想让不同的用户访问不同的行.这是否意味着我需要为每个用户创建单独的视图?有没有更简单的方法?

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?

推荐答案

令人高兴的是，如果您想让不同的用户访问您表中的不同行，您无需为每一行创建单独的视图.您有几个选择.

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.

这些选项都使用了 BigQuery 中的 SESSION_USER() 函数，该函数返回当前运行用户的电子邮件地址.例如，如果我运行:

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()

我回来了[e-mail].

对于向不同的用户显示不同的行，最简单的选择是将另一列添加到您的表中，该列是允许查看该行的用户.例如，架构:{customer:string, id:integer} 将变为 {customer:string, id:integer, allowed_viewer: string}.然后你可以定义一个视图:

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()

(注意，不要忘记按照描述授权视图 此处).然后我将只能看到 [e-mail] 是 allowed_viewer 列中的值的字段.

(note, don't forget to authorize the view as described here). Then I'd be able to see only the fields where [e-mail] was the value in the allowed_viewer column.

然而，这种方法有其自身的缺点；您一次只能授予一个用户访问权限.一种选择是使 allowed_viewer 列成为重复字段；这将允许您为每一行提供用户列表.

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.

然而，这仍然非常严格，并且需要大量记录哪些用户应该有权访问哪一行.很有可能，您真正想做的是指定一个组.所以你的架构看起来像:{customer:string, id:integer, allowed_group: string}，并且 allowed_group 中的任何人都可以看到你的表.

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.

您可以通过拥有另一个包含您的组映射的表来完成这项工作.该表看起来像:{group:string, user_name:string}.这些行可能看起来像:

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, [e-mail]}
{engineers, [e-mail]}
{administrators, [e-mail]}
{sales, [e-mail]}
...

我们称这个表为private.access_control.然后我们可以改变我们的视图定义:

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

(请注意，您需要确保 private.access_control 中没有重复项，否则可能会在结果中重复记录).

(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).

通过这种方式，您可以将private.access_control中的组与数据表(private.customers)分开管理.

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).

这篇关于如何让不同的用户访问不同的行而不在 BigQuery 中创建单独的视图?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！