How can I restrict users from accessing the public datasets in BigQuery?

[Ivan Yudhi]

Hi,

Is there a way to prevent/deny users that are associated to my project from accessing public datasets in BigQuery?

Thanks,

[Hector Fiel]

Hi, in GCP you can grant permissions only, not deny them.

You may probably solve this granting access to the BigQuery dataset directly (as opossed to granting IAM permissions at the project level), as explained here [1].

With this, your user will only have the permissions you choose on that specific dataset, and will not be able to do anything else in the related BigQuery project (and if they add a public dataset, it will be added in their own personal GCP project, if they have any).

Anyway please try it and let us know!

Refs:

1.- https://cloud.google.com/bigquery/docs/dataset-access-controls

[Ivan Yudhi]

Thanks for your reply Hector. That's unfortunate.

I'm trying to design an exercise where I'll have a 3rd party application creating a BigQuery dataset and table using service account, then I'll have the students work using that dataset. I want to make sure that the students can only work on that particular dataset, and not anything else. But it will be painful if I have to wait until the dataset is created before setting the access.

Is there a way to monitor if the student is using public dataset in real time? (other than.. you know... looking over their shoulder)

[Monica (Google Cloud Platform)]

Restricting the access to BigQuery datasets it's a feature request [1] that it is worked upon. You can start the thread to get automatic updates.

In the meantime you can create an Alert Policy [2] that will inform you that a BigQuery public dataset was queried and by whom (the user). I used the console to create the below alert and the minimum time to trigger it is 1 minute but programatically (e.g. [3]) you can set the duration at second levels. 

1. Do a query test on a public dataset e.g.

SELECT count(gameId) FROM `bigquery-public-data.baseball.schedules` where duration_minutes >180

2. Go to Logging select BigQuery and find the performed query (Bigquery insert jobs [...user...])

2.1. Extend the log and chose "Expand all" 

2.2. In jobStatistics: --> referencedTables: 

     click on "bigquery-public-data" (found next to projectId:)

2.3. Select "Show matching entries" 

The result will look like this:

protoPayload.serviceData.jobInsertResponse.resource.jobStatistics.referencedTables.projectId="bigquery-public-data"

3. Select from on-top Create metric and configure it as follows:

Name: publicA

Description: Alert for querying BigQuery public datasets

3.1. Labels --> Add item

Name: user

Description: originator

Label type: String

Field name: protoPayload.authenticationInfo.principalEmail

Extraction regular expression: (.*)

--> Done

3.2. Units 1

3.3. Type: Counter

--> Create Metric

4. Monitoring --> Alerting --> Create Policy

4.1. Name: Alert for BigQuery public datasets

4.2. ADD Condition

4.3. Metric/Target/Find resource type and metric

4.4. Type publicA

4.5. Configuration

Condition triggers if: Any time series violates

Condition: is above

Threshold: 0

For: 1 minute

--> ADD 

5. Add Notification Channel 

chose your preferred option e.g. email / phone

6. Documentation (optional): 

"Hello, 

This is an alert for your project ${resource.project} where a user is querying a BigQuery public dataset."

--> SAVE

Please be advise that there are many other public datasets available [4] and the alert should be adjusted to include their project ID. For example, to be alerted for the Tcia public dataset [5] the Logs-based Metrics would contain:

protoPayload.serviceData.jobInsertResponse.resource.jobStatistics.referencedTables.projectId="bigquery-public-data" OR

protoPayload.serviceData.jobInsertResponse.resource.jobStatistics.referencedTables.projectId="chc-tcia"

[1] https://issuetracker.google.com/110208085

[2] https://cloud.google.com/monitoring/alerts/concepts-indepth 

[3] https://medium.com/google-cloud/stackdriver-monitoring-automation-part-2-alerting-policies-9f42068603c4

[4] https://cloud.google.com/bigquery/public-data#other_public_datasets 

[5] https://cloud.google.com/healthcare/docs/resources/public-datasets/tcia#bigquery