AEAD encryption functions

GoogleSQL for BigQuery supports the following AEAD encryption functions. For a description of how the AEAD encryption functions work, see AEAD encryption concepts.

Function list

Name Summary
AEAD.DECRYPT_BYTES Uses the matching key from a keyset to decrypt a BYTES ciphertext.
AEAD.DECRYPT_STRING Uses the matching key from a keyset to decrypt a BYTES ciphertext into a STRING plaintext.
AEAD.ENCRYPT Encrypts STRING plaintext, using the primary cryptographic key in a keyset.
DETERMINISTIC_DECRYPT_BYTES Uses the matching key from a keyset to decrypt a BYTES ciphertext, using deterministic AEAD.
DETERMINISTIC_DECRYPT_STRING Uses the matching key from a keyset to decrypt a BYTES ciphertext into a STRING plaintext, using deterministic AEAD.
DETERMINISTIC_ENCRYPT Encrypts STRING plaintext, using the primary cryptographic key in a keyset, using deterministic AEAD encryption.
KEYS.ADD_KEY_FROM_RAW_BYTES Adds a key to a keyset, and return the new keyset as a serialized BYTES value.
KEYS.KEYSET_CHAIN Produces a Tink keyset that's encrypted with a Cloud KMS key.
KEYS.KEYSET_FROM_JSON Converts a STRING JSON keyset to a serialized BYTES value.
KEYS.KEYSET_LENGTH Gets the number of keys in the provided keyset.
KEYS.KEYSET_TO_JSON Gets a JSON STRING representation of a keyset.
KEYS.NEW_KEYSET Gets a serialized keyset containing a new key based on the key type.
KEYS.NEW_WRAPPED_KEYSET Creates a new keyset and encrypts it with a Cloud KMS key.
KEYS.REWRAP_KEYSET Re-encrypts a wrapped keyset with a new Cloud KMS key.
KEYS.ROTATE_KEYSET Adds a new primary cryptographic key to a keyset, based on the key type.
KEYS.ROTATE_WRAPPED_KEYSET Rewraps a keyset and rotates it.

AEAD.DECRYPT_BYTES

AEAD.DECRYPT_BYTES(keyset, ciphertext, additional_data)

Description

Uses the matching key from keyset to decrypt ciphertext and verifies the integrity of the data using additional_data. Returns an error if decryption or verification fails.

keyset is a serialized BYTES value returned by one of the KEYS functions or a STRUCT returned by KEYS.KEYSET_CHAIN. keyset must contain the key that was used to encrypt ciphertext, and the key must be in an 'ENABLED' state, or else the function returns an error. AEAD.DECRYPT_BYTES identifies the matching key in keyset by finding the key with the key ID that matches the one encrypted in ciphertext.

ciphertext is a BYTES value that's the result of a call to AEAD.ENCRYPT where the input plaintext was of type BYTES.

If ciphertext includes an initialization vector (IV), it should be the first bytes of ciphertext. If ciphertext includes an authentication tag, it should be the last bytes of ciphertext. If the IV and authentic tag are one (SIV), it should be the first bytes of ciphertext. The IV and authentication tag commonly require 16 bytes, but may vary in size.

additional_data is a STRING or BYTES value that binds the ciphertext to its context. This forces the ciphertext to be decrypted in the same context in which it was encrypted. This function casts any STRING value to BYTES. This must be the same as the additional_data provided to AEAD.ENCRYPT to encrypt ciphertext, ignoring its type, or else the function returns an error.

Return Data Type

BYTES

Example

This example creates a table of unique IDs with associated plaintext values and keysets. Then it uses these keysets to encrypt the plaintext values as BYTES and store them in a new table. Finally, it uses AEAD.DECRYPT_BYTES to decrypt the encrypted values and display them as plaintext.

The following statement creates a table CustomerKeysets containing a column of unique IDs, a column of AEAD_AES_GCM_256 keysets, and a column of favorite animals.

CREATE TABLE aead.CustomerKeysets AS SELECT   1 AS customer_id,   KEYS.NEW_KEYSET('AEAD_AES_GCM_256') AS keyset,   b'jaguar' AS favorite_animal UNION ALL SELECT   2 AS customer_id,   KEYS.NEW_KEYSET('AEAD_AES_GCM_256') AS keyset,   b'zebra' AS favorite_animal UNION ALL SELECT   3 AS customer_id,   KEYS.NEW_KEYSET('AEAD_AES_GCM_256') AS keyset,   b'nautilus' AS favorite_animal;

