Skip to main content

GCP BigQuery Tables Should Be Encrypted

More Info:​

Ensure that BigQuery Tables should be encrypted

Risk Level​

High

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)
  • GDPR
  • 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
  • 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​

Using Console

To remediate the misconfiguration of GCP BigQuery Tables should be encrypted, you can follow the below steps using GCP console:

  1. Open the Google Cloud Console and select your project.

  2. In the navigation menu, select "BigQuery" under the "Big Data" section.

  3. Select the dataset that contains the tables you want to encrypt.

  4. Click on the "Encrypt" button in the top menu bar.

  5. Select the "Customer-managed key" option.

  6. Choose the key you want to use to encrypt the data.

  7. Click on the "Encrypt" button to start the encryption process.

  8. Wait for the encryption process to complete.

  9. Once the encryption process is complete, all the tables in the selected dataset will be encrypted using the customer-managed key.

  10. Verify that the tables are encrypted by checking the "Encryption" column in the table list. It should show "Customer-managed key".

By following these steps, you can remediate the misconfiguration of GCP BigQuery Tables should be encrypted.

Using CLI

To remediate the misconfiguration "GCP BigQuery Tables Should Be Encrypted" for GCP using GCP CLI, follow these steps:

  1. Open the Cloud Shell from the GCP console.
  2. Run the following command to enable the Cloud KMS API:
gcloud services enable cloudkms.googleapis.com
  1. Create a new keyring for BigQuery table encryption by running the following command:
gcloud kms keyrings create <KEYRING_NAME> --location <LOCATION>

Note: Replace <KEYRING_NAME> with a name of your choice and <LOCATION> with the location where you want to create the keyring. 4. Create a new key in the keyring by running the following command:

gcloud kms keys create <KEY_NAME> --keyring <KEYRING_NAME> --location <LOCATION> --purpose encryption

Note: Replace <KEY_NAME> with a name of your choice and <KEYRING_NAME> and <LOCATION> with the names you used in step 3. 5. Grant the BigQuery service account the necessary permissions to use the key by running the following command:

gcloud kms keys add-iam-policy-binding <KEY_NAME> --keyring <KEYRING_NAME> --location <LOCATION> --member serviceAccount:<BIGQUERY_SERVICE_ACCOUNT_EMAIL> --role roles/cloudkms.cryptoKeyEncrypterDecrypter

Note: Replace <KEY_NAME>, <KEYRING_NAME>, <LOCATION>, and <BIGQUERY_SERVICE_ACCOUNT_EMAIL> with the appropriate values. 6. Update the BigQuery table to use the new key for encryption by running the following command:

bq update --table --customer-managed-encryption <KEY_NAME> <DATASET_NAME>.<TABLE_NAME>

Note: Replace <KEY_NAME>, <DATASET_NAME>, and <TABLE_NAME> with the appropriate values. 7. Verify that the BigQuery table is now encrypted by running the following command:

bq show --format=prettyjson <DATASET_NAME>.<TABLE_NAME> | grep -i "encryption"

The output should show that the table is encrypted using the specified key.

By following these steps, you can remediate the misconfiguration "GCP BigQuery Tables Should Be Encrypted" for GCP using GCP CLI.

Using Python

To remediate the misconfiguration "GCP BigQuery Tables Should Be Encrypted", you can use the following steps:

  1. Install the Google Cloud SDK by following the instructions provided in the official documentation.

  2. Once you have installed the Google Cloud SDK, authenticate with your GCP account by running the following command:

    gcloud auth login

  3. Create a Python script to encrypt all the tables in your BigQuery dataset. You can use the following code as a starting point:

    from google.cloud import bigquery

    # Initialize the BigQuery client
    client = bigquery.Client()

    # Set the dataset ID
    dataset_id = 'your_dataset_id'

    # Get a list of all the tables in the dataset
    tables = client.list_tables(dataset_id)

    # Encrypt each table in the dataset
    for table in tables:
        table_ref = client.dataset(dataset_id).table(table.table_id)
        table = client.get_table(table_ref)
        table.encryption_configuration = bigquery.EncryptionConfiguration(
            kms_key_name='projects/your_project_id/locations/your_location/keyRings/your_key_ring/cryptoKeys/your_crypto_key'
        )
        client.update_table(table, ['encryption_configuration'])

    Replace the your_dataset_id, your_project_id, your_location, your_key_ring, and your_crypto_key placeholders with the actual values.

  4. Save the Python script and run it using the following command:

    python your_script_name.py

    This will encrypt all the tables in your BigQuery dataset using the specified KMS key.

  5. Verify that the tables have been encrypted by running the following command:

    bq show --format=prettyjson your_dataset_id.your_table_id

    This will display the table metadata, including the encryption configuration.

Using Terraform
# Replace the placeholders with your project, dataset, table, and KMS key.
# This configures CMEK encryption for a BigQuery table.

resource "google_bigquery_table" "encrypted_table" {
  project    = "GCP_PROJECT_ID"
  dataset_id = "BIGQUERY_DATASET_ID"
  table_id   = "BIGQUERY_TABLE_ID"

  # ... your existing schema / table configuration ...

  encryption_configuration {
    kms_key_name = "projects/KMS_PROJECT_ID/locations/KMS_LOCATION/keyRings/KEY_RING_ID/cryptoKeys/KEY_ID"
  }
}

# (Optional but commonly needed) Allow BigQuery to use the KMS key.
resource "google_kms_crypto_key_iam_binding" "bq_table_cmek_binding" {
  crypto_key_id = "projects/KMS_PROJECT_ID/locations/KMS_LOCATION/keyRings/KEY_RING_ID/cryptoKeys/KEY_ID"
  role          = "roles/cloudkms.cryptoKeyEncrypterDecrypter"

  members = [
    # Replace with the BigQuery service account for your project:
    # Format: serviceAccount:service-PROJECT_NUMBER@gcp-sa-bigquery.iam.gserviceaccount.com
    "serviceAccount:BIGQUERY_SERVICE_ACCOUNT_EMAIL",
  ]
}

Changing an existing table’s encryption_configuration.kms_key_name typically forces replacement of the table, which can be disruptive if it holds data; plan carefully or recreate via a migration process.

To verify, terraform plan should show an update (or new resource) adding the encryption_configuration block with your kms_key_name and, if used, the new google_kms_crypto_key_iam_binding.

Additional Reading:​