topic Re: How to Apply Encryption Function to a Specific Column in Data Engineering

How to Apply Encryption Function to a Specific Column

weilin0323 — Wed, 22 Jan 2025 06:18:34 GMT

Hello!

I would like to apply a function to encrypt a specific column. The UDF is as follows:

DROP FUNCTION IF EXISTS EncryptColumn; CREATE FUNCTION EncryptColumn (key_name STRING, encryptcolumn STRING) RETURN base64(aes_encrypt(encryptcolumn, key_name, 'GCM', 'DEFAULT', 'Some AAD'))

The parameter 'key_name' is the encryption key, and 'encryptcolumn' is the column to be encrypted.

I tried using the following:

ALTER TABLE table_name ALTER COLUMN column_name SET MASK EncryptColumn(key_name, column_name)

However, this approach seems to accept only one parameter.

Therefore, I would like to ask if there is any way to apply a function to encrypt a column in the Databricks UC architecture?

Thank you for your response!

Re: How to Apply Encryption Function to a Specific Column

MadhuB — Wed, 22 Jan 2025 17:59:35 GMT

Hi @weilin0323

Have you tried the built-in function? Refer to below encryption code snippet.

create table if not exists catalog.schema.testencryption( id int, name string, tss string ); insert into catalog.schema.testencryption values (1,'sam','123-123-123'), (2,'john','456-456-456'), (3,'pat','789-789-789'); SELECT id, name, base64(aes_encrypt(tss, 'sample_key_16by.', 'GCM')) AS encrypted_tss FROM catalog.schema.testencryption;

without encryption:

with encryption:

Please let me know if there is anything else. Otherwise, please mark it as a solution.

Re: How to Apply Encryption Function to a Specific Column

weilin0323 — Thu, 23 Jan 2025 00:46:24 GMT

Hi @MadhuB,

The method you provided is feasible, and I later finded other ways to apply UDF:

UPDATE table_name SET column_name = EncryptColumn(key_name, column_name)

Thank you!