Encryption in BigQuery

BigQuery encrypts all data at rest by default. Customer-managed keys (CMEK) put the key in your Cloud KMS. The AEAD SQL functions encrypt single values, which also gives you crypto-shredding by deleting a key.

BigQuery encrypts everything it stores, with no setting to turn on. There are three levels. Google default encryption needs nothing from you. Customer-managed keys (CMEK) keep the same table-level encryption but put the key-encryption key in your own Cloud KMS, where you control it. AEAD functions are SQL functions that encrypt individual values inside a table, with keys you manage, which is how you encrypt one customer’s data separately and “delete” it by destroying a key. CMEK is a project and table setting. AEAD is a query-level tool.

Default encryption

Google encrypts each table’s data and metadata with AES. Data is encrypted with a data encryption key, and those keys are in turn encrypted by key encryption keys that Google owns and rotates. This is envelope encryption. You see none of it: queries, exports and permissions work as usual.

Customer-managed keys (CMEK)

With CMEK you create the key encryption key in Cloud KMS and tell BigQuery to use it. The data is still encrypted with data keys, but the key that wraps them is yours, and you control its access, rotation schedule and lifecycle. The key can protect:

• a table, through the kms_key_name table option;
• all new tables in a dataset, through default_kms_key_name, which doesn’t change tables that already exist;
• query results, when a destination table is set with a key, or a project default key applies to the temporary result table.

CREATE TABLE sales.orders (id INT64, total NUMERIC)
OPTIONS (kms_key_name = 'projects/key-project/locations/us/keyRings/ring/cryptoKeys/bq-key');

ALTER SCHEMA sales SET OPTIONS (
  default_kms_key_name = 'projects/key-project/locations/us/keyRings/ring/cryptoKeys/bq-key'
);

Statements that depend on a Cloud KMS key you control can’t run in the browser, so they are shown as plain code, and we didn’t run the key lifecycle ourselves. What we ran: a CREATE TABLE pointing at a key that doesn’t exist. BigQuery answered Access Denied: ... Cloud KMS Error: Permission 'cloudkms.cryptoKeyVersions.useToEncrypt' denied on resource ... (or it may not exist). Please grant Cloud KMS CryptoKey Encrypter/Decrypter role to BigQuery service account. The message covers both cases, a key that doesn’t exist and one BigQuery may not use, the same way an error for a missing or forbidden table does.

Setup facts from Google’s guide:

• The BigQuery service account that needs the key is [email protected]. It doesn’t exist until something triggers it, for example bq show --encryption_service_account --project_id=.... It needs Cloud KMS CryptoKey Encrypter/Decrypter on the key.
• The key ring must be in the dataset’s location: a US dataset takes a us key ring, an EU dataset a europe key ring, a regional dataset a key in the same region.
• BigQuery calls Cloud KMS once per query on a CMEK table, and those calls are billed by Cloud KMS.
• Keys are AES-256 key encryption keys. Customer-supplied keys (CSEK) are not supported, and the key path can’t include a /cryptoKeyVersions/ part, because BigQuery always uses the primary version.
• Moving a table between default encryption and CMEK, or to another key, is done with ALTER TABLE ... SET OPTIONS (kms_key_name = ...) or a copy job.
• Cloud KMS Autokey can create the key ring and keys when a resource is created, so a dataset or table doesn’t need a key set up ahead of time.
• External data in Cloud Storage is encrypted by Cloud Storage, not by BigQuery’s CMEK.

CMEK doesn’t change who can read data. Someone with dataViewer queries a CMEK table exactly as before. See access control.

Encrypting single values with AEAD

CMEK protects a table as a whole. To encrypt values inside a table, so that rows from different customers use different keys, use the AEAD functions. AEAD (authenticated encryption with associated data) means two things: the ciphertext can’t be read without the key, and it can’t be altered or moved to another context without detection.

The pieces are:

• KEYS.NEW_KEYSET('AEAD_AES_GCM_256') returns a keyset, a BYTES value that holds one key. 'DETERMINISTIC_AEAD_AES_SIV_CMAC_256' gives a keyset for deterministic encryption. These two are the only key types it accepts: AEAD_AES_CBC fails with must be one of 'AEAD_AES_GCM_256', 'DETERMINISTIC_AEAD_AES_SIV_CMAC_256'.
• AEAD.ENCRYPT(keyset, plaintext, additional_data) returns ciphertext as BYTES.
• AEAD.DECRYPT_STRING(keyset, ciphertext, additional_data) and AEAD.DECRYPT_BYTES reverse it.
• DETERMINISTIC_ENCRYPT and DETERMINISTIC_DECRYPT_STRING do the same with a deterministic keyset.

The simplest check is that a value comes back:

-- A new keyset is random every time, so the example shows only what does not change: decrypt(encrypt(x)) = x.
CREATE TEMP TABLE keysets AS
SELECT KEYS.NEW_KEYSET('AEAD_AES_GCM_256') AS keyset;

SELECT
  '[email protected]' AS original,
  AEAD.DECRYPT_STRING(
    keyset,
    AEAD.ENCRYPT(keyset, '[email protected]', 'customer-17'),
    'customer-17'
  ) AS decrypted