The following statement creates a table EncryptedCustomerData containing a column of unique IDs and a column of ciphertext. The statement encrypts the plaintext favorite_animal using the keyset value from CustomerKeysets corresponding to each unique ID.

CREATE TABLE aead.EncryptedCustomerData AS SELECT   customer_id,   AEAD.ENCRYPT(keyset, favorite_animal, CAST(CAST(customer_id AS STRING) AS BYTES))    AS encrypted_animal FROM   aead.CustomerKeysets AS ck;

The following query uses the keysets in the CustomerKeysets table to decrypt data in the EncryptedCustomerData table.

SELECT   ecd.customer_id,   AEAD.DECRYPT_BYTES(     (SELECT ck.keyset      FROM aead.CustomerKeysets AS ck      WHERE ecd.customer_id = ck.customer_id),     ecd.encrypted_animal,     CAST(CAST(customer_id AS STRING) AS BYTES)   ) AS favorite_animal FROM aead.EncryptedCustomerData AS ecd;

AEAD.DECRYPT_STRING

AEAD.DECRYPT_STRING(keyset, ciphertext, additional_data)

Description

Like AEAD.DECRYPT_BYTES, but where additional_data is of type STRING.

Return Data Type

STRING

AEAD.ENCRYPT

AEAD.ENCRYPT(keyset, plaintext, additional_data)

Description

Encrypts plaintext using the primary cryptographic key in keyset. The algorithm of the primary key must be AEAD_AES_GCM_256. Binds the ciphertext to the context defined by additional_data. Returns NULL if any input is NULL.

keyset is a serialized BYTES value returned by one of the KEYS functions or a STRUCT returned by KEYS.KEYSET_CHAIN.

plaintext is the STRING or BYTES value to be encrypted.

additional_data is a STRING or BYTES value that binds the ciphertext to its context. This forces the ciphertext to be decrypted in the same context in which it was encrypted. plaintext and additional_data must be of the same type. AEAD.ENCRYPT(keyset, string1, string2) is equivalent to AEAD.ENCRYPT(keyset, CAST(string1 AS BYTES), CAST(string2 AS BYTES)).

The output is ciphertext BYTES. The ciphertext contains a Tink-specific prefix indicating the key used to perform the encryption.

Return Data Type

BYTES

Example

The following query uses the keysets for each customer_id in the CustomerKeysets table to encrypt the value of the plaintext favorite_animal in the PlaintextCustomerData table corresponding to that customer_id. The output contains a column of customer_id values and a column of corresponding ciphertext output as BYTES.

WITH CustomerKeysets AS (   SELECT 1 AS customer_id, KEYS.NEW_KEYSET('AEAD_AES_GCM_256') AS keyset UNION ALL   SELECT 2, KEYS.NEW_KEYSET('AEAD_AES_GCM_256') UNION ALL   SELECT 3, KEYS.NEW_KEYSET('AEAD_AES_GCM_256') ), PlaintextCustomerData AS (   SELECT 1 AS customer_id, 'elephant' AS favorite_animal UNION ALL   SELECT 2, 'walrus' UNION ALL   SELECT 3, 'leopard' ) SELECT   pcd.customer_id,   AEAD.ENCRYPT(     (SELECT keyset      FROM CustomerKeysets AS ck      WHERE ck.customer_id = pcd.customer_id),     pcd.favorite_animal,     CAST(pcd.customer_id AS STRING)   ) AS encrypted_animal FROM PlaintextCustomerData AS pcd;

DETERMINISTIC_DECRYPT_BYTES

DETERMINISTIC_DECRYPT_BYTES(keyset, ciphertext, additional_data)

Description

Uses the matching key from keyset to decrypt ciphertext and verifies the integrity of the data using additional_data. Returns an error if decryption fails.

keyset is a serialized BYTES value or a STRUCT value returned by one of the KEYS functions. keyset must contain the key that was used to encrypt ciphertext, the key must be in an 'ENABLED' state, and the key must be of type DETERMINISTIC_AEAD_AES_SIV_CMAC_256, or else the function returns an error. DETERMINISTIC_DECRYPT_BYTES identifies the matching key in keyset by finding the key with the key ID that matches the one encrypted in ciphertext.

ciphertext is a BYTES value that's the result of a call to DETERMINISTIC_ENCRYPT where the input plaintext was of type BYTES.

The ciphertext must follow Tink's wire format. The first byte of ciphertext should contain a Tink key version followed by a 4 byte key hint. If ciphertext includes an initialization vector (IV), it should be the next bytes of ciphertext. If ciphertext includes an authentication tag, it should be the last bytes of ciphertext. If the IV and authentic tag are one (SIV), it should be the first bytes of ciphertext. The IV and authentication tag commonly require 16 bytes, but may vary in size.

