Encryption/Decryption Process for DB2 V6

A. Ward

Encryption/Decryption Process for DB2 V6
We are running DB2 for OS/390 V6. We currently have a need to encrypt
certain columns of data. Other than a fieldproc, is there a way to use
triggers? We could call the encryption routine upon an update or insert
but how could would decrypt the data upon a Select? I've read many
negative things on this list regarding fieldprocs. Is it IBM's intent to
phase these out? Is there something in the near future that will allow us
to do encryp/decrypt without fieldprocs?
Thanks for your input.
A.B. Ward



James Campbell

Re: Encryption/Decryption Process for DB2 V6
(in response to A. Ward)
Angela

You could write an en/decrypt UDF. To invoke on insert/update, use a
trigger like:

CREATE TRIGGER xxx
NO CASCADE BEFORE INSERT/UPDATE OF encrypted-column ON table
REFERENCING NEW AS NEW
FOR EACH ROW MODE DB2SQL
SET NEW.encrypted-column = CRYPTION(NEW.encrypted-column,'ENCRYPT');

I have never written a trigger quite like this. I would be _extremely_
careful because it would take only a minor mistake to encrypt already
encrypted data. Ooops. Test carefully.

To select the value, use
SELECT CRYPTION(encrypted-column,'DECRYPT') FROM table
Note that LOAD will not invoke the trigger - you have to encrypt it
previously.

All in all, a proc is better than a trigger for this.


I must ask 'what are you trying to achieve?' Are you trying to stop:
- someone reading data out of the base VSAM cluster. Because without
additional security on the encryption routine, someone who can read the
cluster might be able to manually call the routine passing it the encrypted
data
- unauthorised people selecting data. Is DB2 security good enough?
- people who don't know the decryption key from reading the data If this, I
would make the application encrypt the data and then store it. That is, all
DB2 sees is the encrypted data.

/* standard disclaimer */
James Campbell
DBA
Hansen Corporation, Doncaster
+61 3 9843 8442
[login to unmask email]

-----Original Message-----
From: A. Ward [mailto:[login to unmask email]
Sent: Thursday, January 11, 2001 4:22 AM
To: [login to unmask email]
Subject: [DB2-L] Encryption/Decryption Process for DB2 V6


We are running DB2 for OS/390 V6. We currently have a need to encrypt
certain columns of data. Other than a fieldproc, is there a way to use
triggers? We could call the encryption routine upon an update or insert
but how could would decrypt the data upon a Select? I've read many
negative things on this list regarding fieldprocs. Is it IBM's intent to
phase these out? Is there something in the near future that will allow us
to do encryp/decrypt without fieldprocs?
Thanks for your input.
A.B. Ward







**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.

www.mimesweeper.com
**********************************************************************



Roger Miller

Re: Encryption/Decryption Process for DB2 V6
(in response to James Campbell)
Hello Beth, nice to hear from you.

There are several possibilities for encryption with DB2. What you choose
generally depends upon what you are trying to protect from and what side
effects you can tolerate. Encryption and general relational function
conflict. Here is how I would put out the primary options.

Possible side effects

A. Encrypted data will not give correct results for comparisons other than
equals or not equals. All greater than, less than and range predicates are
not usable. Functions are generally not usable.

B. While the data is encrypted, the indexes are not.

Places for encryption code to be used:

1. Before data is provided to DB2. Effect A.

2. FIELDPROC. Effect A. Also see other FIELDPROC restrictions.

3. EDITPROC. Effect B. Also check other EDITPROC restrictions.

4. UDF primary usage is expected to be the same as 1.

So, as James started, the objective is the big question. If the objective
is something like protection from access outside of DB2 and protection for
copies of the data, then the EDITPROC has been used at times. If you want
to use the IBM crypto facilities (ICRF and ICSF I think), we have code for
an EDITPROC.

My crystal ball is pretty cloudy today, but I suspect that crypto is getting
more important.

Roger Miller, DB2 for OS/390