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.

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, context)

DLP_DETERMINISTIC_ENCRYPT(key, plaintext, context, surrogate)

Description

This function derives a data encryption key from key and context, and then encrypts plaintext. Optionally, you can use surrogate to prepend the encryption result.

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.
  • 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.
  • surrogate: A STRING value that you can prepend to output.

Return data type

STRING

Example

SELECT
  DLP_DETERMINISTIC_ENCRYPT(
    DLP_KEY_CHAIN(
      'gcp-kms://projects/my_project/locations/us-central1/keyRings/keyringtest/cryptoKeys/testkey',
      b'\123\044\290\876....'),
    plaintext,
    '',
    'test') AS results

/*--------------------------------------*
 | results                              |
 +--------------------------------------+
 | AXDEwUnZsTf/NzxoHaC8AZXcawWuma7L39A= |
 *--------------------------------------*/

DLP_DETERMINISTIC_DECRYPT

DLP_DETERMINISTIC_DECRYPT(key, ciphertext, context)

DLP_DETERMINISTIC_DECRYPT(key, ciphertext, context, surrogate)

Description

This function decrypts ciphertext using an encryption key derived from key and context. Optionally, you can use surrogate to prepend the decryption result.

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.
  • context: A STRING value that is used with a Cloud KMS key to derive a data encryption key. For more information, see CryptoDeterministicConfig:context.
  • surrogate: A STRING value that you can prepend to output.

Return data type

STRING

Example

SELECT
  DLP_DETERMINISTIC_DECRYPT(
    DLP_KEY_CHAIN(
      'gcp-kms://projects/myproject/locations/us-central1/keyRings/keyringtest/cryptoKeys/testkey',
      b'\123\044\290\876....'),
    'your_surrogate(36)AdFnA6r5doSDWxPwW/W4vBaa4iOvDagC8z8=',
    '',
    'your_surrogate') 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.

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

Example

SELECT
  DLP_DETERMINISTIC_ENCRYPT(
    DLP_KEY_CHAIN(
      'gcp-kms://projects/my_project/locations/us-central1/keyRings/keyringtest/cryptoKeys/testkey',
      b'\123\044\290\876....'),
    plaintext,
    '',
    'test') AS results

/*--------------------------------------*
 | results                              |
 +--------------------------------------+
 | AXDEwUnZsTf/NzxoHaC8AZXcawWuma7L39A= |
 *--------------------------------------*/