DLP encryption functions

GoogleSQL for BigQuery supports the following DLP functions that allow interoperable encryption and decryption between BigQuery and Cloud Data Loss Prevention (Cloud DLP), using AES-SIV. To use DLP functions, you need a new cryptographic key and then use that key to get a wrapped key.

Function list

Name Summary
DLP_DETERMINISTIC_ENCRYPT Encrypts data with a DLP compatible algorithm.
DLP_DETERMINISTIC_DECRYPT Decrypts DLP-encrypted data.
DLP_KEY_CHAIN Gets a data encryption key that's wrapped by Cloud Key Management Service.

DLP_DETERMINISTIC_ENCRYPT

DLP_DETERMINISTIC_ENCRYPT(key,plaintext,surrogate)

DLP_DETERMINISTIC_ENCRYPT(key,plaintext,surrogate,context)

Description

This function derives a data encryption key from key and context, and then encrypts plaintext. You can use surrogate to prepend the encryption result. To use DLP functions, you need a new cryptographic key and then use that key to get a wrapped key.

Definitions

  • key: A serialized BYTES value that's returned by DLP_KEY_CHAIN. key must be set to ENABLED in Cloud KMS. For information about how to generate a wrapped key, see gcloud kms encrypt.
  • plaintext: The STRING value to encrypt.
  • surrogate: A STRING value that you can prepend to output. If you don't want to use surrogate, pass an empty string (enclosed in "").
  • context: A user-provided STRING value that's used with a Cloud KMS key to derive a data encryption key. For more information, see CryptoDeterministicConfig:context.

Return data type

STRING

Examples

In the following query, the wrapped key is presented in a BYTES literal format:

SELECT
DLP_DETERMINISTIC_ENCRYPT(
DLP_KEY_CHAIN(
'gcp-kms://projects/myproject/locations/us/keyRings/kms-test/cryptoKeys/test-KEK',
b'0円120円440円00\325\155\264\153\2460円71\172\130\372\305\1030円47\342\3560円610円770円140円30\126\1470円41\126\1500円120円360円20\202\2150円44\267\310\3310円14\116\2330円220円710円00\363\344\2300円67\2740円07\340\2730円16\212\151\2260円64\200\377\303\207\103\1470円52\2670円35\3500円04\147\365\251\271\1330円62\251\246\152\1770円170円05\2700円44\141\211\116\3370円430円35\263\122\340\110\333\266\220\377\247\204\215\233'),
'Plaintext',
'',
'aad')ASresults;
/*--------------------------------------+
 | results |
 +--------------------------------------+
 | AWDeSznl9C7+NzTaCgiqiEAZ8Y55fZSuvCQ= |
 +--------------------------------------*/

In the following query, the wrapped key is presented in the base64 format:

DECLAREDLP_KEY_VALUEBYTES;
SETDLP_KEY_VALUE=
FROM_BASE64(
'CiQA1W20a6Y5elj6xUMn4u4xPwwYVmchVmgKHhCCjSS3yNkMTpsSOQDz5Jg3vAfguw6KaZY0gP/Dh0NnKrcd6ARn9am5WzKppmp/DwW4JGGJTt8jHbNS4EjbtpD/p4SNmw==');
SELECT
DLP_DETERMINISTIC_ENCRYPT(
DLP_KEY_CHAIN(
'gcp-kms://projects/myproject/locations/us/keyRings/kms-test/cryptoKeys/test-Kek',
DLP_KEY_VALUE),
'Plaintext',
'your_surrogate',
'aad')ASresults;
/*---------------------------------------------------------+
 | results |
 +---------------------------------------------------------+
 | your_surrogate(36):AWDeSznl9C7+NzTaCgiqiEAZ8Y55fZSuvCQ= |
 +---------------------------------------------------------*/

DLP_DETERMINISTIC_DECRYPT

DLP_DETERMINISTIC_DECRYPT(key,ciphertext,surrogate)

DLP_DETERMINISTIC_DECRYPT(key,ciphertext,surrogate,context)

Description

This function decrypts ciphertext using an encryption key derived from key and context. You can use surrogate to prepend the decryption result. To use DLP functions, you need a new cryptographic key and then use that key to get a wrapped key.

Definitions

  • key: A serialized BYTES value returned by DLP_KEY_CHAIN. key must be set to ENABLED in Cloud KMS. For information about how to generate a wrapped key, see gcloud kms encrypt.
  • ciphertext: The STRING value to decrypt.
  • surrogate: A STRING value that you can prepend to output. If you don't want to use surrogate, pass an empty string (enclosed in "").
  • context: A STRING value that's used with a Cloud KMS key to derive a data encryption key. For more information, see CryptoDeterministicConfig:context.

Return data type

STRING

Examples

In the following query, the wrapped key is presented in a BYTES literal format:

