Logo of Trend Micro
Use the Knowledge Base AI to help improve your Cloud Posture

Enable BigQuery Encryption with Customer-Managed Keys

Trend Vision One™ provides continuous assurance that gives peace of mind for your cloud infrastructure, delivering over 1100 automated best practice checks.

Risk Level: High (not acceptable risk)
Rule ID: BigQuery-002

Ensure that the tables created for your Google Cloud BigQuery datasets are encrypted with Customer-Managed Keys (CMKs) in order to have a more granular control over data encryption/decryption process. A BigQuery dataset table contains individual records organized in rows where each data record is composed of columns (also called fields). You can create, rotate, manage and destroy your own Customer-Managed Keys (CMKs) using Google Cloud Key Management Service (Cloud KMS).

This rule resolution is part of the Conformity Security & Compliance tool for GCP.

Security

By default, the BigQuery service encrypts all your content stored at rest using Google-managed encryption keys. The cloud service manages this type of encryption without any additional actions from you and your application. However, if you have sensitive or confidential data to encrypt, and you need to fully control and manage encryption yourself, you can create and use Customer-Managed Keys (CMKs). Instead of letting Google to manage the encryption keys that protect your BigQuery data, you can choose to create and manage your own encryption keys using Cloud KMS service.

Audit

To determine if your Google Cloud BigQuery dataset tables are encrypted with Customer-Managed Keys (CMKs), perform the following operations:

Using GCP Console

01 Sign in to Google Cloud Management Console.

02 Select the Google Cloud Platform (GCP) project that you want to examine from the console top navigation bar.

03 Navigate to Google Cloud BigQuery dashboard at https://console.cloud.google.com/bigquery.

04 In the navigation panel, expand the selection menu for your chosen subscription, navigate to find the name of the chosen dataset, and click on the specific BigQuery table that you want to examine.

05 Alternatively, use the search bar to search for and identify the BigQuery table that you want to examine.

06 Select the Details tab from the dashboard bottom panel to access the configuration details available for the selected table.

07 On the Details panel, within the Table info section, search for the Customer-managed key configuration attribute. If the Customer-managed key attribute is not listed in the table information section, the selected Google Cloud BigQuery dataset table is not encrypted using a Customer-Managed Key (CMK).

08 Repeat steps no. 5 – 7 for each BigQuery table created for the selected dataset.

09 Repeat steps no. 4 – 8 for each BigQuery dataset available within the selected GCP project.

10 Repeat steps no. 2 – 9 for each project deployed in your Google Cloud account.

Using GCP CLI

01 Run projects list command (Windows/macOS/Linux) using custom query filters to list the IDs of all the Google Cloud Platform (GCP) projects available in your cloud account:

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899
gcloud projects list
	--format="table(projectId)"

02 The command output should return the requested GCP project IDs:

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899
PROJECT_ID
cc-ml-project-112233
cc-data-project-123123

03 Run bq ls command (using bq Python tool) to list the identifier (name) of each BigQuery dataset created for the specified Google Cloud project:

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899
bq ls
	--project_id cc-ml-project-112233
	--format=pretty

04 The command output should return a table with the requested dataset identifier(s):

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899
+------------------------+
|       datasetId        |
+------------------------+
| cc_analytics_dataset   |
| cc_ad_traffic_dataset  |
+------------------------+

05 Run bq ls command (using bq tool) using the ID of the GCP project and the name of the BigQuery dataset that you want to examine as identifier parameters, to list the identifier (ID) of each table created for the selected dataset:

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899
bq ls cc-ml-project-112233:cc_analytics_dataset

06 The command output should return the ID and type of each table within the dataset:

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899
    tableId       Type   Labels  Time Partitioning  Clustered Fields
------------------  -----  ------  -----------------  ----------------
cc_pageviews_table  TABLE
cc_users_table      TABLE
cc_visits_table     TABLE

07 Run bq show command (using bq tool) using the ID of the project, the name of the dataset, and the ID of the associated table that you want to examine as identifier parameters, to describe the Cloud KMS encryption configuration available for the selected BigQuery dataset table:

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899
bq show
	--format=prettyjson
	cc-ml-project-112233:cc_analytics_dataset.cc_pageviews_table | jq '.encryptionConfiguration'

08 The command output should return the requested configuration information (i.e. the name of the Cloud KMS Customer-Managed Key used to encrypt the table data):

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899
null

If the bq show command output returns null, as shown in the example above, the selected Google Cloud BigQuery dataset table is not encrypted using a Customer-Managed Key (CMK).