additional_data is a STRING or BYTES value that binds the ciphertext to its context. This forces the ciphertext to be decrypted in the same context in which it was encrypted. This function casts any STRING value to BYTES. This must be the same as the additional_data provided to DETERMINISTIC_ENCRYPT to encrypt ciphertext, ignoring its type, or else the function returns an error.

Return Data Type

BYTES

Example

This example creates a table of unique IDs with associated plaintext values and keysets. Then it uses these keysets to encrypt the plaintext values as BYTES and store them in a new table. Finally, it uses DETERMINISTIC_DECRYPT_BYTES to decrypt the encrypted values and display them as plaintext.

The following statement creates a table CustomerKeysets containing a column of unique IDs, a column of DETERMINISTIC_AEAD_AES_SIV_CMAC_256 keysets, and a column of favorite animals.

CREATE TABLE deterministic.CustomerKeysets AS SELECT   1 AS customer_id,   KEYS.NEW_KEYSET('DETERMINISTIC_AEAD_AES_SIV_CMAC_256') AS keyset,   b'jaguar' AS favorite_animal UNION ALL SELECT   2 AS customer_id,   KEYS.NEW_KEYSET('DETERMINISTIC_AEAD_AES_SIV_CMAC_256') AS keyset,   b'zebra' AS favorite_animal UNION ALL SELECT   3 AS customer_id,   KEYS.NEW_KEYSET('DETERMINISTIC_AEAD_AES_SIV_CMAC_256') AS keyset,   b'nautilus' AS favorite_animal;

The following statement creates a table EncryptedCustomerData containing a column of unique IDs and a column of ciphertext. The statement encrypts the plaintext favorite_animal using the keyset value from CustomerKeysets corresponding to each unique ID.

CREATE TABLE deterministic.EncryptedCustomerData AS SELECT   customer_id,   DETERMINISTIC_ENCRYPT(ck.keyset, favorite_animal, CAST(CAST(customer_id AS STRING) AS BYTES))    AS encrypted_animal FROM   deterministic.CustomerKeysets AS ck;

The following query uses the keysets in the CustomerKeysets table to decrypt data in the EncryptedCustomerData table.

SELECT   ecd.customer_id,   DETERMINISTIC_DECRYPT_BYTES(     (SELECT ck.keyset      FROM deterministic.CustomerKeysets AS ck      WHERE ecd.customer_id = ck.customer_id),     ecd.encrypted_animal,     CAST(CAST(ecd.customer_id AS STRING) AS BYTES)   ) AS favorite_animal FROM deterministic.EncryptedCustomerData AS ecd;

DETERMINISTIC_DECRYPT_STRING

DETERMINISTIC_DECRYPT_STRING(keyset, ciphertext, additional_data)

Description

Like DETERMINISTIC_DECRYPT_BYTES, but where plaintext is of type STRING.

Return Data Type

STRING

DETERMINISTIC_ENCRYPT

DETERMINISTIC_ENCRYPT(keyset, plaintext, additional_data)

Description

Encrypts plaintext using the primary cryptographic key in keyset using deterministic AEAD. The algorithm of the primary key must be DETERMINISTIC_AEAD_AES_SIV_CMAC_256. Binds the ciphertext to the context defined by additional_data. Returns NULL if any input is NULL.

keyset is a serialized BYTES value or a STRUCT value returned by one of the KEYS functions.

plaintext is the STRING or BYTES value to be encrypted.

additional_data is a STRING or BYTES value that binds the ciphertext to its context. This forces the ciphertext to be decrypted in the same context in which it was encrypted. plaintext and additional_data must be of the same type. DETERMINISTIC_ENCRYPT(keyset, string1, string2) is equivalent to DETERMINISTIC_ENCRYPT(keyset, CAST(string1 AS BYTES), CAST(string2 AS BYTES)).

The output is ciphertext BYTES. The ciphertext contains a Tink-specific prefix indicating the key used to perform the encryption. Given an identical keyset and plaintext, this function returns the same ciphertext each time it's invoked (including across queries).

Return Data Type

BYTES

Example

The following query uses the keysets for each customer_id in the CustomerKeysets table to encrypt the value of the plaintext favorite_animal in the PlaintextCustomerData table corresponding to that customer_id. The output contains a column of customer_id values and a column of corresponding ciphertext output as BYTES.

