​
More Info:

Ensure that BigQuery Tables should be encrypted

​
Risk Level

High

​
Address

Security

​
Compliance Standards

GDPR, NISTCSF, PCIDSS

​
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.

​

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.
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.

​
Additional Reading: