View Only
  • 1.  Simple column encryption

    Posted Tue March 23, 2021 06:01 PM

    We export data from our DB regularly and now we need to encrypt some columns. Something like

    unload 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.10

    Thanks in advance,

    Snorri Bergmann


  • 2.  RE: Simple column encryption

    Posted Wed March 24, 2021 05:01 AM

    Hello 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_test
    select 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_test
    insert into customer_enc;

    insert into customer2
    select customer_num, lname, decrypt_char(fname_enc,"Kalu knows the solution") from customer_enc;

    select * from customer2;

    Gerd Kaluzinski