Deterministic encryption failing

[tanu3012]

I am trying to build a function to encrypt PII data in GBQ tables.

CREATE OR REPLACE FUNCTION `centralized-data-hub-reporting.USCentral1_dataset.ecrypt`(COL string) RETURNS BYTES AS (

DETERMINISTIC_ENCRYPT(

KEYS.KEYSET_CHAIN('gcp-kms://projects/centralized-data-hub-reporting/locations/us-central1/keyRings/bi-keyring/cryptoKeys/bi-key',b'CiQA1NQUSleANwuPFTwbTWnmcgYFTwfI+x/PYab......'),COL, ""));

The function is created but while using it's throwing error

SELECT

  name,USCentral1_dataset.ecrypt(name) as e_name

FROM

  centralized-data-hub-reporting.USCentral1_dataset.customer;

Steps I followed to create key,key_ring,cipher key are as follows:

1) gcloud kms keyrings create "bi-keyring" \

    --location "us-central1"

2)gcloud kms keys create "bi-key" \

  --location "us-central1" \

    --keyring "bi-keyring" \

    --purpose "encryption"

3)gcloud kms keys list \

    --location "us-central1" \

    --keyring "bi-keyring"

4)base64 - encoded AES key

gRXdkfRMrf+AN+G8D/dth7XD3Pb4G7M+5E7f3LZn7tw=

5) wrapped the AES using cloud KMS key

curl "https://cloudkms.googleapis.com/v1/projects/centralized-data-hub-reporting/locations/us-central1/key..." \

  --request "POST" \

  --header "Authorization:Bearer $(gcloud auth application-default print-access-token)" \

  --header "content-type: application/json" \

  --data "{\"plaintext\": \"gRXdkfRMrf+AN+G8D/dth7XD3Pb4G7M+5E7f3LZn7tw=\"}"

result:

{

  "name": "projects/centralized-data-hub-reporting/locations/us-central1/keyRings/bi-keyring/cryptoKeys/bi-key/cryptoKeyVersions/1",

  "ciphertext": "CiQA1NQUSleANwuPFTwbTWn.....",

  "ciphertextCrc32c": "2434165586",

  "protectionLevel": "SOFTWARE"

}

[lsolatorio · Accepted solution]

Hi @tanu3012, 

Thank you for reaching out to the community.

As I review available resources in line with this failing decryption, I found a couple of possible causes:

• Incorrect key version/ format - Cloud KMS allows you to create multiple key versions and it supports multiple key formats, using the wrong version or format to decrypt your data will eventually lead to an error
• Insufficient permissions - Cloud KMS utilizes IAM to control access to keys, if you don't have the right access rights then the decryption process will fail.

Review and make sure that you are using the right key version or format, and you have the necessary IAM permission to access the key.

Here are some other related links for your reference:

• Cloud KMS client libraries
• GCP - DLP - Decryption failed: the ciphertext is invalid error when using KMS wrapped key
• GCP DLP(Data Loss prevention) getting "Decryption failed: the ciphertext is invalid."

Hope this helps.

[tanu3012]

I tried ones again with new keyring & key

now I am getting following error while creating wrapped keyset 

$ curl "https://cloudkms.googleapis.com/v1/projects/centralized-data-hub-reporting/locations/us-central1/key..." \
--request "POST" \
--header "Authorization:Bearer $(gcloud auth application-default print-access-token)" \
--header "content-type: application/json" \
--data "{\"plaintext\": \"hvVtUmEvxhkYnMok6ghkaf+dD/vRp9hUfZCrOcjZ3Tw=\"}"
{
"error": {
"code": 403,
"message": "Permission 'cloudkms.cryptoKeyVersions.useToEncrypt' denied on resource 'projects/centralized-data-hub-reporting/locations/us-central1/keyRings/encypt_keyring/cryptoKeys/encypt_key' (or it may not exist).",
"status": "PERMISSION_DENIED"
}
}