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 is 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 is 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 is 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'\012\044\000\325\155\264\153\246\071\172\130\372\305\103\047\342\356\061\077\014\030\126\147\041\126\150\012\036\020\202\215\044\267\310\331\014\116\233\022\071\000\363\344\230\067\274\007\340\273\016\212\151\226\064\200\377\303\207\103\147\052\267\035\350\004\147\365\251\271\133\062\251\246\152\177\017\005\270\044\141\211\116\337\043\035\263\122\340\110\333\266\220\377\247\204\215\233'),
    'Plaintext',
    '',
    'aad') AS results;

/*--------------------------------------*
 | results                              |
 +--------------------------------------+
 | AWDeSznl9C7+NzTaCgiqiEAZ8Y55fZSuvCQ= |
 *--------------------------------------*/

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

DECLARE DLP_KEY_VALUE BYTES;

SET DLP_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') AS results;

/*---------------------------------------------------------*
 | 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 is 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'\012\044\000\325\155\264\153\246\071\172\130\372\305\103\047\342\356\061\077\014\030\126\147\041\126\150\012\036\020\202\215\044\267\310\331\014\116\233\022\071\000\363\344\230\067\274\007\340\273\016\212\151\226\064\200\377\303\207\103\147\052\267\035\350\004\147\365\251\271\133\062\251\246\152\177\017\005\270\044\141\211\116\337\043\035\263\122\340\110\333\266\220\377\247\204\215\233'),
    'AWDeSznl9C7+NzTaCgiqiEAZ8Y55fZSuvCQ=',
    '',
    'aad') AS results;

/*--------------------------------------*
 | results                              |
 +--------------------------------------+
 | Plaintext                            |
 *--------------------------------------*/

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

DECLARE DLP_KEY_VALUE BYTES;

SET DLP_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') AS results;

/*--------------------------------------*
 | 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 cannot 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'\012\044\000\325\155\264\153\246\071\172\130\372\305\103\047\342\356\061\077\014\030\126\147\041\126\150\012\036\020\202\215\044\267\310\331\014\116\233\022\071\000\363\344\230\067\274\007\340\273\016\212\151\226\064\200\377\303\207\103\147\052\267\035\350\004\147\365\251\271\133\062\251\246\152\177\017\005\270\044\141\211\116\337\043\035\263\122\340\110\333\266\220\377\247\204\215\233'),
    'Plaintext',
    '',
    'aad') AS results;

/*--------------------------------------*
 | results                              |
 +--------------------------------------+
 | AWDeSznl9C7+NzTaCgiqiEAZ8Y55fZSuvCQ= |
 *--------------------------------------*/

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

DECLARE DLP_KEY_VALUE BYTES;

SET DLP_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') AS results;

/*--------------------------------------*
 | results                              |
 +--------------------------------------+
 | AWDeSznl9C7+NzTaCgiqiEAZ8Y55fZSuvCQ= |
 *--------------------------------------*/