FROM keysets

A keyset is random, so these examples never print ciphertext or key bytes, only properties that hold every run. A keyset has to be stored somewhere to decrypt later; the example holds it in a temp table, and a real table of keysets needs the same protection as the data.

The additional data binds a ciphertext to a context

The third argument is not optional. Decryption must give the same value, and any other value fails:

-- Decrypting with different additional data fails. This is what binds a ciphertext to its row.
CREATE TEMP TABLE keysets AS
SELECT KEYS.NEW_KEYSET('AEAD_AES_GCM_256') AS keyset;

SELECT AEAD.DECRYPT_STRING(
  keyset,
  AEAD.ENCRYPT(keyset, '[email protected]', 'customer-17'),
  'customer-18'
) AS decrypted
FROM keysets

Use something that identifies the row, such as the customer id, so that a ciphertext copied into another row doesn’t decrypt there. plaintext and additional_data must have the same type: AEAD.ENCRYPT(keyset, 'x', b'y') fails with No matching signature, listing (BYTES, STRING, STRING) and (BYTES, BYTES, BYTES). A NULL keyset or plaintext returns NULL, not an error. DETERMINISTIC_ENCRYPT has the same type rule and the same NULL behavior.

Same input, different ciphertext

AEAD.ENCRYPT uses a random IV for each call. Encrypting the same value twice gives two ciphertexts, and both decrypt:

-- AEAD.ENCRYPT picks a new random IV on every call, so the same input never gives the same ciphertext.
CREATE TEMP TABLE keysets AS
SELECT KEYS.NEW_KEYSET('AEAD_AES_GCM_256') AS keyset;

SELECT
  AEAD.ENCRYPT(keyset, 'walrus', 'ctx') = AEAD.ENCRYPT(keyset, 'walrus', 'ctx') AS ciphertexts_equal,
  AEAD.DECRYPT_STRING(keyset, AEAD.ENCRYPT(keyset, 'walrus', 'ctx'), 'ctx') = 'walrus' AS both_decrypt
FROM keysets

That is the secure choice, with a cost: you can’t group, join or deduplicate on the ciphertext. If you need that, use the deterministic functions:

-- DETERMINISTIC_ENCRYPT returns the same ciphertext for the same keyset, input and additional data, so equal values stay equal.
CREATE TEMP TABLE keysets AS
SELECT KEYS.NEW_KEYSET('DETERMINISTIC_AEAD_AES_SIV_CMAC_256') AS keyset;

CREATE TEMP TABLE visits AS
SELECT * FROM UNNEST(['[email protected]', '[email protected]', '[email protected]', '[email protected]', '[email protected]']) AS email;

SELECT
  COUNT(*) AS rows_total,
  COUNT(DISTINCT email) AS distinct_emails,
  COUNT(DISTINCT DETERMINISTIC_ENCRYPT(keyset, email, '')) AS distinct_ciphertexts
FROM visits, keysets

Five rows with two distinct emails give two distinct ciphertexts. That is also what leaks: anyone who sees the column can tell which rows share a value, and how often. Use deterministic encryption only on columns where that is acceptable, and with different additional_data per column so a ciphertext in one column doesn’t match another.

Ciphertext is longer than the input

Storage grows. We encrypted strings of 1, 5 and 16 bytes:

-- Ciphertext is longer than the input by a fixed amount for each function.
CREATE TEMP TABLE keysets AS
SELECT
  KEYS.NEW_KEYSET('AEAD_AES_GCM_256') AS gcm,
  KEYS.NEW_KEYSET('DETERMINISTIC_AEAD_AES_SIV_CMAC_256') AS siv;

SELECT
  LENGTH(s) AS plaintext_bytes,
  LENGTH(AEAD.ENCRYPT(gcm, s, '')) AS aead_bytes,
  LENGTH(DETERMINISTIC_ENCRYPT(siv, s, '')) AS deterministic_bytes
FROM keysets, UNNEST(['a', 'hello', '[email protected]']) AS s
ORDER BY plaintext_bytes

AEAD.ENCRYPT adds 33 bytes to every value and the deterministic function adds 21. For a short column the ciphertext can be many times larger than the plaintext, which matters for a table with billions of small values.

A keyset in a CTE is created again at every use

KEYS.NEW_KEYSET returns a different keyset each time it is evaluated, and a CTE is not a stored result. If you name the keyset in a WITH clause and refer to it twice, you get two keysets:

-- A CTE that calls KEYS.NEW_KEYSET is evaluated at each place it is used, so encrypt and decrypt get two different keys.
WITH k AS (
  SELECT KEYS.NEW_KEYSET('AEAD_AES_GCM_256') AS keyset
)
SELECT AEAD.DECRYPT_STRING(
  (SELECT keyset FROM k),
  AEAD.ENCRYPT((SELECT keyset FROM k), '[email protected]', ''),
  ''
) AS decrypted

