Enable BigQuery Encryption with Customer-Managed Keys

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:

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

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

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:

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

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

+------------------------+
|       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:

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:

    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:

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):

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):

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:

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:

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):

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:

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):

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):

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:

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

Google Cloud Platform (GCP) Documentation
BigQuery
Manage tables
Encryption at rest
Customer-managed Cloud KMS keys
Cloud Key Management
Encryption at rest in Google Cloud

GCP Command Line Interface (CLI) Documentation
gcloud projects list
gcloud kms keyrings create
gcloud kms keys create
gcloud projects add-iam-policy-binding

BQ Command Line Interface (CLI) Documentatio
Using the bq command-line tool
Listing datasets
Getting information about datasets

Publication date Apr 21, 2021