z/Data Perspectives: DB2 Encryption Support Keeps Improving

by Craig S. Mullins
November 13, 2007

In this data-breach and regulation-laden world, the topic of data encryption has become increasingly popular. If we encrypt our sensitive data, then only authorized folks who know the decryption key will be able to access it.

So what can we do about encrypting sensitive DB2 data? Well, DB2 9 offers some encryption news, but we need to go back a version to tell the complete story. You see, DB2 supports encryption in V8 through encryption functions that can be coded to encrypt and decrypt DB2 data.

These functions allow you to encrypt and decrypt data at the column level. Because you can specify a different password for every row you insert, you’re encrypting data at the “cell” level in your tables. If you use these functions to encrypt your data, be sure to put some mechanism in place to manage the passwords used to encrypt the data. Without the password, there’s absolutely no way to decrypt the data.

To assist you in remembering the password, you can specify a hint when you encrypt the data. The following SQL example shows an INSErT that encrypts a social security number using a password and a hint:

INSERT INTO EMP (SSN)

VALUES(ENCRYPT(‘289-46-8832’,’TARZAN’,’? AND JANE’));

The password is “TARZAN” and the hint we’ve chosen is “? AND JANE”… so the hint will prompt us to think of Tarzan as the companion of Jane. To retrieve the encrypted data, you’ll need to use the DECRYPT function supplying the correct password. This is shown in the following SELECT statement:

SELECT DECRYPT_BIT(SSN,’TARZAN’) AS SSN FROM EMP;

If we fail to supply a password, or the correct password, the data is returned in an encrypted unreadable format.

The result of encrypting data using the ENCRYPT function is VARCHAR FOR BIT DATA. The encryption algorithm is an internal algorithm that uses Triple DES cipher block chaining with padding and the 128-bit secret key is derived from the password using an MD5 hash.

When defining columns to contain encrypted data, the DBA must be involved because the data storage required is significantly different. The length of the column must include the length of the non-encrypted data, plus 24 bytes, plus the number of bytes to the next 8-byte boundary, plus 32 bytes for the hint.

OK, that’s the V8 stuff, but what about DB2 9? Well, DB2 9 for z/OS offers some nice improvements to encryption support. First, DB2 can take advantage of encryption hardware advances.

CP Assist for Cryptographic Function (CPACF) is available on z990 hardware. CPACF can run on all the CPUs, but remember, this feature is available only on z990 and later machines, not the older z900. The z990 also introduces a PCIXCC card that’s needed for the IBM Data Encryption Tool, but not for the DB2 encryption functions. The IBM Data Encryption Tool (available from IBM at an additional price) offers encryption for DB2 tables at the table level, whereas the encryption functions (free with DB2) offer encryption at the column level.