Thought most of us who are working, as application programmer may never use this feature of DB2 at a program level it is an interesting upgrade in DB2 V8 and is worth understanding.
DB2 provides encryption and decryption of data at a column level and provides a number of built in functions to facilitate this.
- ENCRYPT Encrypt data at column level
- DECRYPT_BIN Return decrypted data in VARCHAR FOR BIT DATA format
- DECRYPT_CHAR Return decrypted data in VARCHAR format
- GET HINT Return hint value for the password used
Before you use the encryption commands on a column, the column must be defined as a VARCHAR and the length depends on the password and password hint. The below example shows how a 6 byte VARCHAR with password and hint must be defined as a 64 byte field for using encrypt function
- Maximum length of un-encrypted data – 6 Bytes
- Number of bytes rounded up to 8-byte boundary – 2 Bytes
- Encrypted password phrase – 24 Bytes
- Optional password hint – 32 Bytes
- Total Length Needed – 64 Bytes
The password is character string, which is 6 to 127 bytes long, and it is case sensitive and a hint that is 32 bytes long. There are two ways in which a password can be set.
SET ENCRYPTION PASSWORD statement will set the password for all the rows or an INSERT with ENCRYPT option can be used to have separate password for each row.
CREATE TABLE EMPLOYEE_DATA
( EMP_ID VARCHAR (20) FOR BIT DATA
EMP_NAME VARCHAR (40)
) IN DB001.TS001;
SET ENCRYPTION PASSWORD = ‘JAN2015’ WITH HINT =‘MMYYYY’;
This would set the default encryption password as ‘JAN2015’ and HINT as ‘ MMYYY’.
The below Insert command would cause the EMP_ID to be encrypted with decryption password as “JAN2015″
INSERT INTO EMPLOYEE_DATA
VALUES (ENCRYPT (‘IN_001’),‘RAMESH’)
To specify a separate password other than the one set by the SET ENCRYPTION PASSWORD command , we can use the below INSERT
INSERT INTO EMPLOYEE_DATA
VALUES (ENCRYPT (‘IN_002’,’09-09-1988’,’DOB’), ‘SURESH’)
This would cause the employee id ‘IN_002’ to be to be encrypted and stored with a password as ’09-09-1988’ and the password hint as ‘DOB’
To retrieve encrypted data, use the DECRYPT function with the appropriate password . For the decryption functions to return the original value, the encryption password must be set to the same password value used to encrypt data
he password value used for the decryption process can be specified on either the Set Encryption Password statement or the decryption function
SET ENCRYPTION PASSWORD = ‘JAN2015’
SELECT DECRYPT_CHAR (EMP_ID)
FROM EMPLOYEE_DATA
WHERE EMP_NAME = ‘RAJESH’
DECRYPT function has three parameters, first parameter, which identifies the value that must be decrypted, second to the password and the third is an integer value that can be used to specify the CCSID value for the character string value produced by DECRYPT_CHAR and DECRYPT_DB.
Only the first parameter, which identifies the value that must be decrypted, is mandatory. Here is another example of using the DECRYPT with the password
SELECT DECRYPT_CHAR (EMP_ID,’JAN2015’)
FROM EMPLOYEE_DATA
WHERE EMP_NAME = ‘RAJESH’
The GETHINT function can be used to retrieve the password hint value stored
SELECT GETHINT (EMP_ID) FROM EMPLOYEE_DATA
If the password supplied is wrong or not given then the data is returned in encrypted format and is unreadable. Below are few of SQL codes you might encounter while using ENCRYPT and DECRYPT functions.
- -20143: Function failed as password is not set
- -20144: Password length invalid
- -20146 Decryption failed as data is not encrypted
- -20223: Encrypt function failed
If you have any question on DB2 encrypt/decrypt function please post in the comments session below and we will be happy to get back to you.
Recent Comments