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

Function list

AEAD.DECRYPT_BYTES

AEAD.DECRYPT_BYTES(keyset,ciphertext,additional_data)

Description

Uses the matching key fromkeyset to decryptciphertext and verifies theintegrity of the data usingadditional_data. Returns an error if decryption orverification fails.

keyset is a serializedBYTES value returned by one of theKEYS functions or aSTRUCT returned byKEYS.KEYSET_CHAIN.keyset must contain the key that was used toencryptciphertext, and the key must be in an'ENABLED' state, or else thefunction returns an error.AEAD.DECRYPT_BYTES identifies the matching keyinkeyset by finding the key with the key ID that matches the one encrypted inciphertext.

ciphertext is aBYTES value that's the result ofa call toAEAD.ENCRYPT where the inputplaintext was of typeBYTES.

Ifciphertext includes an initialization vector (IV),it should be the first bytes ofciphertext. Ifciphertext includes anauthentication tag, it should be the last bytes ofciphertext. If theIV and authentic tag are one (SIV), it should be the first bytes ofciphertext. The IV and authentication tag commonly require 16 bytes, but mayvary in size.

additional_data is aSTRING orBYTES value that binds the ciphertext toits context. This forces the ciphertext to be decrypted in the same context inwhich it was encrypted. This function casts anySTRING value toBYTES.This must be the same as theadditional_data provided toAEAD.ENCRYPT toencryptciphertext, 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 andkeysets. Then it uses these keysets to encrypt the plaintext values asBYTES and store them in a new table. Finally, itusesAEAD.DECRYPT_BYTES to decrypt the encrypted values and display them asplaintext.

The following statement creates a tableCustomerKeysets containing a column ofunique IDs, a column ofAEAD_AES_GCM_256 keysets, and a column of favoriteanimals.

CREATETABLEaead.CustomerKeysetsASSELECT1AScustomer_id,KEYS.NEW_KEYSET('AEAD_AES_GCM_256')ASkeyset,b'jaguar'ASfavorite_animalUNIONALLSELECT2AScustomer_id,KEYS.NEW_KEYSET('AEAD_AES_GCM_256')ASkeyset,b'zebra'ASfavorite_animalUNIONALLSELECT3AScustomer_id,KEYS.NEW_KEYSET('AEAD_AES_GCM_256')ASkeyset,b'nautilus'ASfavorite_animal;

The following statement creates a tableEncryptedCustomerData containing acolumn of unique IDs and a column of ciphertext. The statement encrypts theplaintextfavorite_animal using the keyset value fromCustomerKeysetscorresponding to each unique ID.

CREATETABLEaead.EncryptedCustomerDataASSELECTcustomer_id,AEAD.ENCRYPT(keyset,favorite_animal,CAST(CAST(customer_idASSTRING)ASBYTES))ASencrypted_animalFROMaead.CustomerKeysetsASck;

The following query uses the keysets in theCustomerKeysets table to decryptdata in theEncryptedCustomerData table.

SELECTecd.customer_id,AEAD.DECRYPT_BYTES((SELECTck.keysetFROMaead.CustomerKeysetsASckWHEREecd.customer_id=ck.customer_id),ecd.encrypted_animal,CAST(CAST(customer_idASSTRING)ASBYTES))ASfavorite_animalFROMaead.EncryptedCustomerDataASecd;

AEAD.DECRYPT_STRING

AEAD.DECRYPT_STRING(keyset,ciphertext,additional_data)

Description

LikeAEAD.DECRYPT_BYTES, but whereadditional_data isof typeSTRING.

Return Data Type

STRING

AEAD.ENCRYPT

AEAD.ENCRYPT(keyset,plaintext,additional_data)

Description

Encryptsplaintext using the primary cryptographic key inkeyset. Thealgorithm of the primary key must beAEAD_AES_GCM_256. Binds the ciphertext tothe context defined byadditional_data. ReturnsNULL if any input isNULL.

keyset is a serializedBYTES value returned by one of theKEYS functions or aSTRUCT returned byKEYS.KEYSET_CHAIN.

plaintext is theSTRING orBYTES value to be encrypted.

