4

I was wondering whether it is possible to create UDFs that decrypt values that are inserted as encrypted values inside a table by using a private key. I couldn't really find anything by Googling. The only thing I found is that there might be an option to do that with a JavaScript UDF and CryptoJS but I couldn't understand if it's actually possible to import the CryptoJS module to be used in the UDF. From what I've gathered in the documentation it isn't possible.

Is anyone aware of such feature, possibility or a workaround? Thanks!

David Oha
  • 141
  • 2
  • 8
  • You could check out `crypto.subtle` as described here. At least in the browser it is native. Not sure if Snowflake also supports it. https://stackoverflow.com/a/59194711/4219202 Otherwise the easiest way forward is to use a client like Python that is reading encrypted data, decypts it and writes back plain data. – Rick Jun 10 '20 at 14:40

2 Answers2

2

You don't actually need a UDF to do this. Snowflake supports an encrypt and a decrypt function with a private key. (Technically, this example shows a private passphrase that's converted into a private key. It does support direct key specification too though; read on):

select encrypt('Hello, world.', '531daa2aec446116');  -- Displays as binary jibberish.
select hex_encode(encrypt('Hello, world.', '531daa2aec446116'));  -- Displays as HEX. Convert to hex to store as either binary or string.
create temporary table ENCRYPTED_TABLE(COL1 binary, COL2 string);
insert into ENCRYPTED_TABLE select hex_encode(encrypt('Hello, world.', '531daa2aec446116')), hex_encode(encrypt('Hello, world.', '531daa2aec446116'));
select decrypt(COL1, '531daa2aec446116'), decrypt(hex_decode_binary(COL2), '531daa2aec446116') from ENCRYPTED_TABLE;

If you're looking for a way to decrypt values that another system encrypted, the decrypt functions may or may not work. Snowflake's encrypt and decrypt functions use symmetrical encryption with the AES256 cipher. If the source cipher is AES-256, you may get decrypt to work. If the source cipher is anything else, it won't. You'd probably want to try the ENCRYPT_RAW function, which accepts a key rather than dealing with the complexity of how to turn a passphrase into a key.

https://docs.snowflake.com/en/sql-reference/functions/encrypt.html

Greg Pavlik
  • 10,089
  • 2
  • 12
  • 29
0

You could use the public preview feature External Tokenization.

External Tokenization allows organizations to tokenize sensitive data before loading that data into Snowflake and dynamically detokenize data at query runtime using masking policies with External Functions.

There is even an integration with Protegrity Data Security Gateway.

Currently, Snowflake supports using Protegrity to manage the tokenization of sensitive data across all data stores (e.g. tables, databases, data warehouses) in your organization. When Snowflake calls the external function in the masking policy, a REST API call is made to the Protegrity Data Security Gateway(DSG).

Allen
  • 406
  • 2
  • 8