Customer-managed Cloud KMS keys
By default, BigQuery encrypts your content stored at rest. BigQuery handles and manages this default encryption for you without any additional actions on your part. First, data in a BigQuery table is encrypted using a data encryption key. Then, those data encryption keys are encrypted with key encryption keys, which is known as envelope encryption. Key encryption keys don't directly encrypt your data but are used to encrypt the data encryption keys that Google uses to encrypt your data. For more information, see Cloud Key Management Service (KMS).
If you want to control encryption yourself, you can use customer-managed encryption keys (CMEK) for BigQuery. Instead of Google managing the key encryption keys that protect your data, you control and manage key encryption keys in Cloud KMS. This document provides details about this technique.
Learn more about encryption options on Google Cloud. For specific information about CMEK, including its advantages and limitations, see Customer-managed encryption keys.
Before you begin
All data assets residing in BigQuery managed storage support CMEK. However, if you are also querying data stored in an external data source such as Cloud Storage that has CMEK-encrypted data, then the data encryption is managed by Cloud Storage. For example, BigLake tables support data encrypted with CMEK in Cloud Storage.
BigQuery and BigLake tables don't support Customer-Supplied Encryption Keys (CSEK).
Decide whether you are going to run BigQuery and Cloud KMS in the same Google Cloud project, or in different projects. For documentation example purposes, the following convention is used:
PROJECT_ID
: the project ID of the project running BigQueryPROJECT_NUMBER
: the project number of the project running BigQueryKMS_PROJECT_ID
: the project ID of the project running Cloud KMS (even if this is the same project running BigQuery)
BigQuery is automatically enabled in new projects. If you are using a pre-existing project to run BigQuery, enable the BigQuery API.
For the Google Cloud project that runs Cloud KMS:
- Enable the Cloud Key Management Service API.
- Create a key ring and a key as described in
Creating key rings and keys. Create the key ring
in a location that matches the location of your BigQuery
dataset:
-
Any multi-regional dataset should use a multi-regional key ring from a
matching location. For examples, a dataset in region
US
should be protected with a key ring from regionus
, and a dataset in regionEU
should be protected with a key ring from regioneurope
. -
Regional datasets should use a matching regional keys. For example,
a dataset in region
asia-northeast1
should be protected with a key ring from regionasia-northeast1
. -
The
global
region is not supported for use with BigQuery.
-
Any multi-regional dataset should use a multi-regional key ring from a
matching location. For examples, a dataset in region
A decryption call is performed using Cloud KMS once per query to a CMEK-encrypted table. For more information, see Cloud KMS pricing.
Encryption specification
Cloud KMS keys used to protect your data in BigQuery are AES-256 keys. These keys are used as key encryption keys in BigQuery, in that they encrypt the data encryption keys that encrypt your data.
Grant encryption and decryption permission
To protect your BigQuery data with a CMEK key, grant the BigQuery service account permission to encrypt and decrypt using that key. The Cloud KMS CryptoKey Encrypter/Decrypter role grants this permission.
Make sure your service account has been created, and then use the Google Cloud console to determine the BigQuery service account ID. Next, provide the service account with the appropriate role to encrypt and decrypt using Cloud KMS.
Trigger creation of your service account
Your BigQuery service account is not initially created when you
create a project. To trigger the creation of your service
account, enter a command that uses it, such as the
bq show --encryption_service_account
command, or call the
projects.getServiceAccount API method. For example:
bq show --encryption_service_account --project_id=PROJECT_ID
Determine the service account ID
The BigQuery service account ID is of the form:
bq-PROJECT_NUMBER@bigquery-encryption.iam.gserviceaccount.com
The following techniques show how you can determine the BigQuery service account ID for your project.
Console
Go to the Dashboard page in the Google Cloud console.
Click the Select from drop-down list at the top of the page. In the Select From window that appears, select your project.
Both the project ID and project number are displayed on the project Dashboard Project info card:
In the following string, replace PROJECT_NUMBER with your project number. The new string identifies your BigQuery service account ID.
bq-PROJECT_NUMBER@bigquery-encryption.iam.gserviceaccount.com
bq
Use the bq show
command with the --encryption_service_account
flag to
determine the service account ID:
bq show --encryption_service_account
The command displays the service account ID:
ServiceAccountID ------------------------------------------------------------- bq-PROJECT_NUMBER@bigquery-encryption.iam.gserviceaccount.com
Assign the Encrypter/Decrypter role
Assign the Cloud KMS CryptoKey Encrypter/Decrypter role to the BigQuery system service account that you copied to your clipboard. This account is of the form:
bq-PROJECT_NUMBER@bigquery-encryption.iam.gserviceaccount.com
Console
Open the Cryptographic Keys page in the Google Cloud console.
Click the name of the key ring that contains the key.
Click the checkbox for the encryption key to which you want to add the role. The Permissions tab opens.
Click Add member.
Enter the email address of the service account,
bq-PROJECT_NUMBER@bigquery-encryption.iam.gserviceaccount.com
.- If the service account is already on the members list, it has
existing roles. Click the current role drop-down list for the
bq-PROJECT_NUMBER@bigquery-encryption.iam.gserviceaccount.com
service account.
- If the service account is already on the members list, it has
existing roles. Click the current role drop-down list for the
Click the drop-down list for Select a role, click Cloud KMS, and then click the Cloud KMS CryptoKey Encrypter/Decrypter role.
Click Save to apply the role to the
bq-PROJECT_NUMBER@bigquery-encryption.iam.gserviceaccount.com
service account.
gcloud
You can use the Google Cloud CLI to assign the role:
gcloud kms keys add-iam-policy-binding \ --project=KMS_PROJECT_ID \ --member serviceAccount:bq-PROJECT_NUMBER@bigquery-encryption.iam.gserviceaccount.com \ --role roles/cloudkms.cryptoKeyEncrypterDecrypter \ --location=KMS_KEY_LOCATION \ --keyring=KMS_KEY_RING \ KMS_KEY
Replace the following:
KMS_PROJECT_ID
: the ID of your Google Cloud project that is running Cloud KMSPROJECT_NUMBER
: the project number (not project ID) of your Google Cloud project that is running BigQueryKMS_KEY_LOCATION
: the location name of your Cloud KMS keyKMS_KEY_RING
: the key ring name of your Cloud KMS keyKMS_KEY
: the key name of your Cloud KMS key
Key resource ID
The resource ID for the Cloud KMS key is required for CMEK use, as shown in the examples. This key is case-sensitive and in the form:
projects/KMS_PROJECT_ID/locations/LOCATION/keyRings/KEY_RING/cryptoKeys/KEY
Retrieve the key resource ID
Open the Cryptographic Keys page in the Google Cloud console.
Click the name of the key ring that contains the key.
For the key whose resource ID you are retrieving, click More more_vert.
Click Copy Resource Name. The resource ID for the key is copied to your clipboard. The resource ID is also known as the resource name.
Create a table protected by Cloud KMS
To create a table that is protected by Cloud KMS:
Console
Open the BigQuery page in the Google Cloud console.
In the Explorer panel, expand your project and select a dataset.
Expand the
Actions option and click Open.In the details panel, click Create table
.On the Create table page, fill in the information needed to create an empty table with a schema definition. Before you click Create Table, set the encryption type and specify the Cloud KMS key to use with the table:
- Click Advanced options.
- Click Customer-managed key.
- Select the key. If the key you want to use is not listed, enter the resource ID for the key.
Click Create table.
SQL
Use the
CREATE TABLE
statement
with the kms_key_name
option:
In the Google Cloud console, go to the BigQuery page.
In the query editor, enter the following statement:
CREATE TABLE DATASET_ID.TABLE_ID ( name STRING, value INT64 ) OPTIONS ( kms_key_name = 'projects/KMS_PROJECT_ID/locations/LOCATION/keyRings/KEY_RING/cryptoKeys/KEY');
Click
Run.
For more information about how to run queries, see Running interactive queries.
bq
You can use the bq
command-line tool with the --destination_kms_key
flag
to create the table. The --destination_kms_key
flag specifies the
resource ID of the key to use with the table.
To create an empty table with a schema:
bq mk --schema name:string,value:integer -t \ --destination_kms_key projects/KMS_PROJECT_ID/locations/LOCATION/keyRings/KEY_RING/cryptoKeys/KEY \ DATASET_ID.TABLE_ID
To create a table from a query:
bq query --destination_table=DATASET_ID.TABLE_ID \ --destination_kms_key projects/KMS_PROJECT_ID/locations/LOCATION/keyRings/KEY_RING/cryptoKeys/KEY \ "SELECT name,count FROM DATASET_ID.TABLE_ID WHERE gender = 'M' ORDER BY count DESC LIMIT 6"
For more information about the bq
command-line tool, see
Using the bq
command-line tool.
Go
Before trying this sample, follow the Go setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Go API reference documentation.
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for a local development environment.
Java
Before trying this sample, follow the Java setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Java API reference documentation.
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for a local development environment.
Python
Before trying this sample, follow the Python setup instructions in the
BigQuery quickstart using
client libraries.
For more information, see the
BigQuery Python API
reference documentation.
To authenticate to BigQuery, set up Application Default Credentials.
For more information, see
Set up authentication for a local development environment.
Query a table protected by a Cloud KMS key
No special arrangements are required to query a table protected by Cloud KMS. BigQuery stores the name of the key used to encrypt the table content and uses that key when a table protected by Cloud KMS is queried.
All existing tools, the BigQuery console, and the bq
command-line tool run
the same way as with default-encrypted tables, as long as
BigQuery has access to the Cloud KMS key used to encrypt
the table content.
Protect query results with a Cloud KMS key
By default, query results are stored in a temporary table encrypted with a Google-managed key. To use a Cloud KMS key to encrypt your query results instead, select one of the following options:
Console
Open the BigQuery page in the Google Cloud console.
Click Compose new query.
Enter a valid GoogleSQL query in the query text area.
Click More, click Query settings, then click Advanced options.
Select Customer-managed encryption.
Select the key. If the key you want to use is not listed, enter the resource ID for the key.
Click Save.
Click Run.
bq
Specify the flag --destination_kms_key
to protect the destination table or
query results (if using a temporary table) with your Cloud KMS key.
The --destination_kms_key
flag specifies the
resource ID of the key to use with the destination or
resulting table.
Optionally use the --destination_table
flag to specify the destination for
query results. If --destination_table
is not used, the query results are
written to a temporary table.
To query a table:
bq query \ --destination_table=DATASET_ID.TABLE_ID \ --destination_kms_key projects/KMS_PROJECT_ID/locations/LOCATION/keyRings/KEY_RING/cryptoKeys/KEY \ "SELECT name,count FROM DATASET_ID.TABLE_ID WHERE gender = 'M' ORDER BY count DESC LIMIT 6"
For more information about the bq
command-line tool, see
Using the bq
command-line tool.
Go
Java
Before trying this sample, follow the Java setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Java API reference documentation.
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for a local development environment.
Protect a new table with a customer-managed encryption key by setting the Table.encryption_configuration property to an EncryptionConfiguration object before creating the table.Python
Load a table protected by Cloud KMS
To load a data file into a table that is protected by Cloud KMS:
Console
Protect a load job destination table with a customer-managed encryption key by specifying the key when you load the table.
Open the BigQuery page in the Google Cloud console.
In the Explorer panel, expand your project and select a dataset.
In the details panel, click Create table.
Enter the options you want to use for loading the table, but before you click Create table, click Advanced options.
Under Encryption, select Customer-managed key.
Click the Select a customer-managed key drop-down list and select the key to use. If you don't see any keys available, enter a key resource ID.
Click Create table.
bq
Protect a load job destination table with a customer-managed encryption
key by setting the --destination_kms_key
flag.
bq --location=LOCATION load \ --autodetect \ --source_format=FORMAT \ --destination_kms_key projects/KMS_PROJECT_ID/locations/LOCATION/keyRings/KEY_RING/cryptoKeys/KEY \ DATASET.TABLE \ path_to_sourceFor example:
bq load \ --autodetect \ --source_format=NEWLINE_DELIMITED_JSON \ --destination_kms_key projects/KMS_PROJECT_ID/locations/LOCATION/keyRings/KEY_RING/cryptoKeys/KEY \ test2.table4 \ gs://cloud-samples-data/bigquery/us-states/us-states.json
Go
Java
Python
Protect a load job destination table with a customer-managed encryption
key by setting the
LoadJobConfig.destination_encryption_configuration
property to an EncryptionConfiguration
and load the table.
Stream into a table protected by Cloud KMS
You can stream data into your CMEK-protected BigQuery table without specifying any additional parameters. Note that this data is encrypted using your Cloud KMS key in the buffer as well as in the final location. Before using streaming with a CMEK table, review the requirements on key availability and accessibility.
Learn more about streaming at Streaming data into BigQuery.
Change a table from default encryption to Cloud KMS protection
bq
You can use the bq cp
command with the --destination_kms_key
flag
to copy a table protected by default encryption into a new table, or into
the original table, protected by Cloud KMS. The
--destination_kms_key
flag specifies the resource ID
of the key to use with the destination table.
To copy a table that has default encryption to a new table that has Cloud KMS protection:
bq cp \ --destination_kms_key projects/KMS_PROJECT_ID/locations/LOCATION/keyRings/KEY_RING/cryptoKeys/KEY \ SOURCE_DATASET_ID.SOURCE_TABLE_ID DESTINATION_DATASET_ID.DESTINATION_TABLE_ID
In you want to copy a table that has default encryption to the same table with Cloud KMS protection:
bq cp -f \ --destination_kms_key projects/KMS_PROJECT_ID/locations/LOCATION/keyRings/KEY_RING/cryptoKeys/KEY \ DATASET_ID.TABLE_ID DATASET_ID.TABLE_ID
If you want to change a table from Cloud KMS protection to default
encryption, copy the file to itself by running bq cp
without using the
--destination_kms_key
flag.
For more information about the bq
command-line tool, see
Using the bq
command-line tool.
Go
Java
Python
Before trying this sample, follow the Python setup instructions in the
BigQuery quickstart using
client libraries.
For more information, see the
BigQuery Python API
reference documentation.
To authenticate to BigQuery, set up Application Default Credentials.
For more information, see
Set up authentication for a local development environment.
Protect the destination of a table copy with a customer-managed
encryption key by setting the QueryJobConfig.destination_encryption_configuration
property to an EncryptionConfiguration
and copy the table.
Determine if a table is protected by Cloud KMS
In the Google Cloud console, click the blue arrow to the left of your dataset to expand it, or double-click the dataset name. This displays the tables and views in the dataset.
Click the table name.
Click Details. The Table Details page displays the table's description and table information.
If the table is protected by Cloud KMS, the Customer-Managed Encryption Key field displays the key resource ID.
Change the Cloud KMS key for a BigQuery table
To change the Cloud KMS key of an existing CMEK-protected table, you
can run an ALTER TABLE
query, use the API, or use the bq
command-line tool.
There are two ways to modify the Cloud KMS key using the API and the
bq
command-line tool: update
or cp
.
If you use update
, you can change the Cloud KMS key used for a
CMEK-protected table.
If you use cp
, you can change the Cloud KMS key used for a
CMEK-protected table, change a table from default encryption to CMEK-protection,
or change a table from CMEK-protection to default encryption.
An advantage of update
is it is faster than cp
and it lets you use
table decorators.
SQL
Use the
ALTER TABLE SET OPTIONS
statement
to update the kms_key_name
field for a table:
In the Google Cloud console, go to the BigQuery page.
In the query editor, enter the following statement:
ALTER TABLE DATASET_ID.mytable SET OPTIONS ( kms_key_name = 'projects/KMS_PROJECT_ID/locations/LOCATION/keyRings/KEY_RING/cryptoKeys/KEY');
Click
Run.
For more information about how to run queries, see Running interactive queries.
bq
You can use the bq cp
command with the --destination_kms_key
flag
to change the key for a table protected by Cloud KMS. The
--destination_kms_key
flag specifies the resource ID
of the key to use with the table.
bq update \ --destination_kms_key projects/KMS_PROJECT_ID/locations/LOCATION/keyRings/KEY_RING/cryptoKeys/KEY \ -t DATASET_ID.TABLE_ID
Go
Java
Python
Change the customer-managed encryption key for a table by changing the
Table.encryption_configuration
property to a new EncryptionConfiguration
object and update the table.
Set a dataset default key
You can set a dataset-wide default Cloud KMS key that applies to all newly created tables within the dataset, unless a different Cloud KMS key is specified when you create the table. The default key does not apply to existing tables. Changing the default key does not modify any existing tables and applies only to new tables created after the change.
You can apply, change, or remove a dataset default key by
specifying the default key in the
EncryptionConfiguration.kmsKeyName
field when you call thedatasets.insert
ordatasets.patch
methodsspecifying the default key in the
--default_kms_key
flag when you run thebq mk --dataset
command.bq mk \ --default_kms_key projects/KMS_PROJECT_ID/locations/LOCATION/keyRings/KEY_RING/cryptoKeys/KEY \ --dataset DATASET_ID
bq update \ --default_kms_key projects/KMS_PROJECT_ID/locations/LOCATION/keyRings/KEY_RING/cryptoKeys/KEY \ --dataset DATASET_ID
Set a project default key
You can set a project-wide default Cloud KMS key that applies to all query results and newly created tables in the project, unless you specify a different Cloud KMS key. The default key does not apply to existing tables. Changing the default key does not modify any existing tables and applies only to new tables created after the change.
SQL
Use the
ALTER PROJECT SET OPTIONS
statement
to update the default_kms_key_name
field for a project. You can find the
resource name for the key on the Cloud KMS page.
In the Google Cloud console, go to the BigQuery page.
In the query editor, enter the following statement:
ALTER PROJECT PROJECT_ID SET OPTIONS (
region-us.default_kms_key_name
= 'projects/KMS_PROJECT_ID/locations/LOCATION/keyRings/KEY_RING/cryptoKeys/KEY');Click
Run.
For more information about how to run queries, see Running interactive queries.
bq
You can use the bq
command to run an
ALTER PROJECT SET OPTIONS
statement
to update the default_kms_key_name
field for a project:
bq query --nouse_legacy_sql \
'ALTER PROJECT PROJECT_ID
SET OPTIONS (
`region-us.default_kms_key_name`
="projects/KMS_PROJECT_ID/locations/LOCATION/keyRings/KEY_RING/cryptoKeys/KEY");'
Remove BigQuery's access to the Cloud KMS key
You can remove BigQuery's access to the Cloud KMS key at any time, by revoking the Identity and Access Management (IAM) permission for that key.
If BigQuery loses access to the Cloud KMS key, the user experience can suffer significantly and data loss can occur:
Data in these CMEK-protected tables can no longer be accessed:
query
,cp
,extract
, andtabledata.list
will all fail.No new data can be added to these CMEK-protected tables.
After access is granted back, the performance of queries to these tables can be degraded for multiple days.
Control CMEK use with organization policy
BigQuery integrates with CMEK organization policy constraints to let you specify encryption compliance requirements for BigQuery resources in your organization.
This integration lets you do the following:
Require CMEKs for all BigQuery resources in a project.
Restrict which Cloud KMS keys can be used to protect resources in a project.
Require CMEKs for all resources
A common policy is to require CMEKs to be used to protect all resources in a
specific set of projects. You can use the
constraints/gcp.restrictNonCmekServices
constraint to enforce this policy in
BigQuery.
If set, this organization policy causes all resource creation requests without a specified Cloud KMS key to fail.
After you set this policy, it applies only to new resources in the project. Any existing resources without Cloud KMS keys set continue to exist and are accessible without issue.
Console
Open the Organization policies page.
In the Filter field, enter
constraints/gcp.restrictNonCmekServices
, and then click Restrict which services may create resources without CMEK.Click
Edit.Select Customize, select Replace, and then click Add Rule.
Select Custom, then click Deny.
In the Custom Value field, enter
is:bigquery.googleapis.com
.Click Done, and then click Save.
gcloud
gcloud resource-manager org-policies --project=PROJECT_ID \ deny gcp.restrictNonCmekServices is:bigquery.googleapis.com
To verify that the policy is successfully applied, you can try to create a table in the project. The process fails unless you specify a Cloud KMS key.
This policy also applies to query results tables in the project. You can specify a project default key so users don't have to manually specify a key each time they execute a query in the project.
Restrict Cloud KMS keys for a BigQuery project
You can use the constraints/gcp.restrictCmekCryptoKeyProjects
constraint to
restrict the Cloud KMS keys that you can use to protect a resource in a
BigQuery project.
You might specify a rule - for example, "For all BigQuery resources in projects/my-company-data-project, Cloud KMS keys used in this project must come from projects/my-company-central-keys OR projects/team-specific-keys."
Console
Open the Organization policies page.
In the Filter field, enter
constraints/gcp.restrictCmekCryptoKeyProjects
, and then click Restrict which projects may supply KMS CryptoKeys for CMEK.Click
Edit.Select Customize, select Replace, and then click Add Rule.
Select Custom, then click Allow.
In the Custom Value field, enter
under:projects/<var>KMS_PROJECT_ID</var>
.Click Done, and then click Save.
gcloud
gcloud resource-manager org-policies --project=PROJECT_ID \ allow gcp.restrictCmekCryptoKeyProjects under:projects/KMS_PROJECT_ID
To verify that the policy is successfully applied, you can try to create a table using a Cloud KMS key from a different project. The process will fail.
Limitations of organization policies
There are limitations associated with setting an organization policy.
Propagation delay
After you set or update an organization policy, it can take up to 15 minutes for the new policy to take effect. BigQuery caches policies in order to not negatively affect query and table creation latency.
Required permissions to set an organization policy
The permission to set or update the organization policy might be difficult to acquire for testing purposes. You must be granted the Organization Policy Administrator role, which can only be granted at the organization level (rather than the project or folder level).
Although the role must be granted at the organization level, it is still possible to specify a policy that only applies to a specific project or folder.
Impact of Cloud KMS key rotation
BigQuery doesn't automatically rotate a table encryption key when the Cloud KMS key associated with the table is rotated. All data in the existing tables continue to be protected by the key version with which they were created.
Any newly-created tables use the primary key version at the time of their creation.
To update a table to use the most recent key version, change the table to a different Cloud KMS key and then back to the original.
Impact on Cloud KMS billing
When you create or truncate a CMEK-protected table, BigQuery generates an intermediate key-encryption key which is then encrypted with the specified Cloud KMS key.
For billing purposes, this means that neither your calls to Cloud KMS
nor their associated costs scale with the table size. For CMEK-protected tables,
You can expect one call to Cloud KMS
cryptoKeys.encrypt
for each table creation or truncation and one call to Cloud KMS
cryptoKeys.decrypt
for each table involved in a query. These methods both belong to the category of
Key operations: Cryptographic listed in
Cloud KMS Pricing.
Either reading from or writing to an existing CMEK-protected table invokes
Cloud KMS cryptoKeys.decrypt
because the intermediate key must be
decrypted.
Limitations
BigQuery access to the Cloud KMS key
A Cloud KMS key is considered available and accessible by BigQuery under the following conditions:
- The key is enabled
- The BigQuery service account has encrypt and decrypt permissions on the key
The following sections describe impact to streaming inserts and long-term inaccessible data when a key is inaccessible.
Impact to streaming inserts
The Cloud KMS key must be available and accessible for at least 24 consecutive hours in the 48-hour period following a streaming insertion request. If the key is not available and accessible, the streamed data might not be fully persisted and can be lost. For more information about streaming inserts, see Streaming data into BigQuery.
Impact to long-term inaccessible data
As BigQuery provides managed storage, long-term inaccessible data is not compatible with BigQuery's architecture. If the Cloud KMS key of a given BigQuery table is not available and not accessible for 60 consecutive days, BigQuery might choose to delete the table and its associated data. At least 7 days before the data is deleted, BigQuery sends an email to the email address associated with the billing account.
Using external data sources
If you are querying data stored in an external data source such as Cloud Storage that has CMEK-encrypted data, then the data encryption is managed by Cloud Storage. For example, BigLake tables support data encrypted with CMEK in Cloud Storage.
BigQuery and BigLake tables don't support Customer-Supplied Encryption Keys (CSEK).
Switching between CMEK-protected and default encryption
You cannot switch a table in place between default encryptions and CMEK
encryption. To switch encryption, copy the
table with destination encryption
information set or use a SELECT *
query to select the table into itself with
WRITE_TRUNCATE
disposition.
Using table decorators
If you protect a table with Cloud KMS and then replace the data in the
table by using the value WRITE_TRUNCATE
for a load
, cp
, or query
operation, then range decorators
don't work across the encryption change boundary. You can still use table
decorators, including range decorators, to query the data before or after the
boundary, or query the snapshot at a point in time.
Wildcard table queries
CMEK-protected tables cannot be queried with a wildcard suffix.
Editions support
CMEK support for BigQuery is only available for BigQuery Enterprise Plus and BigQuery On-Demand. BigQuery customers of legacy flat-rate reservations prior to July 5, 2023, retain all existing support for CMEK in the Enterprise tier.
Frequently asked questions
Who needs permission to the Cloud KMS key?
With customer-managed encryption keys, specifying permissions repeatedly is not required. As long as the BigQuery service account has permission to use the Cloud KMS key to encrypt and decrypt, anyone with permission to the BigQuery table can access the data, even if they don't have direct access to the Cloud KMS key.
Which service account is used?
The BigQuery service account associated with the Google Cloud project of the table is used to decrypt that table's data. The BigQuery service accounts are unique for each project. For a job that writes data into a Cloud KMS-protected anonymous table, the job's project's service account is used.
As an example, consider three CMEK-protected tables: table1
, table2
, and
table3
. To query data from {project1.table1, project2.table2}
with
destination table {project3.table3}
:
- Use the
project1
service account forproject1.table1
- Use the
project2
service account forproject2.table2
- Use the
project3
service account forproject3.table3
In what ways can BigQuery use my Cloud KMS key?
BigQuery uses the Cloud KMS key to decrypt data in
response to a user query, for example,
tabledata.list
or
jobs.insert
.
BigQuery can also use the key for data maintenance and storage optimization tasks, like data conversion into a read-optimized format.
What cryptography libraries are used?
BigQuery relies on Cloud KMS for CMEK functionality. Cloud KMS uses Tink for encryption.
How to get more help?
If you have questions that are not answered here, see BigQuery support.
Troubleshooting errors
The following describes common errors and recommended mitigations.
Error | Recommendation |
---|---|
Please grant Cloud KMS CryptoKey Encrypter/Decrypter role | The BigQuery service account associated with your project doesn't have sufficient IAM permission to operate on the specified Cloud KMS key. Follow the instructions in the error or in this documentation to grant the proper IAM permission. |
Existing table encryption settings do not match encryption settings specified in the request | This can occur in scenarios where the destination table has encryption
settings that don't match the encryption settings in your request. As
mitigation, use write disposition TRUNCATE to replace the table,
or specify a different destination table. |
This region is not supported | The region of the Cloud KMS key does not match the region of the BigQuery dataset of the destination table. As a mitigation, select a key in a region that matches your dataset, or load into a dataset that matches the key region. |
Your administrator requires that you specify an encryption key for queries in project PROJECT_ID. | An organization policy prevented creating a resource or running a query. To learn more about this policy, see Requiring CMEKs for all resources in a BigQuery project. |
Your administrator prevents using KMS keys from project KMS_PROJECT_ID to protect resources in project PROJECT_ID. | An organization policy prevented creating a resource or running a query. To learn more about this policy, see Restrict Cloud KMS keys for a BigQuery project. |