The encrypt() and decrypt() functions

Encryption is the process of transforming data to maintain its secrecy; the data can be read (unencrypted) only if the recipient has the required key. The IBM® Netezza® implementation uses symmetric encryption, also known as private or secret key encryption, because the same secret key is used to encrypt and to decrypt data. This means that this secret key must be made available on any server that is decrypting previously encrypted data. You can choose which symmetric encryption algorithm the function uses to encrypt/decrypt the data, either Advanced Encryption Standard (AES) or RC4.

Private key encryption is more secure than public key encryption because all public key encryption schemes are susceptible to brute force key search attacks. But private key encryption depends on maintaining the secrecy of the key, so periodically change the private key and take steps to ensure that it cannot be discovered in use, in storage, or in distribution (see the description of the key argument later in the topic for Netezza-specific security recommendations).

Description

varchar = encrypt(varchar text, varchar key [, int algorithm 
          [, varchar IV]]);
nvarchar = encrypt(nvarchar text, nvarchar key [, int algorithm 
          [, varchar IV]]);

varchar = decrypt(varchar text, varchar key [, int algorithm 
          [, varchar IV]]);
nvarchar = decrypt(nvarchar text, nvarchar key [, int algorithm
          [, varchar IV]]);

The text value specifies the value to be encrypted/decrypted.

SELECT decrypt(a.value, b.key) FROM my_table a, my_keys b WHERE 
b.key_id = 1;

RC4, although the most widely used encryption algorithm (used for example by SSL and WEP), is not cryptographically secure and is vulnerable to attacks.

The Advanced Encryption Standard (AES) is the encryption standard adopted by the United States government and is required for all classified information. The three versions of AES differ only in the design and strength of the key lengths. While all three key lengths are sufficient to protect classified information up to the SECRET level, TOP SECRET information requires the use of 192 or 256 key lengths.

The IV value specifies the initialization vector used to encrypt or decrypt the value. You can specify the IV only when using the AES algorithms. (The RC4 algorithm does not use the IV value.) If you do not specify IV, the encrypt() and decrypt() functions use a constant IV value.

Encryption with OFB mode requires the IV and key to be 128 bits. As a best practice, specify 128-bit values for key and IV (if used) so that the system uses your input values. If a value is less than or greater than 128 bits, the system uses an expansion or derivation mechanism to adjust the value as follows:

• For keys or IVs that are smaller than 128 bits, the system duplicates the value until the key or IV reaches the correct length.
• For keys or IVs that are larger than 128 bits, the system creates a 128-bit value by using a logical OR (XOR) of the first 128 bits of the value with the remaining bits of the value.

select encrypt(encrypt('abcde', 'k1', 1), 'k1', 1);
  encrypt
 ---------
  abcde
 (1 row)

Returns

select encrypt('123456',100,0);
 ENCRYPT
---------
 ÉôC¥Ñ
(1 row)

 select encrypt('Netezza Systems','NPS001534',1,'MY_IV is IBM001');
     ENCRYPT
-----------------
 �Ná•▒7Þ¢bhñæ
(1 row)

Key and IV Generation

You can use the hash() and rawtohex() functions to generate keys and IVs. The hash() function returns a 128-bit, 160-bit, or 256-bit hash of the input data, depending on the algorithm input for the function. The following example shows how to create a 256-bit key for AES encryption. In the example, the MYKEY output is intentionally truncated using "..." characters to display within the page boundary.

create table impData (cname varchar(35),cid varchar(20));
CREATE TABLE

insert into impData values('Netezza Systems','CNZ0001SYS');
INSERT 0 1

create table securedImpData as select cid, rawtohex(hash(cid,2))
 as mykey, encrypt(cname, rawtohex(hash(cid,2)),2) as ENCRYPTED_CNAME from impData;
INSERT 0 1

select * from securedImpData;
    CID     |                    MYKEY                        | ENCRYPTED_CNAME
------------+-------------------------------------------------+-----------------
 CNZ0001SYS | 8D812C9E8DB362FCB20E...6A00B564175B92762B1A3520 | Ã4W§
      wÂÃð
ŸÃ
(1 row)

create table decryptedData as select CID, decrypt(ENCRYPTED_CNAME,
MYKEY,2) as DECRYPTED_CNAME from securedImpData;
INSERT 0 1

select * from decryptedData;
    CID     | DECRYPTED_CNAME
------------+-----------------
 CNZ0001SYS | Netezza Systems
(1 row)