views:

139

answers:

0

I am trying to create a view to transparently handle encryption of a BLOB column. I am using the approach described here.

Background

NOTE

There was a comment by @tc, asking why I am trying to encrypt this, since the encryption key is then stored (in plain text) in the code. I have a requirement to encrypt the data at-rest. Since the application needs to be able to encrypt and decrypt the data, without human intervention, the key needs to be stored somewhere. This way, it is stored in the application, which is on a different server from the database. This approach seems to appease the compliance folks, but I am open to other suggestions.

So, here is my table definition (I am using $ as the statement termination character):

CREATE TABLE fileAttachmentEncrypted (
    work_item_id integer NOT NULL,
    fileName varchar(100) NOT NULL,
    documentType varchar(100) NOT NULL,
    contentType varchar(100) NOT NULL,
    fileImage BLOB(104857600) NOT NULL, 
    last_update_by varchar(20) NOT NULL,
    last_update timestamp NOT NULL
)$

This table creation statement executes cleanly.

Then, I try to create my view:

CREATE VIEW decryptedFileAttachment AS 
SELECT work_item_id, fileName, documentType, contentType, 
        DECRYPT_BIT(fileImage, 'SUPERSECRETPASSWORD', 'REMINDER'), 
        last_update_by, last_update FROM fileAttachmentEncrypted$

Problem

This results in the following error:

Error: DB2 SQL Error: SQLCODE=-440, SQLSTATE=42884, SQLERRMC=DECRYPT_BIT;FUNCTION, DRIVER=3.50.152
SQLState:  42884
ErrorCode: -440
Error occured in:
CREATE VIEW decryptedFileAttachment AS 
SELECT work_item_id, fileName, documentType, contentType, DECRYPT_BIT(fileImage, 'SUPERSECRETPASSWORD', 'REMINDER'), last_update_by, last_update FROM fileAttachmentEncrypted

What I know so far

According to the DB2 documentation, this means

SQL0440 SQLCODE -440 SQLSTATE 42884

Explanation: Number of arguments on CALL must match procedure.

However, I am not really sure what that means. I tried omitting the password hint as well as the password (setting it for the session using SET ENCRYPTION PASSWORD) for the DECRYPT_BIT call.

One theory I have is that my table definition should not be using a BLOB column. Most of the examples online showing how to implement column-level encryption in DB2 use VARCHAR FOR BIT DATA as the column type that holds the encrypted value. In fact, the DB2 documentation says to use this:

When data is encrypted, it is stored as a binary data string. Therefore, encrypted data should be stored in columns that are defined as VARCHAR FOR BIT DATA.

However, VARCHAR columns are limited to 32,740 bytes. The data that I need to encrypt will be much larger.

On the other hand, this documentation implies that BLOB is a perfectly cromulent column type for holding encrypted data:

DECRYPT_BINARY: The DECRYPT_BINARY function accepts as its first argument an encrypted_data large object of type BLOB or CLOB. You must specify a password as its second argument, unless the SET ENCRYPTION statement has specified as the default for this session the same password by which the first argument was encrypted.

Is this theory correct? Is something else going on here? How can I encrypt my BLOB column? Has anyone else in the SO community implemented column-level encryption in DB2? Were you able to do it on a BLOB column?