09 Repeat step no. 7 and 8 for each BigQuery table created for the selected dataset.

10 Repeat steps no. 5 – 9 for each BigQuery dataset available in the selected GCP project.

11 Repeat steps no. 3 – 10 for each project deployed within your Google Cloud account.

Remediation / Resolution

To enable encryption with Cloud KMS Customer-Managed Keys (CMKs) for your BigQuery dataset tables, perform the following operations:

Note: Configuring Customer-Managed Keys (CMKs) for BigQuery dataset tables using Google Cloud Platform (GCP) Console is not currently supported.

Using GCP CLI and BQ CLI

01 To set up and manage Customer-Managed Keys (CMKs), you must create a key ring first. A KMS key ring is a grouping of cryptographic keys made available for organizational purposes in a specific Google Cloud location. Run kms keyrings create command (Windows/macOS/Linux) to create a new Cloud KMS key ring in the specified location (must match the BigQuery dataset location):

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899
gcloud kms keyrings create cc-ml-project-key-ring
	--location=us
	--project=cc-ml-project-112233
	--format="table(name)"

02 The command output should return the identifier (name) of the newly created key ring:

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899
NAME
projects/cc-ml-project-112233/locations/us/keyRings/cc-ml-project-key-ring

03 Run kms keys create command (Windows/macOS/Linux) to create a new Cloud KMS Customer-Managed Key (CMK) within the KMS key ring created at the previous steps:

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899
gcloud kms keys create cc-bigquery-cmk
	--location=us
	--keyring=cc-ml-project-key-ring
	--purpose=encryption
	--protection-level=software
	--rotation-period=90d
	--next-rotation-time=2020-10-10T12:00:00.0000Z
	--format="table(name)"

04 The command output should return the name of the new Customer-Managed Key (CMK):

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899
NAME
projects/cc-ml-project-112233/locations/us/keyRings/cc-ml-project-key-ring/cryptoKeys/cc-bigquery-cmk

05 Run projects add-iam-policy-binding command (Windows/macOS/Linux) to assign the Cloud KMS "CryptoKey Encrypter/Decrypter" role to the BigQuery system service account. Replace <kms-project-id> with the ID of the Google Cloud project where the Customer-Managed Keys are provisioned, and replace <project-number> with the project number (not the project ID) of the Google Cloud project that is running your BigQuery datasets:

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899
gcloud projects add-iam-policy-binding <kms-project-id>
	--member serviceAccount:bq-<project-number>@bigquery-encryption.iam.gserviceaccount.com
	--role roles/cloudkms.cryptoKeyEncrypterDecrypter

06 The command output should return the updated IAM policy (YAML format):

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899
Updated IAM policy for project <kms-project-id>.
bindings:
- members:
  - serviceAccount:bq-<project-number>@bigquery-encryption.iam.gserviceaccount.com
  role: roles/cloudkms.cryptoKeyEncrypterDecrypter
- members:
  - serviceAccount:bq-<project-number>@bigquery-encryption.iam.gserviceaccount.com
  - user:admin@cloudconformity.com
  role: roles/owner
etag: abcdabcdabcd
version: 1

07 Run bq cp command (using bq tool) using the name of the new Cloud KMS Customer-Managed Key (CMK) as value for the --destination_kms_key parameter, to copy the selected BigQuery dataset table, protected by default encryption into the original (source) table, protected by the specified Customer-Managed Key (CMK):

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899
bq cp -f
	--destination_kms_key projects/cc-ml-project-112233/locations/global/keyRings/cc-ml-project-key-ring/cryptoKeys/cc-bigquery-cmk cc_analytics_dataset.cc_pageviews_table cc_analytics_dataset.cc_pageviews_table

08 The output should return the bq cp command request status:

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899
Waiting on bqjob_abcdabcdabcdabcd_012342123412341234 ... (0s) Current status: DONE
Table 'cc-ml-project-112233:cc_analytics_dataset.cc_pageviews_table' successfully copied to 'cc-ml-project-112233:cc_analytics_dataset.cc_pageviews_table'

09 Repeat step no. 7 and 8 to enable encryption at rest with Customer-Managed Keys (CMKs) for other BigQuery tables created for the selected dataset.

10 Repeat steps no. 7 – 9 for each BigQuery dataset available in the selected GCP project.

11 Repeat steps no. 1 – 10 for each project deployed within your Google Cloud account.

References

Publication date Apr 21, 2021

Related BigQuery rules