SELECT
DLP_DETERMINISTIC_DECRYPT(
DLP_KEY_CHAIN(
'gcp-kms://projects/myproject/locations/us/keyRings/kms-test/cryptoKeys/test-Kek',
b'0円120円440円00\325\155\264\153\2460円71\172\130\372\305\1030円47\342\3560円610円770円140円30\126\1470円41\126\1500円120円360円20\202\2150円44\267\310\3310円14\116\2330円220円710円00\363\344\2300円67\2740円07\340\2730円16\212\151\2260円64\200\377\303\207\103\1470円52\2670円35\3500円04\147\365\251\271\1330円62\251\246\152\1770円170円05\2700円44\141\211\116\3370円430円35\263\122\340\110\333\266\220\377\247\204\215\233'),
'AWDeSznl9C7+NzTaCgiqiEAZ8Y55fZSuvCQ=',
'',
'aad')ASresults;
/*--------------------------------------+
 | results |
 +--------------------------------------+
 | Plaintext |
 +--------------------------------------*/

In the following query, the wrapped key is presented in the base64 format:

DECLAREDLP_KEY_VALUEBYTES;
SETDLP_KEY_VALUE=
FROM_BASE64(
'CiQA1W20a6Y5elj6xUMn4u4xPwwYVmchVmgKHhCCjSS3yNkMTpsSOQDz5Jg3vAfguw6KaZY0gP/Dh0NnKrcd6ARn9am5WzKppmp/DwW4JGGJTt8jHbNS4EjbtpD/p4SNmw==');
SELECT
DLP_DETERMINISTIC_DECRYPT(
DLP_KEY_CHAIN(
'gcp-kms://projects/myproject/locations/us/keyRings/kms-test/cryptoKeys/test-Kek',
DLP_KEY_VALUE),
'your_surrogate(36):AWDeSznl9C7+NzTaCgiqiEAZ8Y55fZSuvCQ=',
'your_surrogate',
'aad')ASresults;
/*--------------------------------------+
 | results |
 +--------------------------------------+
 | Plaintext |
 +--------------------------------------*/

DLP_KEY_CHAIN

DLP_KEY_CHAIN(kms_resource_name,wrapped_key)

Description

You can use this function instead of the key argument for DLP deterministic encryption functions. This function lets you use the AES-SIV encryption functions without including plaintext keys in a query. To use DLP functions, you need a new cryptographic key and then use that key to get a wrapped key.

Definitions

  • kms_resource_name: A STRING literal that contains the resource path to the Cloud KMS key. kms_resource_name can't be NULL and must reside in the same Cloud region where this function is executed. This argument is used to derive the data encryption key in the DLP_DETERMINISTIC_DECRYPT and DLP_DETERMINISTIC_ENCRYPT functions. A Cloud KMS key looks like this:

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

  • wrapped_key: A BYTES literal that represents a secret text chosen by the user. This secret text can be 16, 24, or 32 bytes. For information about how to generate a wrapped key, see gcloud kms encrypt.

Return data type

STRUCT

Examples

In the following query, the wrapped key is presented in a BYTES literal format:

SELECT
DLP_DETERMINISTIC_ENCRYPT(
DLP_KEY_CHAIN(
'gcp-kms://projects/myproject/locations/us/keyRings/kms-test/cryptoKeys/test-Kek',
b'0円120円440円00\325\155\264\153\2460円71\172\130\372\305\1030円47\342\3560円610円770円140円30\126\1470円41\126\1500円120円360円20\202\2150円44\267\310\3310円14\116\2330円220円710円00\363\344\2300円67\2740円07\340\2730円16\212\151\2260円64\200\377\303\207\103\1470円52\2670円35\3500円04\147\365\251\271\1330円62\251\246\152\1770円170円05\2700円44\141\211\116\3370円430円35\263\122\340\110\333\266\220\377\247\204\215\233'),
'Plaintext',
'',
'aad')ASresults;
/*--------------------------------------+
 | results |
 +--------------------------------------+
 | AWDeSznl9C7+NzTaCgiqiEAZ8Y55fZSuvCQ= |
 +--------------------------------------*/

In the following query, the wrapped key is presented in the base64 format:

DECLAREDLP_KEY_VALUEBYTES;
SETDLP_KEY_VALUE=
FROM_BASE64(
'CiQA1W20a6Y5elj6xUMn4u4xPwwYVmchVmgKHhCCjSS3yNkMTpsSOQDz5Jg3vAfguw6KaZY0gP/Dh0NnKrcd6ARn9am5WzKppmp/DwW4JGGJTt8jHbNS4EjbtpD/p4SNmw==');
SELECT
DLP_DETERMINISTIC_ENCRYPT(
DLP_KEY_CHAIN(
'gcp-kms://projects/myproject/locations/us/keyRings/kms-test/cryptoKeys/test-Kek',
DLP_KEY_VALUE),
'Plaintext',
'',
'aad')ASresults;
/*--------------------------------------+
 | results |
 +--------------------------------------+
 | AWDeSznl9C7+NzTaCgiqiEAZ8Y55fZSuvCQ= |
 +--------------------------------------*/

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see the Google Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025年11月11日 UTC.