Administrator > DB2

The Next Step in Security

Safeguarding sensitive data with DB2 UDB encryption

Native Interfaces and Encryption
The previous encryption examples all use SQL. Thats because theres no native (i.e., non-SQL) interface to the encryption and decryption functions. However, the underlying tables neednt be created with SQL; the column being encrypted need only meet the previously discussed requirements.

Database triggers provide a way to make encryption services available without changing all of your applications to use SQL. The Before Insert and Before Update triggers can be defined to intercept the write requests to your database and then encrypt the sensitive data before its passed into DB2. With this approach, the only program that must use SQL to invoke ENCRYPT_RC2 is the trigger program. Both SQL and external triggers can be used.

An SQL trigger is shown below. The protect_id SQL trigger first calls an external user-defined function, get_passwordUDF(), to get the encryption password for the employee table. That password is then used to encrypt the employee ID in the Before Record image that DB2 uses to insert a new row into the employees table. Any native write (or SQL Insert) causes Before Insert to be invoked.

CREATE TRIGGER protect_id BEFORE INSERT ON employees
REFERENCING NEW ROW AS n
FOR EACH ROW
BEGIN
  DECLARE passwd VARCHAR(127);
  SET passwd = get_passwordUDF('EMPLOYEES');
  SET n.empid = ENCRYPT_RC2(n.empid, passwd);
END

SQL views are probably the most common approach to give native programs the capability to decrypt data. Here are two sample views that could be used in this manner:

CREATE VIEW decdata AS
  SELECT DECRYPT_CHAR( empid ) 'empid', empname FROM emp

CREATE VIEW decdataf AS
  SELECT CHAR(DECRYPT_CHAR( empid ),6) 'empid', empname FROM emp

The SQL view (decdata) can be opened as a logical file by the native program. The decrypt column function contained in the SQL view allows the unencrypted version of the column data to be returned to the native program. Decryption only occurs if the native program has set the proper encryption key either by executing the Set Encryption Password SQL statement directly or indirectly by calling another program.

DECRYPT_CHAR returns a variable-length character string value, so the native application must be modified to handle a variable-length string or use an SQL view like decdataf that uses the CHAR function to return a fixed-length character string value. Encrypted columns require a longer column length, so native programs must either decrypt the data or be modified to handle the longer column length.

Summary
The new encryption and decryption functions in V5R3 provide iSeries shops with a simple way to add an extra lock of security around their sensitive DB2 UDB for iSeries data.

However, before implementing column encryption, you must carefully consider how select decryption of encrypted columns in your database will impact your application and business processes. Although this task isnt trivial, given the need for enterprises to encrypt personal and financial data, it is worth the effort.