Encrypting a column in Db2 without modifying the application
ealameida 310002GWAR Visits (1031)
Db2 12 Function Level 505 introduces a new Encryption Built-in Function (BIF) ENCRYPT_DATAKEY that can be used to encrypt data at the column level using the AES algorithm.
Previously, Db2 had the ENCRYPT_TDES function, but it was not widely used or enhanced to support AES because it required the application to manage the encryption key. Encryption itself is easy because algorithms are standardized, even to the point that HW implementations exist, key management, however, is hard. Encryption without key management is like buying a good lock and hiding the key under the mat.
What’s different now? The second argument to ENCRYPT_TDES (the old Db2 encryption BIF) is the encryption key and the second argument to ENCRYPT_DATAKEY is a key label. Why does this make a difference? With an encryption key, once you know the key, you simply use it to decrypt the data. In the case of a key label, knowing the key label is not valuable unless you also have authority to use the key label in RACF (See Steps for RACF-protecting keys and services for further information). Hardcoding a key in an application is a security exposure (as is fetching the key from a table or reading it from a file), but encrypting or decrypting using a key label does not expose the data to an unauthorized user. This means that key labels can be visible in tables, files, or source code… but are worthless to users who do not have access to utilize that key label.
Let’s take a simple example with a table and a query from an end user – to explain how the new capability works.
CREATE TABLE SAMPLE_TABLE (NAME CHAR(20), PHONE_NUMBER CHAR(12));
CREATE INDEX IDX ON SAMPLE_TABLE(NAME);
INSERT INTO SAMPLE_TABLE VALUES ('Ch
INSERT INTO SAMPLE_TABLE VALUES ('Sa
SELECT NAME, PHONE_NUMBER FROM SAMPLE_TABLE;
The result of a recent audit determines that PHONE_NUMBER is personal information and auditors are saying that the column needs to be encrypted.
We can modify the statements above as follows to encrypt PHONE_NUMBER:
CREATE TABLE SAMPLE_TABLE (NAME CHAR(20), PHONE_NUMBER VARBINARY(95)See note 1);
CREATE INDEX IDX ON SAMPLE_TABLE (NAME);
INSERT INTO SAMPLE_TABLE
INSERT INTO SAMPLE_TABLE
VALUES ('Sarbinder', ENCR
SELECT NAME, DECR
It should be noted that while Db2 requires the key label to be specified for the ENCRYPT_DATAKEY function, for the DECRYPT_DATAKEY_xxx functions a key label is not required.
It is also noteworthy that there is one encryption function, ENCRYPT_DATAKEY, that takes an expression of any supported datatype as the first input. However, for decryption, there are eight functions, each specifying an output datatype. For example, DECR
While the above changes do encrypt the data, the application needs to change their SQL to encrypt/decrypt. That may be OK for new applications, but avoiding application changes for existing applications is desireable.
There are various mechanisms that Db2 for z/OS provides that may be used to more transparently encrypt the data.
Method One – VIEW
By changing the DDL to use a view and instead of triggers, we can avoid changes to the INSERT and SELECT statements in our example:
We can change the above DDL so the INSERT and SELECT remain unchanged.
RENAME TABLE SAMPLE_TABLE TO SAMPLE_TABLE1;
CREATE VIEW SAMPLE_TABLE (NAME, PHONE_NUMBER) AS
(SELECT NAME, DECR
CREATE TRIGGER TR1 INSTEAD OF INSERT ON SAMPLE_TABLE REFERENCING NEW AS NEW_DATA
FOR EACH ROW MODE DB2SQL INSERT INTO SAMPLE_TABLE1 (NAME, PHONE_NUMBER)
VALUES (NEW_DATA.NAME, ENCR
The INSERT and SELECT are now the same as they were in the original example:
INSERT INTO SAMPLE_TABLE (NAME, PHONE_NUMBER) VALUES ('Ch
INSERT INTO SAMPLE_TABLE (NAME, PHONE_NUMBER) VALUES ('Sa
SELECT * FROM SAMPLE_TABLE;
Using a view, and an instead of trigger, we can hide the encrypt/decrypt of the data from the application for a simple application. That said, encryption isn’t completely transparent. The results of DESCRIBE will be different because the length of PHONE_NUMBER will be 12 in the case of the base table, but the length attribute returned by the view will be 79.See note 3
One other drawback of using a view is that if you use a predicate on PHONE_NUMBER with the view, such as:
SELECT * FROM SAMPLE_TABLE WHERE PHONE_NUMBER = ‘958-555-5309’ ;
Db2 will re-write this query as:
SELECT * FROM SAMPLE_TABLE WHERE DECR
While this rewrite will enable the query to work without the application knowing the column is encrypted, the predicate will be stage 2. Furthermore, the only way to evaluate the predicate is to decrypt every row and compare it to the literal ‘958-555-1234’. If your query is written as:
SELECT * FROM SAMPLE_TABLE WHERE NAME = ‘Chris’ AND PHONE_NUMBER = ‘958-555-5309’ ;
The precidate on NAME is indexible,and will filter the rows that need to be decrypted. As such you need to understand the SQL that will be used with the encrypted data to understand if a solution based on VIEWs is acceptable.
Method Two – TABLE FUNCTION
A table function can be used to solve the DESCRIBE issue, as well as the predicate issue, but may not be as transparent as a Method One.
CREATE FUNCTION SAMPLE_TABLE_FUNC (INAME CHAR(20), IPHONE_NUMBER CHAR(12))
RETURNS TABLE (NAME CHAR(20), PHONE_NUMBER CHAR(12))
READS SQL DATA
NO EXTERNAL ACTION
SELECT NAME, DECR
With the table function, DESCRIBE now returns the same length for PHONE_NUMER as it would for the original table. Additionally, the predicates for the following query:
SELECT * FROM TABL
are both stage 1. It is possible to create a VIEW on top of a TABLE FUNCTION, which would allow you to use the same SELECT syntax that we started with ( SELECT NAME, PHONE_NUMBER FROM SAMPLE_TABLE; ), but in order to do that, we have to use compound predicates and wild cards that would make the predicates stage 2 (which defeats the purpose of the Table Function).
What about the key label?
Key Labels are protected entities. You have to be authorized to use the key label. In my examples, I didn’t bother trying to hide the key label, I simply hardcoded “MYKEYLABEL”. There may be cases where you need more flexibility and want to use a global variable, host variable, or parameter marker to provide the key label. If that is the case, making encr
With the introduction of the ENCRYPT_DATAKEY and DECRYPT_DATAKEY_xxx functions, Db2 has provided the capability to use key labels to provide key management capability for encryption and decryption at the column level. In this blog, we discussed some of the mechanisms that DBAs can use to enable applications to use this new encryption capability with no, or minimal change to applications. While it may be possible to make encryption completely transparent to an application, that may not be the best choice when it comes to performance. It is important to know not just that data needs to be encrypted, but also how that encrypted data will be accessed to understand the best choices for transparency and performance.
Note 1: The length of an encrypted column uses the following formula to determine the size of the binary field used to contain the encrypted string – ((((length attribute of source data in bytes + 16 - 1) / 16) * 16) + 15-byte fixed header + 64)
Note 2: Deterministic AES 256 algorithm is chosen here to allow equality comparison of encrypted values
Note 3: 79 = 95 - 16(minimum key label header length)
Chris Crone is an IBM Distinguished Engineer for Db2 for z/OS development and Sarbinder Kallar is a software developer for Db2 for z/OS.