additional_data is aSTRING orBYTES value that binds the ciphertext toits context. This forces the ciphertext to be decrypted in the same context inwhich it was encrypted.plaintext andadditional_data must be of the sametype.AEAD.ENCRYPT(keyset, string1, string2) is equivalent toAEAD.ENCRYPT(keyset, CAST(string1 AS BYTES), CAST(string2 AS BYTES)).

The output is ciphertextBYTES. The ciphertext contains aTink-specific prefix indicating the key used to perform the encryption.

Return Data Type

BYTES

Example

The following query uses the keysets for eachcustomer_id in theCustomerKeysets table to encrypt the value of the plaintextfavorite_animalin thePlaintextCustomerData table corresponding to thatcustomer_id. Theoutput contains a column ofcustomer_id values and a column ofcorresponding ciphertext output asBYTES.

WITHCustomerKeysetsAS(SELECT1AScustomer_id,KEYS.NEW_KEYSET('AEAD_AES_GCM_256')ASkeysetUNIONALLSELECT2,KEYS.NEW_KEYSET('AEAD_AES_GCM_256')UNIONALLSELECT3,KEYS.NEW_KEYSET('AEAD_AES_GCM_256')),PlaintextCustomerDataAS(SELECT1AScustomer_id,'elephant'ASfavorite_animalUNIONALLSELECT2,'walrus'UNIONALLSELECT3,'leopard')SELECTpcd.customer_id,AEAD.ENCRYPT((SELECTkeysetFROMCustomerKeysetsASckWHEREck.customer_id=pcd.customer_id),pcd.favorite_animal,CAST(pcd.customer_idASSTRING))ASencrypted_animalFROMPlaintextCustomerDataASpcd;

DETERMINISTIC_DECRYPT_BYTES

DETERMINISTIC_DECRYPT_BYTES(keyset,ciphertext,additional_data)

Description

Uses the matching key fromkeyset to decryptciphertext and verifies theintegrity of the data usingadditional_data. Returns an error if decryptionfails.

keyset is a serializedBYTES value or aSTRUCTvalue returned by one of theKEYS functions.keyset must containthe key that was used to encryptciphertext, the key must be in an'ENABLED'state, and the key must be of typeDETERMINISTIC_AEAD_AES_SIV_CMAC_256, orelse the function returns an error.DETERMINISTIC_DECRYPT_BYTES identifies thematching key inkeyset by finding the key with the key ID that matches the oneencrypted inciphertext.

ciphertext is aBYTES value that's the result of a call toDETERMINISTIC_ENCRYPT where the inputplaintext was of typeBYTES.

The ciphertext must follow Tink'swire format. The firstbyte ofciphertext should contain a Tink key version followed by a 4 byte keyhint. Ifciphertext includes an initialization vector (IV), it should be thenext bytes ofciphertext. Ifciphertext includes an authentication tag, itshould be the last bytes ofciphertext. If the IV and authentic tag are one(SIV), it should be the first bytes ofciphertext. The IV and authenticationtag commonly require 16 bytes, but may vary in size.

additional_data is aSTRING orBYTES value that binds the ciphertext toits context. This forces the ciphertext to be decrypted in the same context inwhich it was encrypted. This function casts anySTRING value toBYTES. Thismust be the same as theadditional_data provided toDETERMINISTIC_ENCRYPT toencryptciphertext, 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 andkeysets. Then it uses these keysets to encrypt the plaintext values asBYTESand store them in a new table. Finally, it usesDETERMINISTIC_DECRYPT_BYTES todecrypt the encrypted values and display them as plaintext.

The following statement creates a tableCustomerKeysets containing a column ofunique IDs, a column ofDETERMINISTIC_AEAD_AES_SIV_CMAC_256 keysets, and acolumn of favorite animals.

CREATETABLEdeterministic.CustomerKeysetsASSELECT1AScustomer_id,KEYS.NEW_KEYSET('DETERMINISTIC_AEAD_AES_SIV_CMAC_256')ASkeyset,b'jaguar'ASfavorite_animalUNIONALLSELECT2AScustomer_id,KEYS.NEW_KEYSET('DETERMINISTIC_AEAD_AES_SIV_CMAC_256')ASkeyset,b'zebra'ASfavorite_animalUNIONALLSELECT3AScustomer_id,KEYS.NEW_KEYSET('DETERMINISTIC_AEAD_AES_SIV_CMAC_256')ASkeyset,b'nautilus'ASfavorite_animal;