We ran the same shape three times and it failed each time. In one test, a keyset produced in a FROM subquery and read twice as a column (FROM (SELECT KEYS.NEW_KEYSET(...) AS k)) did decrypt, but don’t build on that. Write the keyset into a table and read it from there. A temp table is enough inside one script, and it is what the other examples here use.

Crypto-shredding

Crypto-shredding is deleting the key to make the data unreadable. Keep one keyset per customer in a table, encrypt that customer’s values with it, and delete the keyset row when the customer asks to be forgotten. The ciphertext can stay in the table, in backups and in time travel, because it can no longer be decrypted.

-- One keyset per customer. Deleting a customer's keyset makes that customer's ciphertext unreadable; the others still decrypt.
CREATE TEMP TABLE keysets AS
SELECT customer_id, KEYS.NEW_KEYSET('AEAD_AES_GCM_256') AS keyset
FROM UNNEST([1, 2, 3]) AS customer_id;

CREATE TEMP TABLE notes AS
SELECT
  n.customer_id,
  AEAD.ENCRYPT(k.keyset, n.note, CAST(n.customer_id AS STRING)) AS note_enc
FROM UNNEST([STRUCT(1 AS customer_id, 'allergic to nuts' AS note), (2, 'prefers email'), (3, 'VIP')]) AS n
JOIN keysets AS k USING (customer_id);

DELETE FROM keysets WHERE customer_id = 2;

SELECT
  n.customer_id,
  AEAD.DECRYPT_STRING(k.keyset, n.note_enc, CAST(n.customer_id AS STRING)) AS note
FROM notes AS n
LEFT JOIN keysets AS k USING (customer_id)
ORDER BY n.customer_id

After the DELETE, customer 2’s note decrypts to NULL, because the keyset is NULL after the left join and AEAD.DECRYPT_STRING returns NULL for a NULL input. The other customers are unaffected. This only holds if the keyset exists nowhere else: a copy in a snapshot, an export, a log, or the table’s time travel window still decrypts the data. A key deleted from a table is deleted from the present, not from the past, until the time travel window passes. The same mechanism works against you by accident: if every copy of a keyset is lost, the data is gone, so plan how keysets are backed up and restored.

Rotating keys

KEYS.ROTATE_KEYSET(keyset, 'AEAD_AES_GCM_256') adds a new primary key to the keyset and keeps the old one as a secondary key. New encryptions use the new key. Old ciphertext carries the id of the key it was encrypted with, and still decrypts:

-- Rotating adds a new primary key. Old ciphertext still decrypts, new ciphertext uses the new key.
CREATE TEMP TABLE keysets AS
SELECT KEYS.NEW_KEYSET('AEAD_AES_GCM_256') AS keyset;

CREATE TEMP TABLE old_data AS
SELECT AEAD.ENCRYPT(keyset, 'written before rotation', '') AS ciphertext FROM keysets;

CREATE TEMP TABLE rotated AS
SELECT KEYS.ROTATE_KEYSET(keyset, 'AEAD_AES_GCM_256') AS keyset FROM keysets;

SELECT
  (SELECT KEYS.KEYSET_LENGTH(keyset) FROM keysets) AS keys_before,
  (SELECT KEYS.KEYSET_LENGTH(keyset) FROM rotated) AS keys_after,
  (SELECT AEAD.DECRYPT_STRING(r.keyset, o.ciphertext, '') FROM rotated AS r, old_data AS o) AS old_ciphertext_decrypts

Rotation doesn’t change existing ciphertext. To stop using an old key, decrypt and re-encrypt the data with the new primary, then disable or remove the old key. KEYS.KEYSET_LENGTH counts the keys in a keyset.

Wrapping keysets with Cloud KMS

A keyset stored as plain BYTES is only as safe as the table it is in. The wrapped versions encrypt the keyset with a Cloud KMS key: KEYS.NEW_WRAPPED_KEYSET(kms_resource_name, key_type) creates one, and KEYS.KEYSET_CHAIN(kms_resource_name, wrapped_keyset) passes it to the AEAD functions at query time. BigQuery asks Cloud KMS to unwrap the keyset, uses it in memory for that query, and discards it. A user then needs both access to the table and cloudkms permission on the key to see plaintext. These functions can’t run without a KMS key, so they are not shown as runnable.

SELECT AEAD.DECRYPT_STRING(
  KEYS.KEYSET_CHAIN(
    'gcp-kms://projects/key-project/locations/us/keyRings/ring/cryptoKeys/kek',
    wrapped_keyset
  ),
  ciphertext,
  CAST(customer_id AS STRING)
) AS note
FROM crm.customer_notes;

KEYS.KEYSET_TO_JSON returns a keyset as JSON, key data included, so treat its output like the key.

Which one to use

CMEK is for a rule that says you must control the key for the whole table. It needs no change to queries. AEAD is for a rule that some values need their own key, or that a particular customer’s data has to be erasable by key deletion. They stack: a table can use CMEK and also hold AEAD-encrypted columns.

AEAD cuts what you can do with the column. A ciphertext can’t be searched, compared with a range, or aggregated, apart from equality on deterministic values. If a team only needs to see a column in a less sensitive form, column-level security and data masking leave the data usable.