是否可以将Google服务帐户限制为项目中特定的BigQuery数据集? [英] Is it possible to limit a Google service account to specific BigQuery datasets within a project?

问题描述

我已经使用GCP UI为特定项目 Project X 设置了服务帐户.在 Project X 中，有3个数据集:

I've set up a service account using the GCP UI for a specific project Project X. Within Project X there are 3 datasets:

数据集1

数据集2

数据集3

如果我将角色 BigQuery Admin 分配给 Project X ，则当前所有3个数据集都将继承该角色.

If I assign the role BigQuery Admin to Project X this is currently being inherited by all 3 datasets.

当前，所有这些数据集都继承了在项目级别分配给服务帐户的权限.有什么方法可以修改服务帐户的权限，使其只能访问指定的数据集?例如允许访问数据集1 ，但不能访问数据集2 或数据集3 .

Currently all of these datasets inherit the permissions assigned to the service account at the project level. Is there any way to modify the permissions for the service account such that it only has access to specified datasets? e.g. allow access to Dataset 1 but not Dataset 2 or Dataset 3.

这种类型的配置可行吗?

Is this type of configuration possible?

我尝试在UI中添加条件，但是当我使用 Name 资源类型并将值设置为等于 Dataset 1 时，我无法访问任何数据集-值可能不正确.或者数据集不是有效的名称资源.

I've tried to add a condition in the UI but when I use the Name resource type and set the value equal to Dataset 1 I'm not able to access any of the datasets - presumably the value is not correct. Or a dataset is not a valid name resource.

添加一些有关我在发布之前已经尝试过的内容的详细信息，以及有关我正在做什么的更多详细信息.

Adding some more detail regarding what I'd already tried before posting, as well as some more detail on what I'm doing.

对于我的特定用例，我正在尝试执行SQL查询以及通过API(使用Python)在BigQuery中修改表.

For my particular use case, I'm trying to perform SQL queries as well as modifying tables in BigQuery through the API (using Python).

案例A:我创建一个角色为 'BigQuery Admin' 的服务帐户.

Case A: I create a service account with the role 'BigQuery Admin'.

此角色传播到项目内的所有数据集-属性是继承的，我无法从任何数据集中删除此服务帐户角色.

This role is propagated to all datasets within the project - the property is inherited and I can not delete this service account role from any of the datasets.

在这种情况下，我可以使用Python API来查询所有数据集和表，就像您期望的那样.

In this case I'm able to query all datasets and tables using the Python API - as you'd expect.

案例B:我创建了一个没有默认角色的服务帐户.

Case B: I create a service account with no default role.

不传播任何角色，我可以通过单击UI中的共享数据集" 选项将'BigQuery Admin'角色分配给特定数据集，从而将角色分配给特定数据集他们.

No role is propagated and I can assign roles to specific datasets by clicking on the 'Share dataset' option in the UI to assign the 'BigQuery Admin' role to them.

在这种情况下，我无法查询任何数据集或表，并且在尝试时会出现以下错误:

In this case I'm not able to query any of the datasets or tables and get the following error if I try:

<代码> *禁止访问:403 POST https://bigquery.googleapis.com/bq/projects/project-x/jobs:拒绝访问:Project X:用户在Project Project中没有bigquery.jobs.create权限X.*

即使我想要的数据集具有所需的权限(在这种情况下为 bigquery.jobs.create )，但由于 bigquery.jobs出现，我无法查询数据在项目级别还需要具有.create 权限才能使用API​​.

Even though the permissions required (bigquery.jobs.create in this case) exist for the dataset I want, I can't query the data as it appears that the bigquery.jobs.create permission is also required at a project level to use the API.

推荐答案

我将发布针对该问题的解决方案，以防其他尝试实现此目的的人有用.

I'm posting the solution that I found to the problem in case it is useful to anyone else trying to accomplish the same.

在项目级别分配角色"BigQuery作业用户" ，以将权限 bigquery.jobs.create 分配给该项目的服务帐户.

Assign the role "BigQuery Job User" at a project level in order to have the permission bigquery.jobs.create assigned to the service account for that project.

然后您可以手动为特定的数据集分配"BigQuery数据编辑器" 的角色，以便通过Python中的API查询它们.通过单击BigQuery UI中的共享数据集"来执行此操作.因此，在此示例中，我已经与服务帐户共享"了 Dataset 1 和 Dataset 2 .

You can then manually assign specific datasets the role of "BigQuery Data Editor" in order to query them through the API in Python. Do this by clciking on "Share dataset" in the BigQuery UI. So for this example, I've "Shared" Dataset 1 and Dataset 2 with the service account.

您现在应该能够查询已在Python中为其分配了BigQuery Data Editor角色的数据集.

You should now be able to query the datasets for which you've assigned the BigQuery Data Editor role in Python.

但是，对于尚未为其分配"BigQuery Data Editor" 角色的 Dataset 3 角色，如果尝试查询表，则应返回错误:

However, for Dataset 3, for which the "BigQuery Data Editor" role has not been assigned, if you attempt to query a table this should return the error:

禁止:403访问被拒绝:表Project-x:dataset_1.table_1:用户无权查询表Project-x:dataset_1.table_1.

如上所述，我们现在有足够的权限来访问项目，但没有权限通过设计访问 Dataset 3 中的表.

As described above, we now have sufficient permissions to access the project but not the table within Dataset 3 - by design.

这篇关于是否可以将Google服务帐户限制为项目中特定的BigQuery数据集?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！