Ensure that BigQuery datasets are not anonymously or publicly accessible

Using Console

To remediate this misconfiguration in GCP using GCP console, you can follow these steps:

Open the GCP console and navigate to the BigQuery section.
Click on the dataset that you want to remediate.
In the dataset details page, click on the “Share dataset” button.
In the “Share dataset” dialog box, review the current access controls.
If the dataset is publicly accessible, click on the “X” next to the “allUsers” entry to remove it.
If the dataset is anonymously accessible, click on the “X” next to the “allAuthenticatedUsers” entry to remove it.
If you want to grant access to specific users or groups, click on the “Add item” button and enter their email addresses.
Choose the appropriate access level for the users or groups, such as “Viewer” or “Editor”.
Click on the “Save” button to apply the changes.
Finally, verify that the dataset is no longer publicly or anonymously accessible by reviewing the access controls again.

Using CLI

To remediate the misconfiguration of BigQuery datasets being anonymously or publicly accessible in GCP using GCP CLI, follow these steps:

Open the Cloud Shell from the GCP console.
Run the following command to list all the datasets in your project:

bq ls

For each dataset that is publicly accessible, run the following command to revoke the public access:

bq update --default_table_expiration <dataset_id>

Note: Replace <dataset_id> with the ID of the dataset that you want to remediate.

After running the above command, you will see the following prompt:

This update will modify table(s) <dataset_id>:*. Do you want to continue? (y/N):

Type y and press enter to confirm the update.
Repeat steps 3-5 for all the datasets that are publicly accessible.
Run the following command to verify that the datasets are no longer publicly accessible:

bq show <dataset_id>

Note: Replace <dataset_id> with the ID of the dataset that you want to verify.

Verify that the defaultTableExpirationMs field is set to -1 in the output. This indicates that the dataset is not publicly accessible.
Repeat step 8 for all the datasets that you have remediated.

By following these steps, you can remediate the misconfiguration of BigQuery datasets being anonymously or publicly accessible in GCP using GCP CLI.

Using Python

To remediate the misconfiguration of BigQuery datasets being publicly accessible, you can use the following Python code:

First, you need to authenticate and authorize your Python script to access the Google Cloud Platform. For this, you can use the google-auth and google-auth-oauthlib libraries. Here is an example of how to authenticate and authorize:

from google.oauth2 import service_account
from google.auth.transport.requests import AuthorizedSession

# Load the service account credentials
credentials = service_account.Credentials.from_service_account_file(
    'path/to/service_account.json'
)

# Create an authorized session using the credentials
session = AuthorizedSession(credentials)

Once you have authenticated and authorized your script, you can use the google-cloud-bigquery library to access the BigQuery API. Here is an example of how to check if a dataset is publicly accessible:

from google.cloud import bigquery

# Create a BigQuery client
client = bigquery.Client()

# Get the dataset reference
dataset_ref = client.dataset('my_dataset')

# Get the dataset metadata
dataset = client.get_dataset(dataset_ref)

# Check if the dataset is publicly accessible
if dataset.acl_entries:
    for entry in dataset.acl_entries:
        if entry.role == 'READER' and entry.entity_type == 'userByEmail' and entry.entity_id == '':
            print('Dataset is publicly accessible')

If the dataset is publicly accessible, you can revoke the public access by removing the READER role for the anonymous user. Here is an example of how to do this:

from google.cloud import bigquery

# Create a BigQuery client
client = bigquery.Client()

# Get the dataset reference
dataset_ref = client.dataset('my_dataset')

# Get the dataset metadata
dataset = client.get_dataset(dataset_ref)

# Remove the public access
if dataset.acl_entries:
    for entry in dataset.acl_entries:
        if entry.role == 'READER' and entry.entity_type == 'userByEmail' and entry.entity_id == '':
            dataset.acl_entries.remove(entry)
            client.update_dataset(dataset, ['acl_entries'])
            print('Public access revoked')

By following these steps, you can remediate the misconfiguration of BigQuery datasets being publicly accessible in GCP using Python.

Using Terraform

resource "google_bigquery_dataset" "dataset" {
  dataset_id                  = "DATASET_ID"        # e.g. "analytics_events"
  project                     = "PROJECT_ID"        # GCP project ID
  location                    = "DATASET_LOCATION"  # e.g. "US"
  default_table_expiration_ms = 0
}

# IAM binding WITHOUT any public/anonymous principals
# Remove any existing bindings/members that use:
#   - "allUsers"
#   - "allAuthenticatedUsers"
resource "google_bigquery_dataset_iam_binding" "dataset_viewers" {
  dataset_id = google_bigquery_dataset.dataset.dataset_id
  project    = google_bigquery_dataset.dataset.project
  role       = "roles/bigquery.dataViewer"

  members = [
    "group:SECURITY_REVIEWED_GROUP@EXAMPLE.COM",
    "user:APP_SERVICE_ACCOUNT@PROJECT_ID.iam.gserviceaccount.com",
    # DO NOT include "allUsers" or "allAuthenticatedUsers" here
  ]
}

Changing only IAM bindings/members does not replace the dataset; it applies in-place. After editing out any allUsers / allAuthenticatedUsers references from all google_bigquery_dataset_iam_* resources, terraform plan should show those principals being removed from the dataset IAM policy and no other changes.

GCP Introduction

GCP Pricing

GCP Threats

GCP Misconfigurations

Resources

GCP Threats

Ensure that BigQuery datasets are not anonymously or publicly accessible

More Info:

Risk Level

Address

Compliance Standards

Triage and Remediation

Remediation

Additional Reading:

​More Info:

​Risk Level

​Address

​Compliance Standards

​Triage and Remediation

​Remediation

​

​Additional Reading: