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 
 12 
 \0 
 44 
 \0 
 00 
 \ 
 325 
 \ 
 155 
 \ 
 264 
 \ 
 153 
 \ 
 246 
 \0 
 71 
 \ 
 172 
 \ 
 130 
 \ 
 372 
 \ 
 305 
 \ 
 103 
 \0 
 47 
 \ 
 342 
 \ 
 356 
 \0 
 61 
 \0 
 77 
 \0 
 14 
 \0 
 30 
 \ 
 126 
 \ 
 147 
 \0 
 41 
 \ 
 126 
 \ 
 150 
 \0 
 12 
 \0 
 36 
 \0 
 20 
 \ 
 202 
 \ 
 215 
 \0 
 44 
 \ 
 267 
 \ 
 310 
 \ 
 331 
 \0 
 14 
 \ 
 116 
 \ 
 233 
 \0 
 22 
 \0 
 71 
 \0 
 00 
 \ 
 363 
 \ 
 344 
 \ 
 230 
 \0 
 67 
 \ 
 274 
 \0 
 07 
 \ 
 340 
 \ 
 273 
 \0 
 16 
 \ 
 212 
 \ 
 151 
 \ 
 226 
 \0 
 64 
 \ 
 200 
 \ 
 377 
 \ 
 303 
 \ 
 207 
 \ 
 103 
 \ 
 147 
 \0 
 52 
 \ 
 267 
 \0 
 35 
 \ 
 350 
 \0 
 04 
 \ 
 147 
 \ 
 365 
 \ 
 251 
 \ 
 271 
 \ 
 133 
 \0 
 62 
 \ 
 251 
 \ 
 246 
 \ 
 152 
 \ 
 177 
 \0 
 17 
 \0 
 05 
 \ 
 270 
 \0 
 44 
 \ 
 141 
 \ 
 211 
 \ 
 116 
 \ 
 337 
 \0 
 43 
 \0 
 35 
 \ 
 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'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 
 12 
 \0 
 44 
 \0 
 00 
 \ 
 325 
 \ 
 155 
 \ 
 264 
 \ 
 153 
 \ 
 246 
 \0 
 71 
 \ 
 172 
 \ 
 130 
 \ 
 372 
 \ 
 305 
 \ 
 103 
 \0 
 47 
 \ 
 342 
 \ 
 356 
 \0 
 61 
 \0 
 77 
 \0 
 14 
 \0 
 30 
 \ 
 126 
 \ 
 147 
 \0 
 41 
 \ 
 126 
 \ 
 150 
 \0 
 12 
 \0 
 36 
 \0 
 20 
 \ 
 202 
 \ 
 215 
 \0 
 44 
 \ 
 267 
 \ 
 310 
 \ 
 331 
 \0 
 14 
 \ 
 116 
 \ 
 233 
 \0 
 22 
 \0 
 71 
 \0 
 00 
 \ 
 363 
 \ 
 344 
 \ 
 230 
 \0 
 67 
 \ 
 274 
 \0 
 07 
 \ 
 340 
 \ 
 273 
 \0 
 16 
 \ 
 212 
 \ 
 151 
 \ 
 226 
 \0 
 64 
 \ 
 200 
 \ 
 377 
 \ 
 303 
 \ 
 207 
 \ 
 103 
 \ 
 147 
 \0 
 52 
 \ 
 267 
 \0 
 35 
 \ 
 350 
 \0 
 04 
 \ 
 147 
 \ 
 365 
 \ 
 251 
 \ 
 271 
 \ 
 133 
 \0 
 62 
 \ 
 251 
 \ 
 246 
 \ 
 152 
 \ 
 177 
 \0 
 17 
 \0 
 05 
 \ 
 270 
 \0 
 44 
 \ 
 141 
 \ 
 211 
 \ 
 116 
 \ 
 337 
 \0 
 43 
 \0 
 35 
 \ 
 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 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 
 12 
 \0 
 44 
 \0 
 00 
 \ 
 325 
 \ 
 155 
 \ 
 264 
 \ 
 153 
 \ 
 246 
 \0 
 71 
 \ 
 172 
 \ 
 130 
 \ 
 372 
 \ 
 305 
 \ 
 103 
 \0 
 47 
 \ 
 342 
 \ 
 356 
 \0 
 61 
 \0 
 77 
 \0 
 14 
 \0 
 30 
 \ 
 126 
 \ 
 147 
 \0 
 41 
 \ 
 126 
 \ 
 150 
 \0 
 12 
 \0 
 36 
 \0 
 20 
 \ 
 202 
 \ 
 215 
 \0 
 44 
 \ 
 267 
 \ 
 310 
 \ 
 331 
 \0 
 14 
 \ 
 116 
 \ 
 233 
 \0 
 22 
 \0 
 71 
 \0 
 00 
 \ 
 363 
 \ 
 344 
 \ 
 230 
 \0 
 67 
 \ 
 274 
 \0 
 07 
 \ 
 340 
 \ 
 273 
 \0 
 16 
 \ 
 212 
 \ 
 151 
 \ 
 226 
 \0 
 64 
 \ 
 200 
 \ 
 377 
 \ 
 303 
 \ 
 207 
 \ 
 103 
 \ 
 147 
 \0 
 52 
 \ 
 267 
 \0 
 35 
 \ 
 350 
 \0 
 04 
 \ 
 147 
 \ 
 365 
 \ 
 251 
 \ 
 271 
 \ 
 133 
 \0 
 62 
 \ 
 251 
 \ 
 246 
 \ 
 152 
 \ 
 177 
 \0 
 17 
 \0 
 05 
 \ 
 270 
 \0 
 44 
 \ 
 141 
 \ 
 211 
 \ 
 116 
 \ 
 337 
 \0 
 43 
 \0 
 35 
 \ 
 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= | 
 *--------------------------------------*/

DLP encryption functions Stay organized with collections Save and categorize content based on your preferences.

Function list

DLP_DETERMINISTIC_ENCRYPT

DLP_DETERMINISTIC_DECRYPT

DLP_KEY_CHAIN

DLP encryption functions

`DLP_DETERMINISTIC_ENCRYPT`

`DLP_DETERMINISTIC_DECRYPT`

`DLP_KEY_CHAIN`