Check for Publicly Accessible BigQuery Datasets

Trend Micro Cloud One™ – Conformity is a continuous assurance tool that provides peace of mind for your cloud infrastructure, delivering over 750 automated best practice checks.

Risk level: High (act today)

Ensure there are no anonymously and/or publicly accessible BigQuery datasets available within your Google Cloud Platform (GCP) account. Google Cloud BigQuery datasets have Identity and Access Management (IAM) policies configured to determine who can have access to these resources. To refuse access from anonymous and public users, remove the bindings for "allUsers" and "allAuthenticatedUsers" members from the IAM policy associated with your datasets. The "allUsers" is a special member identifier that represents any user on the Internet, including authenticated and unauthenticated users, whereas the "allAuthenticatedUsers" is an identifier that represents any user or service account that can sign in to Google Cloud Platform (GCP) with a Google account.

Security

BigQuery is a serverless, highly-scalable, and cost-efficient Google Cloud data warehouse service. BigQuery datasets are top-level containers that are used to organize and control access to your data tables and views. Granting permissions to "allUsers" and "allAuthenticatedUsers" members can allow anyone to access your datasets. To prevent sensitive data leaks and data loss, ensure that anonymous and/or public access to your Google Cloud BigQuery datasets is not allowed.


Audit

To determine if there are any publicly accessible BigQuery datasets available within your Google Cloud account, perform the following actions:

Using GCP Console

01 Sign in to Google Cloud Management Console.

02 Select the Google Cloud Platform (GCP) project that you want to examine from the console top navigation bar.

03 Navigate to Google Cloud BigQuery dashboard at https://console.cloud.google.com/bigquery.

04 In the navigation panel, under Resources, click on the name of the dataset that you want to examine.

05 Click on the SHARE DATASET button from the dataset options menu to access the permissions available for selected BigQuery dataset.

06 On the Dataset permissions panel, select DATASET PERMISSIONS tab and use the Search members box to search for both allUsers and allAuthenticatedUsers members. If the search results contain one or more roles associated with allUsers and/or allAuthenticatedUsers members, the selected Google Cloud BigQuery dataset is publicly accessible. Also, the following message should be displayed on the Dataset permissions panel: "This resource is public and can be accessed by anyone on the Internet. To remove public access, remove "allUsers" and "allAuthenticatedUsers" from the resource's members.".

07 Repeat step no. 4 – 6 for each dataset available within the selected GCP project.

08 Repeat steps no. 2 – 7 for each project deployed in your Google Cloud account.

Using GCP CLI

01 Run projects list command (Windows/macOS/Linux) using custom query filters to list the IDs of all the Google Cloud Platform (GCP) projects available in your cloud account:

gcloud projects list
	--format="table(projectId)"

02 The command output should return the requested GCP project IDs:

PROJECT_ID
cc-project5-123123
cc-ml-project-112233

03 Run bq ls command (using bq Python tool) to list the identifier (name) of each BigQuery dataset created for the specified Google Cloud project:

bq ls --project_id cc-project5-123123
	--format=pretty

04 The command output should return a table with the requested dataset identifier(s):

+---------------------------------+
|           datasetId             |
+---------------------------------+
| cc_project5_production_dataset  |
| cc_project5_cloud_index_dataset |
+---------------------------------+

05 Run bq show command (using bq tool) using the ID of the GCP project and the name of the BigQuery dataset that you want to examine as identifier parameters, to describe the Access Control Lists (ACLs) defined for the selected dataset:

bq show --format=pretty cc-project5-123123:cc_project5_production_dataset

06 The command output should return the requested access configuration information:

+-----------------+--------------------------+--------+
|  Last modified  |           ACLs           | Labels |
+-----------------+--------------------------+--------+
| 25 May 10:25:50 | Owners:                  |        |
|                 | bq@cloudconformity@.com, |        |
|                 |   projectOwners          |        |
|                 | Writers:                 |        |
|                 |   projectWriters         |        |
|                 | Readers:                 |        |
|                 |   projectReaders         |        |
|                 | roles/editor:            |        |
|                 |   allUsers               |        |
|                 | roles/owner:             |        |
|                 |   allAuthenticatedUsers  |        |
+-----------------+--------------------------+--------+