WITH CustomerKeysets AS (   SELECT 1 AS customer_id,   KEYS.NEW_KEYSET('DETERMINISTIC_AEAD_AES_SIV_CMAC_256') AS keyset UNION ALL   SELECT 2, KEYS.NEW_KEYSET('DETERMINISTIC_AEAD_AES_SIV_CMAC_256') UNION ALL   SELECT 3, KEYS.NEW_KEYSET('DETERMINISTIC_AEAD_AES_SIV_CMAC_256') ), PlaintextCustomerData AS (   SELECT 1 AS customer_id, 'elephant' AS favorite_animal UNION ALL   SELECT 2, 'walrus' UNION ALL   SELECT 3, 'leopard' ) SELECT   pcd.customer_id,   DETERMINISTIC_ENCRYPT(     (SELECT keyset      FROM CustomerKeysets AS ck      WHERE ck.customer_id = pcd.customer_id),     pcd.favorite_animal,     CAST(pcd.customer_id AS STRING)   ) AS encrypted_animal FROM PlaintextCustomerData AS pcd;

KEYS.ADD_KEY_FROM_RAW_BYTES

KEYS.ADD_KEY_FROM_RAW_BYTES(keyset, key_type, raw_key_bytes)

Description

Returns a serialized keyset as BYTES with the addition of a key to keyset based on key_type and raw_key_bytes.

The primary cryptographic key remains the same as in keyset. The expected length of raw_key_bytes depends on the value of key_type. The following are supported key_types:

  • 'AES_CBC_PKCS': Creates a key for AES decryption using cipher block chaining and PKCS padding. raw_key_bytes is expected to be a raw key BYTES value of length 16, 24, or 32; these lengths have sizes of 128, 192, and 256 bits, respectively. GoogleSQL AEAD functions don't support keys of these types for encryption; instead, prefer 'AEAD_AES_GCM_256' or 'AES_GCM' keys.
  • 'AES_GCM': Creates a key for AES decryption or encryption using Galois/Counter Mode. raw_key_bytes must be a raw key BYTES value of length 16 or 32; these lengths have sizes of 128 and 256 bits, respectively. When keys of this type are inputs to AEAD.ENCRYPT, the output ciphertext doesn't have a Tink-specific prefix indicating which key was used as input.

Return Data Type

BYTES

Example

The following query creates a table of customer IDs along with raw key bytes, called CustomerRawKeys, and a table of unique IDs, called CustomerIds. It creates a new 'AEAD_AES_GCM_256' keyset for each customer_id; then it adds a new key to each keyset, using the raw_key_bytes value corresponding to that customer_id. The output is a table where each row contains a customer_id and a keyset in BYTES, which contains the raw key added using KEYS.ADD_KEY_FROM_RAW_BYTES.

WITH CustomerRawKeys AS (   SELECT 1 AS customer_id, b'0123456789012345' AS raw_key_bytes UNION ALL   SELECT 2, b'9876543210543210' UNION ALL   SELECT 3, b'0123012301230123' ), CustomerIds AS (   SELECT 1 AS customer_id UNION ALL   SELECT 2 UNION ALL   SELECT 3 ) SELECT   ci.customer_id,   KEYS.ADD_KEY_FROM_RAW_BYTES(     KEYS.NEW_KEYSET('AEAD_AES_GCM_256'),     'AES_CBC_PKCS',     (SELECT raw_key_bytes FROM CustomerRawKeys AS crk      WHERE crk.customer_id = ci.customer_id)   ) AS keyset FROM CustomerIds AS ci;

The output keysets each contain two things: the primary cryptographic key created using KEYS.NEW_KEYSET('AEAD_AES_GCM_256'), and the raw key added using KEYS.ADD_KEY_FROM_RAW_BYTES. If a keyset in the output is used with AEAD.ENCRYPT, GoogleSQL uses the primary cryptographic key created using KEYS.NEW_KEYSET('AEAD_AES_GCM_256') to encrypt the input plaintext. If the keyset is used with AEAD.DECRYPT_STRING or AEAD.DECRYPT_BYTES, GoogleSQL returns the resulting plaintext if either key succeeds in decrypting the ciphertext.

KEYS.KEYSET_CHAIN

KEYS.KEYSET_CHAIN(kms_resource_name, first_level_keyset)

Description

Can be used in place of the keyset argument to the AEAD and deterministic encryption functions to pass a Tink keyset that's encrypted with a Cloud KMS key. This function lets you use other AEAD functions without including plaintext keys in a query.

This function takes the following arguments:

  • kms_resource_name: A STRING literal that contains the resource path to the Cloud KMS key that's used to decrypt first_level_keyset. This key must reside in the same Cloud region where this function is executed. A Cloud KMS key looks like this:

    gcp-kms://projects/my-project/locations/us/keyRings/my-key-ring/cryptoKeys/my-crypto-key

  • first_level_keyset: A BYTES literal that represents a keyset or wrapped keyset.