The following statement creates a tableEncryptedCustomerData containing acolumn of unique IDs and a column of ciphertext. The statement encrypts theplaintextfavorite_animal using the keyset value fromCustomerKeysetscorresponding to each unique ID.

CREATETABLEdeterministic.EncryptedCustomerDataASSELECTcustomer_id,DETERMINISTIC_ENCRYPT(ck.keyset,favorite_animal,CAST(CAST(customer_idASSTRING)ASBYTES))ASencrypted_animalFROMdeterministic.CustomerKeysetsASck;

The following query uses the keysets in theCustomerKeysets table to decryptdata in theEncryptedCustomerData table.

SELECTecd.customer_id,DETERMINISTIC_DECRYPT_BYTES((SELECTck.keysetFROMdeterministic.CustomerKeysetsASckWHEREecd.customer_id=ck.customer_id),ecd.encrypted_animal,CAST(CAST(ecd.customer_idASSTRING)ASBYTES))ASfavorite_animalFROMdeterministic.EncryptedCustomerDataASecd;

DETERMINISTIC_DECRYPT_STRING

DETERMINISTIC_DECRYPT_STRING(keyset,ciphertext,additional_data)

Description

LikeDETERMINISTIC_DECRYPT_BYTES, but whereplaintext is of typeSTRING.

Return Data Type

STRING

DETERMINISTIC_ENCRYPT

DETERMINISTIC_ENCRYPT(keyset,plaintext,additional_data)

Description

Encryptsplaintext using the primary cryptographic key inkeyset usingdeterministic AEAD. The algorithm of the primary key mustbeDETERMINISTIC_AEAD_AES_SIV_CMAC_256. Binds the ciphertext to the contextdefined byadditional_data. ReturnsNULL if any input isNULL.

keyset is a serializedBYTES value or aSTRUCTvalue returned by one of theKEYS functions.

plaintext is theSTRING orBYTES value to be encrypted.

additional_data is aSTRING orBYTES value that binds the ciphertext toits context. This forces the ciphertext to be decrypted in the same context inwhich it was encrypted.plaintext andadditional_data must be of the sametype.DETERMINISTIC_ENCRYPT(keyset, string1, string2) is equivalent toDETERMINISTIC_ENCRYPT(keyset, CAST(string1 AS BYTES), CAST(string2 AS BYTES)).

The output is ciphertextBYTES. The ciphertext contains aTink-specific prefix indicating the key used to perform the encryption.Given an identicalkeyset andplaintext, this function returns the sameciphertext each time it's invoked (including across queries).

Return Data Type

BYTES

Example

The following query uses the keysets for eachcustomer_id in theCustomerKeysets table to encrypt the value of the plaintextfavorite_animalin thePlaintextCustomerData table corresponding to thatcustomer_id. Theoutput contains a column ofcustomer_id values and a column of correspondingciphertext output asBYTES.

WITHCustomerKeysetsAS(SELECT1AScustomer_id,KEYS.NEW_KEYSET('DETERMINISTIC_AEAD_AES_SIV_CMAC_256')ASkeysetUNIONALLSELECT2,KEYS.NEW_KEYSET('DETERMINISTIC_AEAD_AES_SIV_CMAC_256')UNIONALLSELECT3,KEYS.NEW_KEYSET('DETERMINISTIC_AEAD_AES_SIV_CMAC_256')),PlaintextCustomerDataAS(SELECT1AScustomer_id,'elephant'ASfavorite_animalUNIONALLSELECT2,'walrus'UNIONALLSELECT3,'leopard')SELECTpcd.customer_id,DETERMINISTIC_ENCRYPT((SELECTkeysetFROMCustomerKeysetsASckWHEREck.customer_id=pcd.customer_id),pcd.favorite_animal,CAST(pcd.customer_idASSTRING))ASencrypted_animalFROMPlaintextCustomerDataASpcd;

KEYS.ADD_KEY_FROM_RAW_BYTES

KEYS.ADD_KEY_FROM_RAW_BYTES(keyset,key_type,raw_key_bytes)

