Deterministic encryption failing

Google Cloud Compute Infrastructure
1

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;

tanu3012_0-1693370487679.png
tanu3012_0-1693370487679.png896×140 10.7 KB

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=

tanu3012_1-1693370942306.png

  1. wrapped the AES using cloud KMS key

curl “https://cloudkms.googleapis.com/v1/projects/centralized-data-hub-reporting/locations/us-central1/keyRings/bi-keyring/cryptoKeys/bi-key:encrypt”
–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”
}

2

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:

Hope this helps.

3

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/keyRings/encpt_keyring/cryptoKeys/encpt_key:encrypt” *
*–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”
}
}