Return Data Type

STRUCT

Example

This example creates a table of example data, then shows how to encrypt that data using a wrapped (encrypted) keyset. Finally it shows how to query the encrypted version of the data.

The following statement creates a table RawCustomerData containing a column of customer ids and a column of favorite animals.

CREATE TABLE aead.RawCustomerData AS SELECT   1 AS customer_id,   b'jaguar' AS favorite_animal UNION ALL SELECT   2 AS customer_id,   b'zebra' AS favorite_animal UNION ALL SELECT   3 AS customer_id,   b'zebra' AS favorite_animal;

The following statement creates a table EncryptedCustomerData containing a column of unique IDs and a column of ciphertext. The statement encrypts the plaintext favorite_animal using the first_level_keyset provided.

DECLARE kms_resource_name STRING; DECLARE first_level_keyset BYTES; SET kms_resource_name = 'gcp-kms://projects/my-project/locations/us/keyRings/my-key-ring/cryptoKeys/my-crypto-key'; SET first_level_keyset = b'\012\044\000\107\275\360\176\264\206\332\235\215\304...';  CREATE TABLE aead.EncryptedCustomerData AS SELECT   customer_id,   AEAD.ENCRYPT(     KEYS.KEYSET_CHAIN(kms_resource_name, first_level_keyset),     favorite_animal,     CAST(CAST(customer_id AS STRING) AS BYTES)   ) AS encrypted_animal FROM   aead.RawCustomerData;

The following query uses the first_level_keyset to decrypt data in the EncryptedCustomerData table.

DECLARE kms_resource_name STRING; DECLARE first_level_keyset BYTES; SET kms_resource_name = 'gcp-kms://projects/my-project/locations/us/keyRings/my-key-ring/cryptoKeys/my-crypto-key'; SET first_level_keyset = b'\012\044\000\107\275\360\176\264\206\332\235\215\304...';  SELECT   customer_id,   AEAD.DECRYPT_BYTES(     KEYS.KEYSET_CHAIN(kms_resource_name, first_level_keyset),     encrypted_animal,     CAST(CAST(customer_id AS STRING) AS BYTES)   ) AS favorite_animal FROM   aead.EncryptedCustomerData;

The previous two steps also work with the DETERMINISTIC_ENCRYPT and DETERMINISTIC_DECRYPT_BYTES functions. The wrapped keyset must be created using the DETERMINISTIC_AEAD_AES_SIV_CMAC_256 type.

The following statement creates a table EncryptedCustomerData containing a column of unique IDs and a column of ciphertext. The statement encrypts the plaintext favorite_animal using the first_level_keyset provided. You can see that the ciphertext for favorite_animal is the same for customers 2 and 3 since their plaintext favorite_animal is the same.

DECLARE kms_resource_name STRING; DECLARE first_level_keyset BYTES; SET kms_resource_name = 'gcp-kms://projects/my-project/locations/us/keyRings/my-key-ring/cryptoKeys/my-crypto-key'; SET first_level_keyset = b'\012\044\000\107\275\360\176\264\206\332\235\215\304...';  CREATE TABLE daead.EncryptedCustomerData AS SELECT   customer_id,   DETERMINISTC_ENCRYPT(     KEYS.KEYSET_CHAIN(kms_resource_name, first_level_keyset),     favorite_animal,     CAST(CAST(customer_id AS STRING) AS BYTES)   ) AS encrypted_animal FROM   daead.RawCustomerData;

The following query uses the first_level_keyset to decrypt data in the EncryptedCustomerData table.

DECLARE kms_resource_name STRING; DECLARE first_level_keyset BYTES; SET kms_resource_name = 'gcp-kms://projects/my-project/locations/us/keyRings/my-key-ring/cryptoKeys/my-crypto-key'; SET first_level_keyset = b'\012\044\000\107\275\360\176\264\206\332\235\215\304...';  SELECT   customer_id,   DETERMINISTIC_DECRYPT_BYTES(     KEYS.KEYSET_CHAIN(kms_resource_name, first_level_keyset),     encrypted_animal,     CAST(CAST(customer_id AS STRING) AS BYTES)   ) AS favorite_animal FROM dead.EncryptedCustomerData;

KEYS.KEYSET_FROM_JSON

KEYS.KEYSET_FROM_JSON(json_keyset)

Description