Description

Returns a serialized keyset asBYTES with theaddition of a key tokeyset based onkey_type andraw_key_bytes.

The primary cryptographic key remains the same as inkeyset. The expectedlength ofraw_key_bytes depends on the value ofkey_type. The following aresupportedkey_types:

• 'AES_CBC_PKCS': Creates a key for AES decryption using cipher block chainingand PKCS padding.raw_key_bytes is expected to be a raw keyBYTES value of length 16, 24, or 32; theselengths have sizes of 128, 192, and 256 bits, respectively. GoogleSQLAEAD 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 usingGalois/Counter Mode.raw_key_bytes must be a raw keyBYTESvalue of length 16 or 32; these lengths have sizes of 128 and 256 bits,respectively. When keys of this type are inputs toAEAD.ENCRYPT, the outputciphertext doesn't have a Tink-specific prefix indicating which key wasused as input.

Return Data Type

BYTES

Example

The following query creates a table of customer IDs along with raw key bytes,calledCustomerRawKeys, and a table of unique IDs, calledCustomerIds. Itcreates a new'AEAD_AES_GCM_256' keyset for eachcustomer_id; then it adds anew key to each keyset, using theraw_key_bytes value corresponding to thatcustomer_id. The output is a table where each row contains acustomer_id anda keyset inBYTES, which contains the raw key addedusing KEYS.ADD_KEY_FROM_RAW_BYTES.

WITHCustomerRawKeysAS(SELECT1AScustomer_id,b'0123456789012345'ASraw_key_bytesUNIONALLSELECT2,b'9876543210543210'UNIONALLSELECT3,b'0123012301230123'),CustomerIdsAS(SELECT1AScustomer_idUNIONALLSELECT2UNIONALLSELECT3)SELECTci.customer_id,KEYS.ADD_KEY_FROM_RAW_BYTES(KEYS.NEW_KEYSET('AEAD_AES_GCM_256'),'AES_CBC_PKCS',(SELECTraw_key_bytesFROMCustomerRawKeysAScrkWHEREcrk.customer_id=ci.customer_id))ASkeysetFROMCustomerIdsASci;

The output keysets each contain two things: the primary cryptographic keycreated usingKEYS.NEW_KEYSET('AEAD_AES_GCM_256'), and the raw key added usingKEYS.ADD_KEY_FROM_RAW_BYTES. If a keyset in the output is used withAEAD.ENCRYPT, GoogleSQL uses the primary cryptographic key createdusingKEYS.NEW_KEYSET('AEAD_AES_GCM_256') to encrypt the input plaintext. Ifthe keyset is used withAEAD.DECRYPT_STRING orAEAD.DECRYPT_BYTES,GoogleSQL returns the resulting plaintext if either key succeeds indecrypting the ciphertext.

KEYS.KEYSET_CHAIN

KEYS.KEYSET_CHAIN(kms_resource_name,first_level_keyset)

Description

Can be used in place of thekeyset argument to the AEADand deterministicencryption functions to pass aTink keyset that's encryptedwith aCloud KMS key. This function lets you useother AEAD functions without including plaintext keys in a query.

This function takes the following arguments:

• kms_resource_name: ASTRING literal that contains the resource path tothe Cloud KMS key that's used to decryptfirst_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: ABYTES literal that represents akeysetorwrapped keyset.

Return Data Type

STRUCT

Example

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

The following statement creates a tableRawCustomerData containing a column ofcustomer ids and a column of favorite animals.

CREATETABLEaead.RawCustomerDataASSELECT1AScustomer_id,b'jaguar'ASfavorite_animalUNIONALLSELECT2AScustomer_id,b'zebra'ASfavorite_animalUNIONALLSELECT3AScustomer_id,b'zebra'ASfavorite_animal;

The following statement creates a tableEncryptedCustomerData containing acolumn of unique IDs and a column of ciphertext. The statement encrypts theplaintextfavorite_animal using the first_level_keyset provided.

