如何在BigQuery中使用行级权限？ [英] How do I use row-level permissions in BigQuery?

问题描述

Google今天宣布了此功能，但我没有看到任何文档。例如，假设我有一个表 private.all_customers 如果我有一个表 private.all_customers 模式 {customer：string，id：integer，is_secret：boolean} 。

我喜欢让我们的销售人员访问字段客户和 id ，但不是 is_secret ，此外，我想给予他们仅访问 is_secret = false 的行。我怎样才能做到这一点？

解决方案

行级权限的关键部分是您实际上授予视图权限。该视图定义了您希望委派用户查看的行和列，而无需授予其访问基础表的权限。

为此，创建将返回您希望用户看到的行和列。对于上面的例子，视图如下所示：

  SELECT customer，id FROM private.all_customers where is_secret = false 
  

然后我可以将它保存为public.public_customers视图，并共享 public

请注意，到目前为止， NOT 意味着分析师可以访问到数据。如果他们现在尝试运行它们，他们会得到一个错误。仅仅有权访问读取表的视图不会让您访问该表上的基础数据。如果是这样，那么任何人都可以创建一个视图来读取他们想要查看的任何数据。

第二步是将该视图添加到<$的ACL中c $ c>私人数据集。它所做的是记录视图应该有权访问数据。通过这种方式， private 数据集的所有者可以审核谁有权访问他们的数据，并在必要时撤销它。

将视图添加到ACL的最简单方法是使用BigQuery Web UI。如果您单击Web UI中 private 数据集名称旁边的箭头，然后单击共享此数据集，则会出现一个对话框，让您可以编辑ACL 。
在该对话框的底部，它将显示添加人员和左侧的可点击图标。如果你点击该图标，你应该可以选择授权视图。选择之后，您应该输入视图的全限定名称 - project：dataset.view 。在我们的例子中，这将是 my-project：public.public_customers 。点击'添加'，它会显示在列表中，然后点击保存更改提交。

一旦视图添加到ACL中，访问公共数据集应该能够针对 public.public_customers 视图运行查询。

该功能的高级使用，这将允许您给不同的用户不同的答案，看到这个问题：

Google announced this feature today, but I don't see any docs for it. How can I grant row-level permissions to a user?

For example, let's say I have a table private.all_customers with the schema {customer:string, id:integer, is_secret:boolean}.

I like to give our salespeople access to the fields customer and id, but not is_secret, and moreover, I'd like to give to give them access to only those rows where is_secret = false. How can I accomplish this?

解决方案

The key part of row-level permissions is that you're actually giving permission to a view. The view defines the rows and columns in that you want the delegated user to see, without giving them access to the underlying table.

To do this, create the view that will return the rows and columns that you'd like the user to see. For the example above, the view would look like:

SELECT customer, id FROM private.all_customers where is_secret = false

Then I can save this as the view "public.public_customers", and share the public dataset with the analysts.

Note that, so far, this does NOT mean that the analysts will have access to the data. If they try to run it now, they'll get an error. Merely having access to a view that reads a table doesn't give you access to the underlying data on that table. If it did, then anyone could just create a view to read any data that they wanted to see.

The second step is adding that view to the ACL of the private dataset. What this does is records that the view should have access to the data. This way the owner of the private dataset can audit who has access to their data, and revoke it if necessary.

The easiest way to add the view to the ACL is to use the BigQuery Web UI. If you click on the arrow next to the private dataset name in the Web UI and click "Share this dataset", it will bring up a dialog box that lets you edit the ACL. At the bottom of that dialog it will show "Add People" and a clickable icon on the left. If you click on that icon, you should be able to select "Authorized View". Once that is selected, you should enter the fully-qualified name -- project:dataset.view of the view. In our example, that would be my-project:public.public_customers. Hit 'Add' and it will show up in the list, and then hit "Save Changes" to commit.

Once the view has been added to the ACL, anyone with access to the 'public' dataset should be able to run queries against the public.public_customers view.

For more advanced usage of this feature, which will allow you to give different answers to different users, see this question: How do I give different users access to different rows without creating separate views in BigQuery?

这篇关于如何在BigQuery中使用行级权限？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！