Posted by I thought it would be relatively easy to investigate encryption for our environment. I was wrong, was just plain confusing. This was not because encryption is complicated per se, but that a DBA really needs to have a good understanding of business needs. If you don’t have this understanding, you can get lost in an array of options.
Are you looking to restrict data access? If this is true, LBAC or Data Masking may be better suited for you. Do you need to encrypt only a column of credit card numbers? How about a few? Maybe a table? What about all tables? A backup? Archive log file? How about application log files? Maybe a flat file meant to be FTP’ed off site?
See what I mean? What seems like a few simple questions can drastically affect scope and price. For the scope of this article, I am going to assume you are truly seeking encryption for data at rest (in flight is a whole other can of worms) and show you a few options. I am not going to advocate for one over another, but give you a starting point for your own research.
DB2’s ENCRYPT/DECRYPT Function
The Encrypt and Decrypt functions have been available since DB2 v7.2 UDB. Where a single password, not related to DB2 authentication, is passed to access encrypted data. You can encrypt a whole column or a cell. The function is called, passing a password, to encrypt and decrypt data as needed.
The benefit of this method is that the functions are included in DB2 at no additional cost. These functions have been used for a while so you are not the cutting edge shop trying to figure this out for the first time. The benefits seem to stop there.
It’s generally agreed that any sort of encryption will have a negative effect on performance. This is one of those cases. This method also older, which means the encryption algorithms are out of date. In doing some research with IBM, a higher level security expert (who will remain nameless) gave me this feedback.
“The ENCRYPT/DECRYPT SQL functions are legacy. I do not recommend them for any serious encryption implementation. They use outdated cryptography, they do not provide good key management, and I don’t think their performance is great.”
This was seconded during a DB2 LUW “Ask the Experts” panel at the IDUG 2014 Technical Conference in Arizona.
So, what is the next option? Two options Walid Rjiabi suggested during the technical panel.
AIX’s Encrypted File System (EFS)
EFS or Encrypted File System, is filesystem level encryption through a key store available starting with AIX v6.1. EFS is a powerful tool because it can protect sensitive data from those with physical access to the server including malicious root.
Just like ENCRYPT/DECRYPT is native to DB2 – EFS is included in AIX, has been available for a while, and has an established a track record … sort of. It is also one of two solutions IBM recommends for encryption over the native ENCRYPT/DECRYPT feature.
EFS was a recommendation to my company via an e-mail conversation with IBM and was confirmed by Walid at the technical conference in open forum.
Here is the catch, although endorsed by IBM we could not find one client that uses EFS for DB2 data encryption. At that time, IBM confirmed that they do not have a client that does (at least in our industry). This means my company would be the trend setters.
At the time of this article, EFS is still being looked at as a serious option for us. However, there is real concern that we don’t know what impact the feature will have performance. We know there will be degradation, but we don’t know how much.
If you are taking a serious look at this option, I would recommend you have a performance monitoring tool similar to DBI’s Brother-Panther or Dell’s DB2 Foglight Performance Analysis tool. Reach for some tool that can monitor at a workload or more granular level to see the effect on specific SQL. My preference is DBI, but use what you have access too.
So this leaves us with another, more expensive, but all-encompassing option.
(Vometric) Data Security Manager / (IBM) Infosphere Guardium Encryption Expert
All hail the all in one solution! At first glance, Guardium Encryption Expert looks like a solid solution for encrypting data at rest. It’s versatile, highly secure, quick to deploy, centralizes key management, provides granulated reporting, provides separation of duties, and is inserted in a way that no application rework is necessary.
Encryption Expert is proven, unlike EFS for DB2, has a small and known performance impact, and is cheaper than some of the compress/encrypt flash memory or SSD solutions.
There really isn’t a catch, but there is something you should know. Inforsphere Guardium Encryption Expert is actually a rebranding and repackaging of Data Security Manager developed by Vometric. It’s the exact same product.
By going with IBM, you will have one stop shopping for support. They will also be well versed in how this integrates and ties into DB2. However, the benefits seem to end there.
IBM will sell you the software and you provide hardware at additional cost. Vometric can provide the software stack like IBM or they can also provide a hardened appliance so there is no monetary layout for separate hardware. At the time of this article, Vometric adjusted its licensing model which seems to be slightly more favorable than IBM’s PVU model.
Which do you go with? I can’t say. As an IBM Champion I am somewhat biased to stay with the company that can provide one stop shopping and knows how this integrates with DB2 in detail. But there is some benefit to having a hardened appliance that essentially “plugs and plays”.
What is the best solution for you?
I can’t recommend one alternative over another. The benefits of one will outweigh another purely because of your specialized environment and budget. Before you move forward in your analysis and proof of concept I encourage you to take a good look at the clarifying questions I asked earlier. Determine your needs and your framework as you look at each solution in more depth. And as you make progress, I would be curious to know what solution worked best for you. Leave a message in the comments.
Michael Krafick is an occasional contributor to db2commerce.com. He has been a production support DBA for over 12 years in data warehousing and highly transactional OLTP environments. He was acknowledged as a top ten session speaker for “10 Minute Triage” at the 2012 IDUG Technical Conference. Michael also has extensive experience in setting up monitoring configurations for DB2 Databases as well as preparing for high availability failover, backup, and recovery. He can be reached at “Michael.Krafick (at) icloud (dot) com”. Linked-in Profile: http://www.linkedin.com/in/michaelkrafick. Twitter: @mkrafick
Mike’s blog posts include:
10 Minute Triage: Assessing Problems Quickly (Part I)
10 Minute Triage: Assessing Problems Quickly (Part II)
Now, now you two play nice … DB2 and HACMP failover
Technical Conference – It’s a skill builder, not a trip to Vegas.
Why won’t you just die?! (Cleaning DB2 Process in Memory)
Attack of the Blob: Blobs in a Transaction Processing Environment
Automatic Storage Tablespaces (AST): Compare and Contrast to DMS
DB2 v10.1 Column Masking
Automatic Storage (AST) and DMS
Reloacting the Instance Home Directory
Informational Constraints: Benefits and Drawbacks
Ember/Michael,
Thank you for this post. This was a nice overview of the encryption options, and very helpful. I do have a question, if either of you care to address it:
Are the ENCRYPT/DECRYPT functions you speak of the same functionality DB2 uses if I create the database with the “encrypt” option? I have a situation where we need to encrypt a card number in a new database. So, I’ve set up a keystore, etc. I’d like to just create the database with encryption and feel that satisfies the at rest enryption requirement. Any guidance is apprecieated.
Thanks,
Brandon
The encrypt option on database creation is brand new with DB2 10.5 Fixpack 5, and was only announced last week. It is different than the options in this blog post in that it represents encryption that is transparent to all applications and that applies both to backups and to the database itself. I don’t have any experience with it yet, but the main questions I’d be asking are:
And yes, I know how to spell “guidance”. Oops.
Oh, man. Now I’m seeing typos all over the place (“appreciated”, “encryption”). Lets just call that a symptom of my brain not able to take any more, after much reading up on encryption. Thanks again!
Very good mentioning of how DB2 encrypts. I was trying to compare the features and benefits of database encryption vs a third party encryption solution, and this article addresses all of my concerns very well.
Thank you!
Hey Michael, this is good information. I am configuring DB2 Native Encryption.
Are you familiar with it? Do you have any information on it?
Unfortunately not much. Most of my shops encrypt at the application level so I never get to “play” with it. I do know there is some drag on performance, so do load testing.
Hello Ember/Michael,
Is there a way to decrypt a backup image to restore on some other server, where the encryption is not enabled ?.