DECLAREkms_resource_nameSTRING;DECLAREfirst_level_keysetBYTES;SETkms_resource_name='gcp-kms://projects/my-project/locations/us/keyRings/my-key-ring/cryptoKeys/my-crypto-key';SETfirst_level_keyset=b'\012\044\000\107\275\360\176\264\206\332\235\215\304...';CREATETABLEaead.EncryptedCustomerDataASSELECTcustomer_id,AEAD.ENCRYPT(KEYS.KEYSET_CHAIN(kms_resource_name,first_level_keyset),favorite_animal,CAST(CAST(customer_idASSTRING)ASBYTES))ASencrypted_animalFROMaead.RawCustomerData;

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

DECLAREkms_resource_nameSTRING;DECLAREfirst_level_keysetBYTES;SETkms_resource_name='gcp-kms://projects/my-project/locations/us/keyRings/my-key-ring/cryptoKeys/my-crypto-key';SETfirst_level_keyset=b'\012\044\000\107\275\360\176\264\206\332\235\215\304...';SELECTcustomer_id,AEAD.DECRYPT_BYTES(KEYS.KEYSET_CHAIN(kms_resource_name,first_level_keyset),encrypted_animal,CAST(CAST(customer_idASSTRING)ASBYTES))ASfavorite_animalFROMaead.EncryptedCustomerData;

The previous two steps also work with theDETERMINISTIC_ENCRYPT andDETERMINISTIC_DECRYPT_BYTES functions. The wrapped keyset must be createdusing theDETERMINISTIC_AEAD_AES_SIV_CMAC_256 type.

The following statement creates a tableEncryptedCustomerData containing acolumn of unique IDs and a column of ciphertext. The statement encrypts theplaintextfavorite_animal using the first_level_keyset provided. You can seethat the ciphertext forfavorite_animal is the same for customers 2 and 3since their plaintextfavorite_animal is the same.

DECLAREkms_resource_nameSTRING;DECLAREfirst_level_keysetBYTES;SETkms_resource_name='gcp-kms://projects/my-project/locations/us/keyRings/my-key-ring/cryptoKeys/my-crypto-key';SETfirst_level_keyset=b'\012\044\000\107\275\360\176\264\206\332\235\215\304...';CREATETABLEdaead.EncryptedCustomerDataASSELECTcustomer_id,DETERMINISTC_ENCRYPT(KEYS.KEYSET_CHAIN(kms_resource_name,first_level_keyset),favorite_animal,CAST(CAST(customer_idASSTRING)ASBYTES))ASencrypted_animalFROMdaead.RawCustomerData;

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

DECLAREkms_resource_nameSTRING;DECLAREfirst_level_keysetBYTES;SETkms_resource_name='gcp-kms://projects/my-project/locations/us/keyRings/my-key-ring/cryptoKeys/my-crypto-key';SETfirst_level_keyset=b'\012\044\000\107\275\360\176\264\206\332\235\215\304...';SELECTcustomer_id,DETERMINISTIC_DECRYPT_BYTES(KEYS.KEYSET_CHAIN(kms_resource_name,first_level_keyset),encrypted_animal,CAST(CAST(customer_idASSTRING)ASBYTES))ASfavorite_animalFROMdead.EncryptedCustomerData;

KEYS.KEYSET_FROM_JSON

KEYS.KEYSET_FROM_JSON(json_keyset)

Description

Returns the inputjson_keysetSTRING asserializedBYTES, which is a valid input for otherKEYS andAEAD functions. The JSONSTRING mustbe compatible with the definition of thegoogle.crypto.tink.Keysetprotocol buffer message: the JSON keyset should be a JSON object containingobjects and name-value pairs corresponding to those in the "keyset" message inthe google.crypto.tink.Keyset definition. You can convert the output serializedBYTES representation back to a JSONSTRING usingKEYS.KEYSET_TO_JSON.

Return Data Type

BYTES

Example

KEYS.KEYSET_FROM_JSON takes JSON-formattedSTRINGvalues 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-formattedSTRINGjson_keyset:

SELECTKEYS.KEYSET_FROM_JSON(json_keyset);

This returns thejson_keyset serialized asBYTES, like the following:

\x08\x9d\x8e\x85\x82\x09\x12d\x0aX\x0a0type.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 STRINGcalledjson_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 convertsjson_keyset to a keyset and then returnsthe number of keys in the keyset:

