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

问题描述

Google 今天宣布了此功能，但我没有看到任何相关文档.如何向用户授予行级权限?

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

例如，假设我有一个表 private.all_customers，其架构为 {customer:string, id:integer, is_secret:boolean}.

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

我喜欢让我们的销售人员访问字段 customer 和 id，而不是 is_secret，而且，我想让他们只能访问 is_secret = false 的那些行.我怎样才能做到这一点?

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

然后我可以将其保存为视图public.public_customers"，并与分析师共享public数据集.

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.

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

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.

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

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.

将视图添加到 ACL 后，任何有权访问公共"数据集的人都应该能够对 public.public_customers 视图运行查询.

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.

有关此功能的更高级用法，这将允许您为不同的用户提供不同的答案，请参阅此问题:如何让不同的用户访问不同的行而不在 BigQuery 中创建单独的视图?

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屋！