Sharing Reports and Controlling Query Run Access in BigQuery

In this article, we will guide you through the process of sharing your Google Sheets reports—enhanced with the 'BigQuery Reports' extension—while ensuring that while others can execute queries, they cannot alter the source data in BigQuery. Our guidance is based on the official descriptions of access control features provided by BigQuery.

Firstly, let's outline two prevalent roles in this scenario: the 'Creator' of the report—typically a data analyst with full access to the source data in BigQuery and responsible for writing SQL code—and the 'Customer' of the report—usually a manager or another colleague who lacks access to BigQuery and does not modify the SQL code.

And, secondly, the Creator has configured a Google Sheets report using the 'BigQuery Reports' extension. The Query has been executed successfully, and the report is set to auto-update by schedule. The Creator now aims to share this report with the Customer. Please follow the instructions below to achieve this goal.

Instructions for the Creator of the Report

Step 1. Check which GCP project your Query is linked to:

gbq-reports-04.png

Step 2. Open the Query editor and inspect in which GCP projects the data queried is stored.

gbq-reports-08.png

Step 3. Proceed with the following instructions depending on whether the GCP projects match:

Scenario 1: The same GCP project is used Scenario 2: Different GCP projects are involved

Navigate to the IAM & Admin page within your BigQuery project. If the Customer’s account has not been added yet, proceed to include it in the list. Assign the following essential roles at the project level to ensure proper access:

  • BigQuery Job User: This role authorizes the execution of jobs, including queries, within the project, enabling the Customer to access and execute the Query. More details
  • BigQuery Data Viewer: This role permits the Customer to read data and metadata from tables or views and includes additional permissions depending on the specified access level. More details
  1. Navigate to the IAM & Admin page in the GCP project that is linked with the Queries (as identified in Step 1). Here, add the Customer's account and assign the BigQuery Job User role, enabling them to execute queries. More details
  2. Proceed to the GCP project where the data is stored (referenced in Step 2). Add the Customer's account with the BigQuery Data Viewer role, permitting them to read data and metadata from the datasets or tables. More details

Note: Optionally, the BigQuery Data Viewer role may be configured not just at the project level but can be more granularly applied to specific datasets or tables. This approach is beneficial if providing access to the entire GCP project is not feasible for security reasons.

Step 4. Share the Google Sheets report with your Customer, ensuring they sign in to the Extension using the Google Account you specify in Step 3.

 

Instructions for the Customer of the Report

Step 1. Open the report prepared by the Creator.

Step 2. Go to the Extensions menu → OWOX BI BigQuery Reports → Edit current report.

gbq-reports-05.png

If the extension is not yet installed, you will need to log in using the Google account the Creator provided.
Step 3. Once authorized, you will see settings on the extension panel, including GCP Project, selected Query, and a list of Dynamic parameters (if they are defined in the SQL code).

gbq-reports-06.png

You’re all set. You can run Queries.

gbq-reports-07-2.png

Note: If changes have been made to the SQL code that cause the Query to fail, we recommend opening the version history menu in the code editor and selecting a previously functioning version of the code.

 

If you have any questions about access rights and roles for using the OWOX BI BigQuery Reports extension, please contact our support team at bi@owox.com.

 

 

 

 

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.