BigQuery Web UI: Which permissions are required to display results from stored procedures?

0
votes

We are creating some managed users in BigQuery for our clients. These managed users have role BigQuery Job User on the project, and role BigQuery Data Viewer on the dataset.

Recently we are experimenting with stored procedures (SP) and created a stored procedure according to https://cloud.google.com/blog/products/data-analytics/command-and-control-now-easier-in-bigquery-with-scripting-and-stored-procedures

The admin user (BigQuery.Owner) has no problem executing the SP CALL some_dataset.some_sp(param); and viewing query results in the BigQuery Web UI.

However, when managed users run the same SP CALL some_dataset.some_sp(param); in the BigQuery Web UI, it returns "Job status: SUCCESS" but the results are not being displayed.

Actual behavior: Job success but no results

Expected behavior: enter image description here

I have confirmed the result of the SP is not empty.

As I examined query history, the managed user does see "Destination table: Temporary table", and this "Temporary table" contains correct query results. The problem is these results were not displayed below the web UI editor when the managed user ran the SP. The user had to go to query history to find them out.

Temporary table contains correct results which were not displayed

This issue does not happen to regular SQL queries like select * from some_table/some_view;. Regular query results are displayed correctly below the web UI editor.

Which permissions are required to display results from stored procedures? I could not find any information on this from the Google BigQuery documentation.

1
google-bigquery
This feels like a bug on the UI, could you fire a bug on BigQuery here: cloud.google.com/support/docs/issue-trackers? – Yun Zhang

1 Answers

0
votes

I have the same demand as your case for managed user with following permissions:

  1. project-level BQ Job User permission
  2. dataset-level BQ Data Viewer permission.

When call the stored procedure, no results but only the "Job status: SUCCESS" displayed. Thanks for your previous idea of "Query History/Temporary Table". But the drawback is that we cannot save the result as the ordinary query job. After testing a couple of times, here is my solution:

  • Create Project-Level Custom Role with permission: bigquery.jobs.list
  • Assign the Custom Role to the user

Now the user can see the SP results directly as normal query process; and he can only see his own personal query history, which is the minimal permission control that I want