Returns the input json_keyset STRING as serialized BYTES, which is a valid input for other KEYS and AEAD functions. The JSON STRING must be compatible with the definition of the google.crypto.tink.Keyset protocol buffer message: the JSON keyset should be a JSON object containing objects and name-value pairs corresponding to those in the "keyset" message in the google.crypto.tink.Keyset definition. You can convert the output serialized BYTES representation back to a JSON STRING using KEYS.KEYSET_TO_JSON.

Return Data Type

BYTES

Example

KEYS.KEYSET_FROM_JSON takes JSON-formatted STRING values like the following:

{   "key":[       {         "keyData":{           "keyMaterialType":"SYMMETRIC",           "typeUrl":"type.googleapis.com/google.crypto.tink.AesGcmKey",           "value":"GiD80Z8kL6AP3iSNHhqseZGAIvq7TVQzClT7FQy8YwK3OQ=="         },         "keyId":3101427138,         "outputPrefixType":"TINK",         "status":"ENABLED"       }     ],   "primaryKeyId":3101427138 }

The following query creates a new keyset from a JSON-formatted STRING json_keyset:

SELECT KEYS.KEYSET_FROM_JSON(json_keyset);

This returns the json_keyset serialized as BYTES, like the following:

\x08\x9d\x8e\x85\x82\x09\x12d\x0aX\x0a0 type.googleapis.com/google.crypto.tink.AesGcmKey\x12\"\x1a qX\xe4IG\x87\x1f\xde \xe3)+e\x98\x0a\x1c}\xfe\x88<\x12\xeb\xc1t\xb8\x83\x1a\xcd\xa8\x97\x84g\x18\x01 \x10\x01\x18\x9d\x8e\x85\x82\x09 \x01

KEYS.KEYSET_LENGTH

KEYS.KEYSET_LENGTH(keyset)

Description

Returns the number of keys in the provided keyset.

Return Data Type

INT64

Example

This example references a JSON-formatted STRING called json_keyset that contains two keys:

{    "primaryKeyId":1354994251,    "key":[       {          "keyData":{             "keyMaterialType":"SYMMETRIC",             "typeUrl":"type.googleapis.com/google.crypto.tink.AesGcmKey",             "value":"GiD9sxQRgFj4aYN78vaIlxInjZkG/uvyWSY9a8GN+ELV2Q=="          },          "keyId":1354994251,          "outputPrefixType":"TINK",          "status":"ENABLED"       }    ],    "key":[       {          "keyData":{             "keyMaterialType":"SYMMETRIC",             "typeUrl":"type.googleapis.com/google.crypto.tink.AesGcmKey",             "value":"PRn76sxQRgFj4aYN00vaIlxInjZkG/uvyWSY9a2bLRm"          },          "keyId":852264701,          "outputPrefixType":"TINK",          "status":"DISABLED"       }    ] }

The following query converts json_keyset to a keyset and then returns the number of keys in the keyset:

SELECT KEYS.KEYSET_LENGTH(KEYS.KEYSET_FROM_JSON(json_keyset)) as key_count;  /*-----------*  | key_count |  +-----------+  | 2         |  *-----------*/

KEYS.KEYSET_TO_JSON

KEYS.KEYSET_TO_JSON(keyset)

Description

Returns a JSON STRING representation of the input keyset. The returned JSON STRING is compatible with the definition of the google.crypto.tink.Keyset protocol buffer message. You can convert the JSON STRING representation back to BYTES using KEYS.KEYSET_FROM_JSON.

Return Data Type

STRING

Example

The following query returns a new 'AEAD_AES_GCM_256' keyset as a JSON-formatted STRING.

SELECT KEYS.KEYSET_TO_JSON(KEYS.NEW_KEYSET('AEAD_AES_GCM_256'));

The result is a STRING like the following.

{   "key":[       {         "keyData":{           "keyMaterialType":"SYMMETRIC",           "typeUrl":"type.googleapis.com/google.crypto.tink.AesGcmKey",           "value":"GiD80Z8kL6AP3iSNHhqseZGAIvq7TVQzClT7FQy8YwK3OQ=="         },         "keyId":3101427138,         "outputPrefixType":"TINK",         "status":"ENABLED"       }     ],   "primaryKeyId":3101427138 }

KEYS.NEW_KEYSET

KEYS.NEW_KEYSET(key_type)

Description

Returns a serialized keyset containing a new key based on key_type. The returned keyset is a serialized BYTES representation of google.crypto.tink.Keyset that contains a primary cryptographic key and no additional keys. You can use the keyset with the AEAD.ENCRYPT, AEAD.DECRYPT_BYTES, and AEAD.DECRYPT_STRING functions for encryption and decryption, as well as with the KEYS group of key- and keyset-related functions.

