如何在 bigquery 中构建视图以实现高效的访问管理

Question

在 BigQuery 中，您可以授予用户/角色(或授权视图(在数据集级别而不是视图/表级别的访问权限。我想解决的挑战是，当我有数百个表和视图以及许多不同的角色/部门应该有权访问所有部门共享的视图和仅针对特定角色/部门的视图时，如何在 bigquery 中管理访问控制？

示例：假设我有一个源数据集，其中包含源表 A->D 和每个表的三个视图，根据数据 1->3 的敏感度公开不同的字段。另外，我有三个角色(蓝色，绿色，红色(。如果我可以在表级别管理访问权限，它将如下所示：

查看：角色

• A1：蓝色、红色

• A2：红色

• A3：红色

• B1：蓝色、绿色、红色

• B2：绿色、红色

• B3：红色

• C1：绿色、红色

• C2：绿色、红色

• C3：红色

• D1：红色

• D2：红色

• D3：红色

鉴于这些要求，我无法仅基于敏感度 (1-3( 或源 (A-D( 创建数据集并基于此管理访问权限。我能看到的唯一满足此要求的解决方案是为每个角色生成一个数据集。如果角色和视图的数量很少，则可以手动完成此操作，但是当管理 10+ 个角色和 50+ 个视图时，这变得更具挑战性。

我能想到的唯一解决方案是CI/CD设置(云构建(，其中包含定义数据集(即角色(，依赖项和DDL语句的文件。 示例文件：

{"roles":["crm_analyst", "admin", "customer_service_agent"],
"ddl":"CREATE VIEW `myproject.'{role}'.newview` AS SELECT column_1, column_2, column_3 FROM myproject.mydataset.myview",
"dependencies":"myproject.mydataset.myview"}

其他公司如何解决这个问题？有些大型银行已经迁移到 bigquery，这些银行必须拥有大量的部门和不同的数据集敏感度。

Answer 1

我最终编写了一个 python 脚本，该脚本从 json 文件中读取视图定义，然后生成数据集和视图并提供正确的访问权限。该解决方案有点粗糙，可以使用依赖项映射(当一个视图查询另一个视图时(而不是当前解决方案迭代视图，直到生成所有视图或脚本无法再生成视图(损坏的依赖项(。该脚本为每个组生成两个数据集，一个使用 READER(后缀"_ro"(，另一个使用 WRITER(后缀"_rw"(，以确保数据团队生成的视图无法修改，同时为组提供沙盒。该组应为电子邮件组，数据集的名称将是电子邮件地址的本地部分。该脚本由谷歌云构建执行，并通过推送到我们的github存储库触发。

示例视图定义(路径：views/view_test.json(

{
"groups":["[e-mail]", "[e-mail]"],
"sql":"SELECT * FROM `{project}.shared_views.test_view`"
}

生成以下数据集(访问(和视图：

analysts_ro ([e-mail]:READER):
- view_test
analysts_rw ([e-mail]:WRITER):
(empty)
developers_ro ([e-mail]:READER):
- view_test
developers_rw ([e-mail]:WRITER):
(empty)
shared_views (analysts_ro.view_test:None, developers_ro.view_test:None):
- test_view

我使 python 脚本作为 datahem 的一部分在 github 上作为开源提供，随意克隆、改进和使用您自己的目的。

Answer 2

与组而不是角色共享数据集。每个"角色"都有一个组;红色、绿色和蓝色。创建仅具有视图的数据集。与视图共享源数据集表。

• RED_DATASET： 共享： RED_GROUP 视图： A1-A3，B1-B3，C1-C3，D1-D3

• BLUE_DATASET： 共享： BLUE_GROUP 视图： A1，B1

• GREEN_DATASET： 共享： GREEN_GROUP 视图： B1-B2，C1-C2

请注意，B1 视图将具有三个副本(每个"view_dataset"中一个副本(，并使用 by 同一查询进行定义。

这是有关视图访问控制的建议做法。

Answer 3

另一种选择是设置行级访问权限并将所有视图放在同一个数据集中。

例如，为access_control表(用户、用户组(建模：

SELECT '[e-mail]' as user_name, ['developer','analyst'] as user_groups
UNION ALL
SELECT '[e-mail]' as user_name, ['developer'] as user_groups

通过添加具有user_groups数组的静态列来创建具有行级访问控制的视图，并与当前用户的至少一个组与allowed_groups匹配的access_control"表"联接：

SELECT c.* EXCEPT(allowed_groups) FROM (
SELECT OrderReference, Date, ['developer', 'analyst'] AS allowed_groups 
FROM `project.dataset.orders`) as c
INNER JOIN (
SELECT user_name, user_group 
FROM  `project.access.access_control`, UNNEST(user_groups) as user_group 
WHERE SESSION_USER() = user_name) g
ON g.user_group IN UNNEST(c.allowed_groups)

这是一个很好的解决方案，但是即使用户无权访问它，它也会向用户公开所有视图。此外，用户将能够针对他/她无权访问的视图运行查询(生成成本(，但不会返回任何结果。从可用性的角度来看(仅显示用户有权访问的视图(，我们选择了上面标记的解决方案。