GCP BigQuery Should Have Audit Logging Enabled

More Info:

Ensure that BigQuery Audit Logging is configured properly across all projects.

Risk Level

Medium

Address

Security

Compliance Standards

• APRA CPS 234 (Australia)
• BSI C5 (Germany)
• Brazil LGPD
• CCPA / CPRA (California)
• CIS Critical Security Controls v8
• CMMC 2.0
• CSA Cloud Controls Matrix v4
• DPDPA
• Digital Operational Resilience Act (EU)
• Essential 8
• HITRUST CSF
• ISO/IEC 27017
• ISO/IEC 27018
• ISO/IEC 27701
• KSA PDPL
• MAS Technology Risk Management (Singapore)
• MITRE ATT&CK (Cloud)
• NIS2 Directive
• NIST CSF
• NIST SP 800-171
• NYDFS 23 NYCRR 500
• PCI
• Reserve Bank of India (RBI) Cyber Security Framework
• Reserve Bank of India (RBI) Master Direction – Information Technology Framework
• SOC2
• SWIFT Customer Security Controls Framework
• Sarbanes-Oxley IT General Controls
• Securities and Exchange Board of India (SEBI) - Cloud Security Adoption Framework
• UK NCSC Cyber Assessment Framework

Triage and Remediation

Remediation

Remediation

Using Console

To remediate the misconfiguration “GCP BigQuery Should Have Audit Logging Enabled” for GCP using GCP console, you can follow the below steps:

1. Open the Google Cloud Console and select the project where BigQuery is enabled.

2. Go to the Navigation menu and select “BigQuery”.

3. In the BigQuery console, click on the “More” button (three dots) on the left-hand side and select “View in APIs Explorer”.

4. In the APIs Explorer, search for “tables.insert” in the search bar.

5. In the “tables.insert” API, scroll down to the “Request body” section and add the following JSON code:

{
  "tableReference": {
    "projectId": "project-id",
    "datasetId": "dataset-id",
    "tableId": "table-id"
  },
  "schema": {
    "fields": [
      {
        "name": "column1",
        "type": "STRING"
      },
      {
        "name": "column2",
        "type": "INTEGER"
      }
    ]
  },
  "labels": {
    "key": "value"
  },
  "timePartitioning": {
    "type": "DAY",
    "field": "timestamp"
  }
}

6. Click on the “Authorize and execute” button.

7. On the next screen, click on the “Execute” button.

8. Go back to the BigQuery console and click on the “More” button (three dots) on the left-hand side.

9. Select “Audit logs” and ensure that the logs are enabled.

By following these steps, you will remediate the misconfiguration “GCP BigQuery Should Have Audit Logging Enabled” for GCP using GCP console.

Using CLI

To remediate the misconfiguration of GCP BigQuery not having audit logging enabled, follow these steps using GCP CLI:

1. Open the Cloud Shell in your GCP console.

2. Run the following command to verify if audit logging is enabled for BigQuery:

gcloud logging logs list | grep bigquery

If you see any results, it means audit logging is already enabled. If not, proceed to the next step.

3. Run the following command to enable audit logging for BigQuery:

gcloud config set project <project-id>
gcloud services enable bigquery.googleapis.com
gcloud logging sinks create bigquery-audit \
  bigquery.googleapis.com/activity \
  --log-filter='protoPayload.serviceName="bigquery.googleapis.com"'

Note: Replace <project-id> with your GCP project ID.

4. Run the following command to verify if the sink was created successfully:

gcloud logging sinks list

5. Run the following command to grant the necessary permissions to the sink:

gcloud projects add-iam-policy-binding <project-id> \
  --member=serviceAccount:[e-mail] \
  --role=roles/bigquery.dataViewer

6. Run the following command to create a dataset in BigQuery to store the audit logs:

bq mk <dataset-name>

Note: Replace <dataset-name> with the desired name for your dataset.

7. Run the following command to create a table in the dataset to store the audit logs:

bq mk --table <dataset-name>.<table-name> \
  protoPayload \
  'timestamp:TIMESTAMP,protoPayload:STRING,severity:STRING,logName:STRING,resource:STRUCT<type:string,labels:map<string,string>>,operation:STRUCT<id:string,producer:string,first:BOOL,last:BOOL>,trace:STRING,spanId:STRING,receiveTimestamp:TIMESTAMP'

Note: Replace <dataset-name> and <table-name> with the desired names for your dataset and table.

8. Run the following command to create a sink to export the audit logs to the BigQuery table:

gcloud logging sinks create bigquery-audit \
  bigquery.googleapis.com/activity \
  --log-filter='protoPayload.serviceName="bigquery.googleapis.com"' \
  --destination=<project-id>:<dataset-name>.<table-name> \
  --include-children \
  --format='json'

Note: Replace <project-id>, <dataset-name> and <table-name> with the names you used in steps 6 and 7.

9. Run the following command to verify if the sink was created successfully:

gcloud logging sinks describe bigquery-audit

After following these steps, audit logging will be enabled for BigQuery in your GCP project, and the audit logs will be exported to the BigQuery table you created.

Using Python

To remediate the misconfiguration “GCP BigQuery should have audit logging enabled” for GCP using Python, follow the below steps:

1. Install the Google Cloud SDK and authenticate using the following command:

gcloud auth login

2. Install the necessary Python libraries:

pip install google-cloud-bigquery google-auth google-auth-oauthlib google-auth-httplib2

3. Create a Python script with the following code:

from google.cloud import bigquery

client = bigquery.Client()

dataset_id = 'my_dataset'

dataset = client.get_dataset(dataset_id)

if not dataset.auditing_logs:
    dataset.auditing_logs = True
    dataset = client.update_dataset(dataset, ['auditing_logs'])

print('Audit logging enabled for dataset {}'.format(dataset_id))

4. Replace my_dataset with the ID of the dataset you want to enable audit logging for.

5. Run the script using the following command:

python script.py

This will enable audit logging for the specified dataset in GCP BigQuery.

Using Terraform

resource "google_project_iam_audit_config" "bigquery_audit_logging" {
  project = "YOUR_PROJECT_ID" # replace with your GCP project ID

  service = "bigquery.googleapis.com"

  audit_log_config {
    log_type = "ADMIN_READ"
  }

  audit_log_config {
    log_type = "DATA_READ"
  }

  audit_log_config {
    log_type = "DATA_WRITE"
  }
}

BigQuery audit logging is not configured on individual datasets/tables in Terraform; it is enabled at the project (or organization/folder) IAM audit config level as shown above.

This change is non-destructive and does not force replacement of any BigQuery datasets or tables.

For verification, terraform plan should show creation (or update) of a google_project_iam_audit_config resource with service = "bigquery.googleapis.com" and the three audit_log_config blocks for ADMIN_READ, DATA_READ, and DATA_WRITE.

Additional Reading:

• https://cloud.google.com/bigquery/docs/reference/auditlogs