key_type is a STRING literal representation of the type of key to create. key_type can't be NULL. key_type can be:

  • AEAD_AES_GCM_256: Creates a 256-bit key with the pseudo-random number generator provided by boringSSL. The key uses AES-GCM for encryption and decryption operations.
  • DETERMINISTIC_AEAD_AES_SIV_CMAC_256: Creates a 512-bit AES-SIV-CMAC key, which contains a 256-bit AES-CTR key and 256-bit AES-CMAC key. The AES-SIV-CMAC key is created with the pseudo-random number generator provided by boringSSL. The key uses AES-SIV for encryption and decryption operations.

Return Data Type

BYTES

Example

The following query creates a keyset for each row in CustomerIds, which can subsequently be used to encrypt data. Each keyset contains a single encryption key with randomly-generated key data. Each row in the output contains a customer_id and an 'AEAD_AES_GCM_256' key in BYTES.

SELECT customer_id, KEYS.NEW_KEYSET('AEAD_AES_GCM_256') AS keyset FROM (   SELECT 1 AS customer_id UNION ALL   SELECT 2 UNION ALL   SELECT 3 ) AS CustomerIds;

KEYS.NEW_WRAPPED_KEYSET

KEYS.NEW_WRAPPED_KEYSET(kms_resource_name, key_type)

Description

Creates a new keyset and encrypts it with a Cloud KMS key. Returns the wrapped keyset as a BYTES representation of google.crypto.tink.Keyset that contains a primary cryptographic key and no additional keys.

This function takes the following arguments:

  • kms_resource_name: A STRING literal representation of the Cloud KMS key. kms_resource_name can't be NULL. The Cloud KMS key must reside in the same Cloud region where this function is executed. A Cloud KMS key looks like this:

    gcp-kms://projects/my-project/locations/us/keyRings/my-key-ring/cryptoKeys/my-crypto-key

  • key_type: A STRING literal representation of the keyset type. key_type can't be NULL but can be one of the following values:

    • AEAD_AES_GCM_256: Creates a 256-bit key with the pseudo-random number generator provided by boringSSL. The key uses AES-GCM for encryption and decryption operations.

    • DETERMINISTIC_AEAD_AES_SIV_CMAC_256: Creates a 512-bit AES-SIV-CMAC key, which contains a 256-bit AES-CTR key and 256-bit AES-CMAC key. The AES-SIV-CMAC key is created with the pseudo-random number generator provided by boringSSL. The key uses AES-SIV for encryption and decryption operations.

Return Data Type

BYTES

Example

Put the following variables above each example query that you run:

DECLARE kms_resource_name STRING; SET kms_resource_name = 'gcp-kms://projects/my-project/locations/us/keyRings/my-key-ring/cryptoKeys/my-crypto-key';

The following query creates a wrapped keyset, which contains the ciphertext produced by encrypting a Tink keyset with the specified Cloud KMS key. If you run the query multiple times, it generates multiple wrapped keysets, and each wrapped keyset is unique to each query that's run.

SELECT KEYS.NEW_WRAPPED_KEYSET(kms_resource_name, 'AEAD_AES_GCM_256');

Multiple calls to this function with the same arguments in one query returns the same value. For example, the following query only creates one wrapped keyset and returns it for each row in a table called my_table.

SELECT   *,   KEYS.NEW_WRAPPED_KEYSET(kms_resource_name, 'AEAD_AES_GCM_256') FROM my_table

KEYS.REWRAP_KEYSET

KEYS.REWRAP_KEYSET(source_kms_resource_name, target_kms_resource_name, wrapped_keyset)

Description

Re-encrypts a wrapped keyset with a new Cloud KMS key. Returns the wrapped keyset as a BYTES representation of google.crypto.tink.Keyset that contains a primary cryptographic key and no additional keys.

When this function is used, a wrapped keyset is decrypted by source_kms_resource_name and then re-encrypted by target_kms_resource_name. During this process, the decrypted keyset is never visible to customers.

This function takes the following arguments:

  • source_kms_resource_name: A STRING literal representation of the Cloud KMS key you want to replace. This key must reside in the same Cloud region where this function is executed. A Cloud KMS key looks like this:

    gcp-kms://projects/my-project/locations/us/keyRings/my-key-ring/cryptoKeys/my-crypto-key

  • target_kms_resource_name: A STRING literal representation of the new Cloud KMS key that you want to use.

  • wrapped_keyset: A BYTES literal representation of the keyset that you want to re-encrypt.

Return Data Type

BYTES

Example

Put the following variables above each example query that you run:

