Skip to main content

GCP BigQuery Tables Should Be Encrypted With Customer

More Info:​

Ensure that BigQuery Tables are encrypted with CMKs

Risk Level​

Medium

Address​

Security

Compliance Standards​

  • APRA CPS 234 (Australia)
  • BSI C5 (Germany)
  • Brazil LGPD
  • CCPA / CPRA (California)
  • CIS Critical Security Controls v8
  • CIS GCP
  • CIS GCP 2.0.0
  • CMMC 2.0
  • CSA Cloud Controls Matrix v4
  • Cloudanix Best Practice
  • DPDPA
  • Digital Operational Resilience Act (EU)
  • Essential 8
  • FedRAMP
  • 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
  • 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​

Using Console

To remediate the misconfiguration "GCP BigQuery Tables Should Be Encrypted With Customer Managed Keys", you can follow the below steps:

  1. Log in to your GCP console.

  2. Navigate to the BigQuery section.

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

  4. Click on the "Show Info Panel" button (i) next to the dataset name.

  5. In the "Encryption" section, click on the "Edit" button.

  6. Select the "Customer-managed encryption keys" option.

  7. Click on the "Create or select a key" button.

  8. Choose an existing key or create a new one.

  9. Click on the "Save" button.

  10. Repeat the above steps for each table in the dataset.

By following these steps, you can remediate the misconfiguration "GCP BigQuery Tables Should Be Encrypted With Customer Managed Keys" and ensure that your BigQuery tables are encrypted with customer-managed keys.

Using CLI

To remediate the misconfiguration of GCP BigQuery tables not being encrypted with customer-managed keys, you can follow the below steps using GCP CLI:

  1. Firstly, create a customer-managed encryption key in Cloud Key Management Service (KMS) using the following command:
gcloud kms keyrings create [KEYRING_NAME] --location [LOCATION]
gcloud kms keys create [KEY_NAME] --location [LOCATION] --keyring [KEYRING_NAME] --purpose encryption

Replace [KEYRING_NAME], [LOCATION] and [KEY_NAME] with your preferred values.

  1. Next, grant the BigQuery service account the necessary permissions to use the encryption key by running the following command:
gcloud kms keys add-iam-policy-binding [KEY_NAME] --location [LOCATION] --keyring [KEYRING_NAME] --member serviceAccount:[SERVICE_ACCOUNT_EMAIL] --role roles/cloudkms.cryptoKeyEncrypterDecrypter

Replace [KEYRING_NAME], [LOCATION], [KEY_NAME] and [SERVICE_ACCOUNT_EMAIL] with your preferred values.

  1. Now, create a new BigQuery dataset or update an existing one to use the customer-managed encryption key by running the following command:
bq update --default_table_expiration [INTEGER_VALUE] --description [DESCRIPTION] --encryption_kms_key_name projects/[PROJECT_ID]/locations/[LOCATION]/keyRings/[KEYRING_NAME]/cryptoKeys/[KEY_NAME] [DATASET_NAME]

Replace [INTEGER_VALUE], [DESCRIPTION], [PROJECT_ID], [LOCATION], [KEYRING_NAME], [KEY_NAME] and [DATASET_NAME] with your preferred values.

  1. Finally, ensure that all existing tables in the dataset are encrypted with the customer-managed key by running the following command:
bq update --table_kms_key projects/[PROJECT_ID]/locations/[LOCATION]/keyRings/[KEYRING_NAME]/cryptoKeys/[KEY_NAME] [DATASET_NAME].[TABLE_NAME]

Replace [PROJECT_ID], [LOCATION], [KEYRING_NAME], [KEY_NAME], [DATASET_NAME] and [TABLE_NAME] with your preferred values.

By following the above steps, you can remediate the misconfiguration of GCP BigQuery tables not being encrypted with customer-managed keys.

Using Python

To remediate the misconfiguration of GCP BigQuery Tables not being encrypted with customer managed keys, you can follow the below steps using Python:

  1. First, you need to create a customer-managed encryption key (CMEK) in the Google Cloud Key Management Service (KMS) using the following code:
from google.cloud import kms_v1
from google.oauth2 import service_account

credentials = service_account.Credentials.from_service_account_file('<path-to-service-account-key-file>')
kms_client = kms_v1.KeyManagementServiceClient(credentials=credentials)

parent = kms_client.key_ring_path('<project-id>', '<location>', '<key-ring>')
purpose = kms_v1.CryptoKey.CryptoKeyPurpose.ENCRYPT_DECRYPT

response = kms_client.create_crypto_key(parent=parent, crypto_key_id='<key-id>', crypto_key={'purpose': purpose})
print(f'Created CMEK: {response.name}')
  1. Next, you need to update the BigQuery table to use the newly created CMEK for encryption using the following code:
from google.cloud import bigquery
from google.oauth2 import service_account

credentials = service_account.Credentials.from_service_account_file('<path-to-service-account-key-file>')
bq_client = bigquery.Client(credentials=credentials, project='<project-id>')

dataset_ref = bq_client.dataset('<dataset-id>')
table_ref = dataset_ref.table('<table-id>')
table = bq_client.get_table(table_ref)

table.encryption_configuration = bigquery.EncryptionConfiguration(
    kms_key_name=f'projects/<project-id>/locations/<location>/keyRings/<key-ring>/cryptoKeys/<key-id>'
)

table = bq_client.update_table(table, ['encryption_configuration'])
print(f'Table {table.table_id} is now encrypted with CMEK')

Once you run the above two code snippets, the BigQuery table will be encrypted with the newly created CMEK.

Using Terraform
# Customer-managed encryption key (CMEK) for BigQuery
resource "google_kms_crypto_key" "bq_cmk" {
  name            = "BIGQUERY_CMEK_NAME"                # replace with your KMS key name
  key_ring        = "projects/PROJECT_ID/locations/LOCATION/keyRings/KEY_RING_NAME" # replace with your key ring path
  rotation_period = "2592000s"                          # 30 days; adjust as needed
}

# BigQuery dataset (must be in same location as the KMS key)
resource "google_bigquery_dataset" "dataset" {
  dataset_id                 = "BIGQUERY_DATASET_ID"     # replace with your dataset id
  project                    = "PROJECT_ID"              # replace with your project id
  location                   = "LOCATION"                # must match the KMS key location
  delete_contents_on_destroy = false
}

# BigQuery table encrypted with the CMEK
resource "google_bigquery_table" "table" {
  dataset_id = google_bigquery_dataset.dataset.dataset_id
  table_id   = "BIGQUERY_TABLE_ID"                       # replace with your table id
  project    = google_bigquery_dataset.dataset.project

  # REQUIRED: apply customer-managed key encryption to this table
  encryption_configuration {
    kms_key_name = google_kms_crypto_key.bq_cmk.id
  }

  schema = <<EOF
[
  {
    "name": "column1",
    "type": "STRING",
    "mode": "NULLABLE"
  }
]
EOF
}

Changing encryption_configuration.kms_key_name on an existing google_bigquery_table forces replacement of the table resource, which will drop and recreate the table (and its data) unless you manage data migration separately.

To verify, terraform plan should show the google_bigquery_table resource with encryption_configuration.kms_key_name being added or changed from null (or the old key) to the CMEK’s full resource ID.

Additional Reading:​