SELECTKEYS.KEYSET_LENGTH(KEYS.KEYSET_FROM_JSON(json_keyset))askey_count;/*-----------+ | key_count | +-----------+ | 2         | +-----------*/

KEYS.KEYSET_TO_JSON

KEYS.KEYSET_TO_JSON(keyset)

Description

Returns a JSONSTRING representation of the inputkeyset. The returned JSONSTRING is compatiblewith the definition of thegoogle.crypto.tink.Keysetprotocol buffer message. You can convert the JSONSTRING representation back toBYTES usingKEYS.KEYSET_FROM_JSON.

Return Data Type

STRING

Example

The following query returns a new'AEAD_AES_GCM_256' keyset as aJSON-formattedSTRING.

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

The result is aSTRING 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 onkey_type. Thereturned keyset is a serializedBYTESrepresentation ofgoogle.crypto.tink.Keysetthat contains a primary cryptographic key and no additional keys. You can usethe keyset with theAEAD.ENCRYPT,AEAD.DECRYPT_BYTES, andAEAD.DECRYPT_STRING functions for encryption and decryption, as well as withtheKEYS group of key- and keyset-related functions.

key_type is aSTRING literal representation of the type of key to create.key_type can't beNULL.key_type can be:

• AEAD_AES_GCM_256: Creates a 256-bit key with the pseudo-random numbergenerator provided byboringSSL. The key uses AES-GCM forencryption and decryption operations.
• DETERMINISTIC_AEAD_AES_SIV_CMAC_256:Creates a 512-bitAES-SIV-CMAC key, which contains a 256-bitAES-CTR keyand 256-bitAES-CMAC key. TheAES-SIV-CMAC key is created with thepseudo-random number generator provided byboringSSL. The keyuses AES-SIV for encryption and decryption operations.

Return Data Type

BYTES

Example

The following query creates a keyset for each row inCustomerIds, which cansubsequently be used to encrypt data. Each keyset contains a single encryptionkey with randomly-generated key data. Each row in the output contains acustomer_id and an'AEAD_AES_GCM_256' key inBYTES.

SELECTcustomer_id,KEYS.NEW_KEYSET('AEAD_AES_GCM_256')ASkeysetFROM(SELECT1AScustomer_idUNIONALLSELECT2UNIONALLSELECT3)ASCustomerIds;

KEYS.NEW_WRAPPED_KEYSET

KEYS.NEW_WRAPPED_KEYSET(kms_resource_name,key_type)

Description

Creates a new keyset and encrypts it with aCloud KMS key.Returns thewrapped keyset as aBYTESrepresentation ofgoogle.crypto.tink.Keysetthat contains a primary cryptographic key and no additional keys.

This function takes the following arguments:

• kms_resource_name: ASTRING literal representation of theCloud KMS key.kms_resource_name can't beNULL. TheCloud KMS key must reside in the same Cloud region where thisfunction 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: ASTRING literal representation of the keyset type.key_type can't beNULL but can be one of the following values:

  • AEAD_AES_GCM_256: Creates a 256-bit key with the pseudo-random numbergenerator provided byboringSSL. The key uses AES-GCM forencryption and decryption operations.

  • DETERMINISTIC_AEAD_AES_SIV_CMAC_256:Creates a 512-bitAES-SIV-CMAC key, which contains a 256-bitAES-CTR keyand 256-bitAES-CMAC key. TheAES-SIV-CMAC key is created with thepseudo-random number generator provided byboringSSL. The keyuses AES-SIV for encryption and decryption operations.

Return Data Type

BYTES

Example

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

DECLAREkms_resource_nameSTRING;SETkms_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 ciphertextproduced by encrypting aTink keysetwith the specified Cloud KMS key. If you run the query multiple times,it generates multiple wrapped keysets, and each wrapped keyset is unique toeach query that's run.

SELECTKEYS.NEW_WRAPPED_KEYSET(kms_resource_name,'AEAD_AES_GCM_256');

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

SELECT*,KEYS.NEW_WRAPPED_KEYSET(kms_resource_name,'AEAD_AES_GCM_256')FROMmy_table

KEYS.REWRAP_KEYSET

KEYS.REWRAP_KEYSET(source_kms_resource_name,target_kms_resource_name,wrapped_keyset)

Description

