Bigquery datasets not publicly accessible remediation

Using Console

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.

GCP Introduction

GCP Pricing

GCP Threats

GCP Misconfigurations

Resources

GCP Threats

Bigquery datasets not publicly accessible remediation

Triage and Remediation

Remediation

GCP Introduction

GCP Pricing

GCP Threats

GCP Misconfigurations

Resources

GCP Threats

​Triage and Remediation

​Remediation

Triage and Remediation

Remediation