How to Apply Encryption Function to a Specific Column

[weilin0323]

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!

[MadhuB · Accepted solution]

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.

[weilin0323]

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!