为工作表支持的表授予用户Bigquery Client Tools访问权限的正确方法，但API仍然失败 [英] Correct way to grant Bigquery Client Tools access for user for table backed by sheets, but API still fails

问题描述

在我们的组织中，我们有一个标准用户，他创建了他们所指向的所有Bigquery项目，表格和一些Google表格.

In our organisation we have a standard user who created all the Bigquery projects, tables, and some Google Sheets that they point to.

我们在组织中创建了另一个用户，为其授予了Bigquery Data Viewer和Bigquery Data User权限，并允许他们访问Google表格.他们可以访问标准表，但不能访问Google表格支持的表；他们得到错误Encountered an error while globbing file pattern.

We created another user in the organisation, granted them Bigquery Data Viewer and Bigquery Data User permissions, and allowed them access to the Google Sheet. They are able to access the standard tables, but the not the ones backed by Google Sheets; they get the error Encountered an error while globbing file pattern.

授予该新用户Bigquery Editor权限，他们可以创建由工作表支持的自己的表，但是会看到以下对话框:

Granting that new user Bigquery Editor permission, they can create their own tables backed by sheets, but they get this dialog box:

授予权限后，我们可以删除Bigquery Editor权限，并且用户继续可以访问所有现有的Bigquery表，包括由工作表支持的表.

After granting permissions, we can remove the Bigquery Editor permission and the user continues to have access to the all the existing Bigquery tables including those backed by sheets.

查找新用户的Google帐户 https://security.google.com/settings/security/permissions，我们看到他们已经添加了Bigquery客户端工具:

Looking in the new user's Google Account https://security.google.com/settings/security/permissions, we see they've added the Bigquery Client Tools:

问题1::向新用户授予此权限的正确方法是什么?我们发现了两种解决方法:

Question 1: What is the correct way to grant this permission for new users? We've found two work-arounds:

• 从弹出窗口中获取URL并为新用户进行编辑，而无需授予他们编辑者"权限.
• 让用户将查询保存到Google表格中，这会触发弹出窗口.

但是我们没有找到以编程方式向用户授予此权限.

But we've found no programatic way to grant the users this permission.

进一步...

但是，API似乎仍然有问题.我们跑了:

However, the API still seems to have problems. We ran:

gcloud auth login --enable-gdrive-access
# approve permissions in the web browser for the new user
bq query --apilog=apil.log "SELECT * FROM [warehouse:catalog.table]"

，我们的日志显示"An internal error occurred and the request could not be completed."或"Encountered an error while globbing file pattern."；例如:

and our log shows either "An internal error occurred and the request could not be completed." or "Encountered an error while globbing file pattern."; for example:

INFO:root:{
 [...]
 "jobReference": {
  "projectId": [...]
  "jobId": "bqjob_r2410ded84270edc0_00000158fdea47bb_1"
 },
 "status": {
  "state": "DONE",
  "errorResult": {
   "reason": "internalError",
   "message": "An internal error occurred and the request could not be completed."
  },
  "errors": [
   {
    "reason": "internalError",
    "message": "An internal error occurred and the request could not be completed."
   }
  ]
 },
 [...]
}

问题2:，我们还需要做些什么才能通过API/cli授予数据访问权限?

Question 2: What further do we need to do to grant data access through the API/cli?

推荐答案

我建议使用单独的数据集，并带有查询原始数据集的视图.您可以与视图共享数据集.这意味着视图将有权访问数据集，然后您可以授予用户访问视图数据集"的权限.

I recommend to have a separate dataset with views querying the original dataset. You can share dataset with views. Which means that views will have access to a dataset and then you can give the users access to the "view dataset".

Dataset_A(Google表格)

Dataset_A (google sheets)

• 与数据集视图中的视图共享

Dataset_views

Dataset_views

• 与用户(或组)共享

这篇关于为工作表支持的表授予用户Bigquery Client Tools访问权限的正确方法，但API仍然失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！