Blog
DB2utor

Categories

June 03, 2013

Deciphering Encryption Options

Numerous options are available for implementing encryption in DB2 for z/OS environments. However, each one has its restrictions and limitations. The encryption solution you choose (column, row, built in functions, edit procs,) comes down to what it is you're trying to accomplish.

My preferred encryption option has always been the built-in SQL function, ENCRYPT. In my initial testing I chose a character column to encrypt, and then used this set of SQL statements to test it out:

 

  SET ENCRYPTION PASSWORD = 'ABC’;

  INSERT INTO EMP(EMPNO,FIRSTNME, MIDINIT, LASTNAME)

  VALUES (ENCRYPT('12346'),'TROY,'L', 'COLEMAN') #

 

This generated the following error:

 

DSNT408I SQLCODE = -20144, ERROR:  THE ENCRYPTION IS INVALID BECAUSE THE    

         LENGTH OF THE PASSWORD WASS LESS THAN 6 BYTES OR GREATER THAN 128 

         BYTES                                                             

 

I quickly realized I needed to increase the password from three characters (ABC) to six (ABCDEF). But then I encountered this error:

SQLCODE = -433, ERROR:  VALUE '3C2BD50401F40010D5ABD5AE04B804B8B96180- 

506FFE4F232B640B11451BA29F7C506A24249436CF'X IS TOO LONG   

 

With additional research, I found that the column needs to be varchar. The 6-character column actually needed to be a varchar 64 byte column. This is made up of 6 bytes for maximum length of unencrypted data plus 2 additional bytes to round up to 8-byte boundary. Then there's 24 bytes for the password phrase plus 32 bytes for the password hint.

This experience made me realize that I've been underestimating the critical role database design considerations play when it comes to implementing encryption. For instance, many third-party applications and reporting tools limit the use of DB2 SQL encryption language. Even a basic statement like SET ENCRYPTION PASSWORD might not be allowed. So unless you can get your vendor to add in support for DB2 SQL encryption statements, you should look at other options.

With this in mind, here are some excellent resources on encryption.

* First is this presentation given by Roger Miller at IDUG EMEA 2009. It provides an overview of various encryption solutions, including ICSF, DB2 fieldprocs, DB2 editprocs, user-defined functions, SQL functions. Encryption is also discussed as it relates to DRDA, tape and disk systems.

* The IBM Redbook, Security Functions of IBM DB2 10 for z/OS, also covers an array of encryption options.

* Another IBM Redbook, Securing and Auditing Data on DB2 for z/OS, provides step by step details on implementing an editproc. It also covers key restrictions (e.g., a table that uses an editproc cannot be altered).

* This 2008 article by Ulf Mattsson explains how to encrypt columns, rows, indexes and disk in DB2 for z/OS.

If you use encryption on DB2 for z/OS, please share your experiences in comments.