DECLARE source_kms_resource_name STRING; DECLARE target_kms_resource_name STRING; DECLARE wrapped_keyset BYTES; SET source_kms_resource_name = 'gcp-kms://projects/my-project/locations/us/keyRings/my-key-ring/cryptoKeys/my-crypto-key'; SET target_kms_resource_name = 'gcp-kms://projects/my-project/locations/another-location/keyRings/my-key-ring/cryptoKeys/my-other-crypto-key'; SET wrapped_keyset = b'\012\044\000\107\275\360\176\264\206\332\235\215\304...';

The following query rewraps a wrapped keyset. If you run the query multiple times, it generates multiple wrapped keysets, and each wrapped keyset is unique to each query that's run.

SELECT KEYS.REWRAP_KEYSET(source_kms_resource_name, target_kms_resource_name, wrapped_keyset);

Multiple calls to this function with the same arguments in one query returns the same value. For example, the following query only creates one wrapped keyset and returns it for each row in a table called my_table.

SELECT   *,   KEYS.REWRAP_KEYSET(source_kms_resource_name, target_kms_resource_name, wrapped_keyset) FROM my_table

KEYS.ROTATE_KEYSET

KEYS.ROTATE_KEYSET(keyset, key_type)

Description

Adds a new key to keyset based on key_type. This new key becomes the primary cryptographic key of the new keyset. Returns the new keyset serialized as BYTES.

The old primary cryptographic key from the input keyset remains an additional key in the returned keyset.

The new key_type must match the key type of existing keys in the keyset.

Return Data Type

BYTES

Example

The following statement creates a table containing a column of unique customer_id values and 'AEAD_AES_GCM_256' keysets. Then, it creates a new primary cryptographic key within each keyset in the source table using KEYS.ROTATE_KEYSET. Each row in the output contains a customer_id and an 'AEAD_AES_GCM_256' keyset in BYTES.

WITH ExistingKeysets AS ( SELECT 1 AS customer_id, KEYS.NEW_KEYSET('AEAD_AES_GCM_256') AS keyset     UNION ALL   SELECT 2, KEYS.NEW_KEYSET('AEAD_AES_GCM_256') UNION ALL   SELECT 3, KEYS.NEW_KEYSET('AEAD_AES_GCM_256') ) SELECT customer_id, KEYS.ROTATE_KEYSET(keyset, 'AEAD_AES_GCM_256') AS keyset FROM ExistingKeysets;

KEYS.ROTATE_WRAPPED_KEYSET

KEYS.ROTATE_WRAPPED_KEYSET(kms_resource_name, wrapped_keyset, key_type)

Description

Takes an existing wrapped keyset and returns a rotated and rewrapped keyset. The returned wrapped keyset is a BYTES representation of google.crypto.tink.Keyset.

When this function is used, the wrapped keyset is decrypted, the new key is added, and then the keyset is re-encrypted. The primary cryptographic key from the input wrapped_keyset remains as an additional key in the returned keyset. During this rotation process, the decrypted keyset is never visible to customers.

This function takes the following arguments:

  • kms_resource_name: A STRING literal representation of the Cloud KMS key that was used to wrap the wrapped keyset. The Cloud KMS key must reside in the same Cloud region where this function is executed. A Cloud KMS key looks like this:

    gcp-kms://projects/my-project/locations/us/keyRings/my-key-ring/cryptoKeys/my-crypto-key

  • wrapped_keyset: A BYTES literal representation of the existing keyset that you want to work with.

  • key_type: A STRING literal representation of the keyset type. This must match the key type of existing keys in wrapped_keyset.

Return Data Type

BYTES

Example

Put the following variables above each example query that you run:

DECLARE kms_resource_name STRING; DECLARE wrapped_keyset BYTES; SET kms_resource_name = 'gcp-kms://projects/my-project/locations/us/keyRings/my-key-ring/cryptoKeys/my-crypto-key'; SET wrapped_keyset = b'\012\044\000\107\275\360\176\264\206\332\235\215\304...';

The following query rotates a wrapped keyset. If you run the query multiple times, it generates multiple wrapped keysets, and each wrapped keyset is unique to each query that's run.

SELECT KEYS.ROTATE_WRAPPED_KEYSET(kms_resource_name, wrapped_keyset, 'AEAD_AES_GCM_256');

Multiple calls to this function with the same arguments in one query returns the same value. For example, the following query only creates one wrapped keyset and returns it for each row in a table called my_table.

SELECT   *,   KEYS.ROTATE_WRAPPED_KEYSET(kms_resource_name, wrapped_keyset, 'AEAD_AES_GCM_256') FROM my_table