2

I have a table which some fields are encrypted using Tink which normally worked well with BigQuery's AEAD function (https://cloud.google.com/bigquery/docs/reference/standard-sql/aead_encryption_functions#aeaddecrypt_string)

When I ran a normal query to decrypt data. I can use

DECLARE KMS_RESOURCE_NAME STRING;
DECLARE FIRST_LEVEL_KEYSET BYTES;

SET KMS_RESOURCE_NAME= "<kms-uri>";
SET FIRST_LEVEL_KEYSET = from_base64("<encrypted-keyset>");

select 
id, 
encrypted_col,
AEAD.DECRYPT_STRING(
   KEYS.KEYSET_CHAIN(KMS_RESOURCE_NAME, FIRST_LEVEL_KEYSET),
   from_base64(encrypted_col),
   "") as decrypted_col
from table

which will output the decrypted data that work fine.

But now I want to create a view on top of this table in which this view will show the decrypted value and view not accept the variable so I hard code into the select statement like this:

create view view_table as 
select 
id, 
encrypted_col,
AEAD.DECRYPT_STRING(
   KEYS.KEYSET_CHAIN("<kms-uri>",  from_base64("<encrypted-keyset>") ),
   from_base64(encrypted_col),
   "") as decrypted_col

from table;

which basically hardcode the value from the declared variable into a select statement

But I got this error

Argument 2 to KEYS.KEYSET_CHAIN must be a literal or query parameter

How should I change this query to make it work with view?

Edit:

I also tried to select it first with this as well (a bit weird)

create view view_table as 
select 
id, 
encrypted_col,
AEAD.DECRYPT_STRING(
   KEYS.KEYSET_CHAIN("<kms-uri>",  (select from_base64("<encrypted-keyset>")) ),
   from_base64(encrypted_col),
   "") as decrypted_col

from table;

but its also not working. (with the same error)

Stephen84s
  • 88
  • 6
emp
  • 602
  • 3
  • 11
  • 22
  • I could make it work through hardcoding key bytes in KEYSET_CHAIN function. I followed this answer [How to use KEYS.KEYSET_CHAIN in BigQuery](https://stackoverflow.com/a/70218254/2824507). It worked but is still hardcoded. – Felipe Crescencio Dec 27 '22 at 18:19

0 Answers0