How to encrypt and decrypt data in Netezza

Answer

Netezza has encrypt and decrypt functions in the SQL Extension Toolkit.

To use this functionality you need to install IBM Netezza SQL Extensions as follows:

1. Download IBM Netezza SQL Extensions from FixCentral (http://www-933.ibm.com/support/fixcentral/)
Product Group: Information Management
Select from Information Management : IBM Netezza Applications
Installed Version : SQLEXT_X.X.X
Platform: Linux

2. Upload the package to the Netezza appliance host somewhere under /nzscratch and extract the nested tar'd and compressed files, and then as the nz user run the install executable for each database where you wish to encrypt data. For example:
    $ tar -xvzpf sql-ext-1.6.0.2.tar.gz
    $ tar xvzf sqlext.package.tar.z
    $ tar xvzpf libnetcrypto-1.6.P-2.tar.gz
    $ cd libnetcrypto/1.6.P-2
    $ ./install -d dataBaseName -u admin  -W admin_password  

    Installing libnetcrypto to default schema of database dataBaseName in unfenced mode: .................................................. Done


Usage of the encrypt and decrypt functions is demonstrated in this example

    $ nzsql -db dataBaseName -u admin -pw admin_password

     => create table enctest
           (raw_data varchar(10),
            encrypted_data varchar(10));

     => insert into enctest values (
           'AbcdEF', encrypt('AbcdEF', 'myencyptpassword', 0));

-- Check that the encrypted data is not readable

    => select * from enctest;

    RAW_DATA  | ENCRYPTED_DATA
    ----------+--------------------------------
    AbcdEF    |°c³$j
   (1 row)

-- Confirm that encrypted data decrypts back to match the original data

   => select raw_data,
            decrypt(encrypted_data, 'myencyptpassword', 0) as decrypted_data
        from enctest;
 
   RAW_DATA  | DECRYPTED_DATA
   ----------+--------------------------------
   AbcdEF    | AbcdEF