Re-encrypts awrapped keyset with a newCloud KMS key. Returns the wrapped keyset as aBYTES representation ofgoogle.crypto.tink.Keysetthat contains a primary cryptographic key and no additional keys.

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

This function takes the following arguments:

• source_kms_resource_name: ASTRING literal representation of theCloud KMS key you want to replace. This key must reside in the sameCloud region where this function is executed. A Cloud KMS key lookslike this:

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

• target_kms_resource_name: ASTRING literal representation of thenew Cloud KMS key that you want to use.

• wrapped_keyset: ABYTES literal representation of thekeyset that you want to re-encrypt.

Return Data Type

BYTES

Example

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

DECLAREsource_kms_resource_nameSTRING;DECLAREtarget_kms_resource_nameSTRING;DECLAREwrapped_keysetBYTES;SETsource_kms_resource_name='gcp-kms://projects/my-project/locations/us/keyRings/my-key-ring/cryptoKeys/my-crypto-key';SETtarget_kms_resource_name='gcp-kms://projects/my-project/locations/another-location/keyRings/my-key-ring/cryptoKeys/my-other-crypto-key';SETwrapped_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 multipletimes, it generates multiple wrapped keysets, and each wrapped keyset is uniqueto each query that's run.

SELECTKEYS.REWRAP_KEYSET(source_kms_resource_name,target_kms_resource_name,wrapped_keyset);

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

SELECT*,KEYS.REWRAP_KEYSET(source_kms_resource_name,target_kms_resource_name,wrapped_keyset)FROMmy_table

KEYS.ROTATE_KEYSET

KEYS.ROTATE_KEYSET(keyset,key_type)

Description

Adds a new key tokeyset based onkey_type. This new key becomes the primarycryptographic key of the new keyset. Returns the new keyset serialized asBYTES.

The old primary cryptographic key from the inputkeyset remains an additionalkey in the returned keyset.

The newkey_type must match the key type of existing keys in thekeyset.

Return Data Type

BYTES

Example

The following statement creates a table containing a column of uniquecustomer_id values and'AEAD_AES_GCM_256' keysets. Then, it creates a newprimary cryptographic key within each keyset in the source table usingKEYS.ROTATE_KEYSET. Each row in the output contains acustomer_id and an'AEAD_AES_GCM_256' keyset inBYTES.

WITHExistingKeysetsAS(SELECT1AScustomer_id,KEYS.NEW_KEYSET('AEAD_AES_GCM_256')ASkeysetUNIONALLSELECT2,KEYS.NEW_KEYSET('AEAD_AES_GCM_256')UNIONALLSELECT3,KEYS.NEW_KEYSET('AEAD_AES_GCM_256'))SELECTcustomer_id,KEYS.ROTATE_KEYSET(keyset,'AEAD_AES_GCM_256')ASkeysetFROMExistingKeysets;

KEYS.ROTATE_WRAPPED_KEYSET

KEYS.ROTATE_WRAPPED_KEYSET(kms_resource_name,wrapped_keyset,key_type)

Description

Takes an existingwrapped keyset and returns a rotated andrewrapped keyset. The returned wrapped keyset is aBYTESrepresentation ofgoogle.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 primarycryptographic key from the inputwrapped_keyset remains as anadditional 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: ASTRING literal representation of theCloud KMS key that was used to wrap thewrapped keyset. The Cloud KMS key must reside in the same Cloudregion where this function is executed. A Cloud KMS key looks likethis:

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

• wrapped_keyset: ABYTES literal representation of theexisting keyset that you want to work with.

• key_type: ASTRING literal representation of the keyset type. This mustmatch the key type of existing keys inwrapped_keyset.

Return Data Type

BYTES

Example

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

DECLAREkms_resource_nameSTRING;DECLAREwrapped_keysetBYTES;SETkms_resource_name='gcp-kms://projects/my-project/locations/us/keyRings/my-key-ring/cryptoKeys/my-crypto-key';SETwrapped_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 multipletimes, it generates multiple wrapped keysets, and each wrapped keyset is uniqueto each query that's run.

SELECTKEYS.ROTATE_WRAPPED_KEYSET(kms_resource_name,wrapped_keyset,'AEAD_AES_GCM_256');

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

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