Check the ACLs list returned by the bq show command output for roles associated with allUsers and/or allAuthenticatedUsers members. If one or more roles are using the "allUsers" and/or "allAuthenticatedUsers" members, as shown in the example above, the selected Google Cloud BigQuery dataset is publicly accessible.

07 Repeat step no. 5 and 6 for each dataset created for the selected GCP project.

08 Repeat steps no. 3 – 7 for each project available within your Google Cloud account.

Remediation / Resolution

To remove all "allUsers" and/or "allAuthenticatedUsers" member bindings from the dataset ACLs in order to restrict anonymous and/or public access to your Google Cloud BigQuery datasets, perform the following actions:

Using GCP Console

01 Sign in to Google Cloud Management Console.

02 Select the Google Cloud Platform (GCP) project that you want to access from the console top navigation bar.

03 Navigate to Google Cloud BigQuery dashboard at https://console.cloud.google.com/bigquery.

04 In the navigation panel, under Resources, click on the name of the dataset that you want to reconfigure.

05 Click on the SHARE DATASET button from the dataset options menu to access the permissions defined for selected BigQuery dataset.

06 On the Dataset permissions panel, perform the following:

  1. Select the DATASET PERMISSIONS tab to access the IAM permissions defined for the dataset.
  2. Click on the role associated with the allUsers and/or allAuthenticatedUsers member to expand the role configuration panel, and click on the delete (icon) button to remove the non-compliant IAM member.
  3. On the Remove member confirmation box, click REMOVE to confirm your action.
  4. Click Done to return to the selected BigQuery dataset dashboard.

07 Repeat step no. 4 – 6 for each publicly accessible dataset created within the selected project.

08 Repeat steps no. 2 – 7 for each project deployed in your Google Cloud account.

Using GCP CLI

01 Run bq show command (using bq Python tool) using the ID of the GCP project and the name of the BigQuery dataset that you want to reconfigure as identifier parameters, to export the configuration information (including ACLs) available for the selected dataset, to a JSON file named cc-dataset-config.json (the command does not produce an output):

bq show
	--format=prettyjson cc-project5-123123:cc_project5_production_dataset > cc-dataset-config.json

02 Open the JSON document exported at the previous step, i.e. cc-dataset-config.json, remove all allUsers and/or allAuthenticatedUsers member bindings (highlighted) from the dataset ACLs, and save the file:

{
  "access": [
    {
      "role": "WRITER",
      "specialGroup": "projectWriters"
    },
    {
      "role": "OWNER",
      "specialGroup": "projectOwners"
    },
    {
      "role": "OWNER",
      "userByEmail": "bq@cloudconformity.com"
    },
    {
      "role": "READER",
      "specialGroup": "projectReaders"
    },
    {
      "role": "roles/editor",
      "iamMember": "allUsers"
    },
    {
      "role": "roles/owner",
      "specialGroup": "allAuthenticatedUsers"
    }
  ],
  "datasetReference": {
    "datasetId": "cc_project5_production_dataset",
    "projectId": "cc-project5-123123"
  },
  "id": "cc-project5-123123:cc_project5_production_dataset",
  "kind": "bigquery#dataset",
  "location": "US",
  "selfLink": "https://bigquery.googleapis.com/bigquery/v2/projects/cc-project5-123123/datasets/cc_project5_production_dataset"
}

03 Run bq update command (using bq tool) using the name of the JSON configuration file, updated at the previous step, as --source parameter value, to update the ACLs of the selected Google Cloud BigQuery dataset:

bq update
	--source dataset-config.json cc-project5-123123:cc_project5_production_dataset

04 The output should return the bq update command request status:

Dataset 'cc-project5-123123:cc_project5_production_dataset' successfully updated.

05 Repeat steps no. 1 – 4 for each anonymously or publicly accessible dataset available in the selected project.

06 Repeat steps no. 1 – 5 for each project created within your Google Cloud account.

References

Publication date Apr 12, 2021

Unlock the Remediation Steps


Gain free unlimited access
to our full Knowledge Base


Over 750 rules & best practices
for AWS and Azure

You are auditing:

Check for Publicly Accessible BigQuery Datasets

Risk level: High