Check for Publicly Accessible BigQuery Datasets

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, click project name that you want to examine to expand the section panel, click on the 3-dot button on the right of the name of the dataset that you want to examine, then click on Open.

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.

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  |        |
+-----------------+--------------------------+--------+

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.

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, click project name that you want to examine to expand the section panel, click on the 3-dot button on the right of the name of the dataset that you want to reconfigure, then click on Open.

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.

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 cc-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.