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

问题描述

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

解析方案

幸运的是，如果您想让不同的用户访问表中的不同行，不需要为每个人创建单独的视图。你有几个选择。

这些选项都使用BigQuery中的 CURRENT_USER（）函数，它返回电子邮件地址当前正在运行的用户。例如，如果我运行：

  SELECT CURRENT_USER（）
  

我找回 [e-mail] 。

然后，向不同用户显示不同行的最简单方法是在允许查看该行的用户的表中添加另一列。例如，架构： {customer：string，id：integer} 会变成 {customer：string，id：integer，allowed_viewer：string} 。然后你可以定义一个视图：

  SELECT customer，id 
 FROM private.customers 
 WHERE allowed_viewer = CURRENT_USER（）
  

（请注意，不要忘记按照here ）。
然后，我只能看到[e-mail]是allowed_viewer列中的值的字段。

这种方法有其自身的缺点但是，您一次只能授予一个用户的访问权限。一种选择是将allowed_viewer列设置为重复字段;这可以让你为每一行提供用户列表。

然而，这仍然是相当严格的，并且需要大量记录哪些用户应该有权访问哪些行。很可能，你真正想做的是指定一个组。因此，您的模式将如下所示： {customer：string，id：integer，allowed_group：string} ，并且allowed_group中的任何人都能够看到您的表。

您可以通过让另一个具有您的组映射的表来完成此工作。该表格如下所示： {group：string，user_name：string} 。行可能如下所示：

  {工程师，[e-mail]} 
 {工程师，some_engineer @ google。 com} 
 {administrators，[e-mail]} 
 {sales，[e-mail]} 
 ... 
  

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

  SELECT c.customer，c.id 
 FROM private.customers c 
 INNER JOIN（
 SELECT组
 FROM private.access_control 
 WHERE CURRENT_USER（）= user_name）g 
 ON c.allowed_group = g.group 
  code> 

（请注意，您需要确保private.access_control中没有重复项，否则可能会在记录中重复记录）。



通过这种方式，您可以独立于数据表管理 private.access_control 中的组（ private.customers ）。

还有一件你可能想要的;组包含其他组的能力。您可以通过执行更复杂的连接来扩展访问控制表中的组（可能需要考虑只做一次并保存结果，以便在每次查询主表时保存工作）。

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.

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

SELECT CURRENT_USER()

I get back [e-mail].

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:

SELECT customer, id 
FROM private.customers 
WHERE allowed_viewer = CURRENT_USER()

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

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

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

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