We export data from our DB regularly and now we need to encrypt some columns. Something likeunload to data.unl select col1, col2, crypt(col3), col4 ....This doesn't have to be super secure and the columns we encrypt (char and int) are rather short so we'd like to keep the encrypted string as short as possible. The function must also be NOT VARIANT (always returns the same output for the same input). Has anyone done something similar? We are using 14.10Thanks in advance,-Snorri
I think you will have to use a temporary table for the load of the data.
Here a small test that works:
set encryption password "Kalu knows the solution";unload to /tmp/kalu_testselect customer_num, lname, fname, encrypt_aes(fname)from customer;drop table if exists customer_enc;create temp table customer_enc(customer_num int,lname varchar(128),fname varchar(128),fname_enc varchar(128)) with no log;drop table if exists customer2;create table customer2(customer_num int,lname varchar(128),fname varchar(128));load from /tmp/kalu_testinsert into customer_enc;insert into customer2select customer_num, lname, decrypt_char(fname_enc,"Kalu knows the solution") from customer_enc;select * from customer2;