Get the nitty-gritty on tools for encrypting data at rest and in motion
With every release of the IBM i operating system, IBM has been giving us new and better tools to protect our sensitive data. Releases 6.1 and 7.1 were no exceptions, and the biggest jewel is in the latest 7.1 update. Join me as I discuss some of the historical barriers you might experience in upgrading to 7.1, describe some of the architectures that surround encryption, explain the 7.1 features that help you encrypt data at rest and in motion, and tell you why you should consider the jump to 7.1 right now.
Why We're Slow to Do OS Upgrades
Humans are good at avoiding pain. Many of us have learned exactly how much pain can be involved in upgrading to a new version of an operating system when there are still significant bugs to be worked out. If you're like me, you've lost hours or days fighting fires after an OS upgrade done too early in the cycle. This pain isn't unique to the IBM i platform; I've had this experience on Microsoft Windows, too. We've all learned to be cautious about upgrading any OS too soon after a new release. In this respect, the safest course would seem to be the slowest possible upgrade path. I know a lot of IBM i shops that upgrade their OS only when IBM is due to stop supporting it.
I totally understand. My personal OS upgrade disaster happened in the early 1980s. I set out on a Friday night to upgrade an IBM S/36 running MAPICs for order processing and inventory management. A new OS version was available, and a new MAPICs version was ready to go. "Why not upgrade?" I said to myself in one of my more naive moments. "Let's get this thing done!"
By Saturday afternoon, the backups were complete, and I started the upgrade. "This is a walk in the park," I was saying to myself.
By Saturday evening, my world was upside down. Things weren't going well, to say the least: weird messages on the console, unreadable diskettes (yes, diskettes—really big ones!), and a variety of other symptoms inexplicable to a novice IT guy. I was in a world of hurt and needed help. My company wouldn't be in business Monday morning if things didn't get straightened out fast.
Pretty soon I was calling IBM for help—one desperate call after another. Eventually, I was connected to an IBM service manager in Reno. He had some replacement diskettes that I badly needed. Would he help me? Yes, he would meet me halfway. Starting from a small winery near Grass Valley, California, I jumped in my car for an after-midnight rendezvous on snowy Donner Pass.
Because of that good-hearted IBMer, I survived that near disaster, but it left indelible marks in my memory. I would never upgrade a system until the absolute last minute. That was my mantra. And that lasted for decades—until now.
There are really good reasons to put aside your fear of a system upgrade to 7.1. Like me, you can get over that upgrade phobia if the benefits are great enough. 7.1 has been out for a while, and its security benefits make upgrading worthwhile. Let's dive into the details.
OS Upgrades and Vendor Support
On top of their normal reluctance to upgrade, some IBM i customers have trouble getting their software vendors to support the latest version of the OS. This upgrade resistance seems to be especially true of smaller software vendors that have very specialized solutions. These vendors are often infected with the same lack of enthusiasm to upgrade as the rest of us, and it affects their customers.
That's unfortunate because IBM does such a good job of making early releases available to its software partners. I've been working with the IBM i developer support team for over a decade, and I've always found them to be helpful in early release testing. Furthermore, IBM's hardware program for software vendors is exceptional. You can get a system at a steep discount, or you can use a virtual loaner to test new IBM i releases.
If you find that your software vendor isn't yet supporting 7.1, you should have an honest discussion with that provider about getting up-to-date. 7.1 has been out for more than two years, and there are few good excuses not to provide support. It would be a shame for you to miss 7.1's security benefits because a software vendor is dragging its feet.
Compliance and Encryption
Why have encryption and security become so important? According to a recent study by GFI Software, 44 percent of US businesses report that their networks have been breached because of malware-laden spam. The number of data breaches has been rising every year for the past several years, and the attacks are getting much more sophisticated. Accordingly, compliance regulations and breach-notification laws are getting much stricter on the issue of encrypting sensitive data. Your company probably falls under several compliance requirements mandating that it protect data, and the financial penalties for data loss are getting harsher. Here are some examples of compliance regulations that might affect your organization:
- PCI Data Security Standard—if you accept credit or payment cards
- HIPAA/HITECH Act of 2009—if you handle patient medical information
- Sarbanes-Oxley Act (SOX)—if you're a publicly traded company
- FERPA—if you're an educational institution
- GLBA/FFIEC—if you're a bank or financial institution
- State privacy laws—if you have a pulse (i.e., everyone)
Across the board, all these compliance regulations are putting more attention on encryption and having a big impact on companies of all sizes. I can't think of any organization that doesn't handle sensitive information at some place in its IT systems and databases.
Almost all of us in the IBM community are affected by multiple compliance regulations. So the encryption features in 7.1 are starting to look pretty good. Let's turn our attention to automatic encryption technologies, which hold a lot of promise to make encryption easier.
Automatic Encryption with Transparent Data Encryption
Transparent Data Encryption (TDE) is a method of whole-database encryption implemented at the database level. All the database tables and objects are encrypted, and pages are decrypted as needed. The nice thing about TDE, from a developer's point of view, is its ease of use. There's no need to do any programming to activate the encryption facility; you typically just create some encryption keys and turn on the TDE encryption facility.
Though new to IBM i, the TDE concept has been around for years. TDE is implemented on Microsoft SQL Server 2008 Enterprise and higher. Users of Oracle's 11g Enterprise Edition with Advanced Security also have been using TDE for years.
The beauty of TDE is its simplicity; the downside is the lack of scalability. Very large databases perform poorly with TDE, and some Microsoft and Oracle customers can't use TDE for this reason.
On IBM i, we have no option for TDE; DB2 for i simply doesn't support it. The closest we get is iASP disk encryption, but this is disk-level encryption that just happens to cover any database that might be on the disk. It's not the same thing from an implementation standpoint or a compliance standpoint, and customers report troubles with iASP encryption performance.
Automatic Column-Level Encryption: Two Architectures
Put on your magic hat for a moment and imagine that you're a database developer who has to implement automatic encryption at the column level for key (index) fields. You might say to yourself, "Whenever someone wants to read a value from a table using an index over the encrypted values, I'll simply decrypt the entire index and then read it. That will be elegant and simple, and I won't need to worry about that nasty encrypted data that might be in who knows what order."
So you've taken probably the easiest path to automatic column-level encryption. But you'll soon run into a wall (more on that later).
Another database developer might put on the magic hat and arrive at a different approach: "Whenever someone wants to read a value from a table using an index over the encrypted values, I'll simply encrypt the search value and then read the index using the encrypted value. That way, I need to encrypt only one value, and I bet I can make this thing really fast."
So that developer has taken a more difficult path to automatic column-level encryption, but it's an approach that will work on really large databases. That developer, however, will also soon run into a wall (more on that later, too).
OK, you can take off your magic hat now. These are the two basic approaches, illustrated in Figure 1, that database developers have taken with automatic column-level encryption. Let's look at some examples of these approaches.
Figure 1: Two possible approaches to automatic column-level encryption
Decrypt and Then Process
If you're like that first developer and decided to decrypt the entire index before accessing it, , you probably have a lot of experience working with Microsoft SQL Server. This approach, which Figure 2 illustrates, is the one that Microsoft SQL Server takes with Cell-Level Encryption (Microsoft uses the term "cell" interchangeably with "column"). So, if you're a user of SQL Server 2008 Enterprise (or later), you have access to Cell-Level Encryption, and this is how it works. Whenever you perform a SELECT statement that uses an encrypted column that is an index, SQL Server decrypts the entire column before attempting to satisfy the SQL request.
Figure 2: The Decrypt and Process model of automatic column-level encryption
As you might guess, the larger the database, the more time and effort (i.e., CPU cycles) it takes to decrypt all that data. In fact, the performance overhead is the biggest downside to this approach. However, you might have encrypted indexes that are rarely used, in which case this approach can work well. But, when the database is large, you shouldn't deploy an encrypted index that is used frequently by a lot of users.
And that's the wall you run into. This model of column-level encryption works well with small to mid-sized database tables but is unsuitable for large database tables—the kind we tend to have on the IBM i platform.
Process While Encrypted
If you're like that second database developer, you decided not to decrypt that entire column in order to process it. Instead, you decided to parse that SQL SELECT statement, encrypt the search value, and then perform an index search against the encrypted values. If you're like this database developer, you might just be working at IBM. This approach, which Figure 3 illustrates, is exactly the one that the database developers at IBM Rochester in Minnesota took when they implemented Field Procedure (FIELDPROC) automatic encryption in IBM i 7.1.
Figure 3: The Process While Encrypted model of automatic column-level encryption
Those IBM developers had some challenging work to do in parsing every SQL (or RPG or COBOL or you name it) statement to encrypt search values, but they rose to the challenge, and the result is speedy performance. You have really big data? No problem! We can get to that data really fast!
However, what if you want to read a range of encrypted values? Well, now you've run into our special wall on the IBM i platform. Encrypted values don't obey any sort order (if they did, it would mean they're not actually encrypted). Consequently, if you have to read a range of values, you're going to have a harder time getting that done. That’s the special challenge that we, as IBM i developers, have with DB2 FIELDPROC on the IBM i platform: Encrypted indexes may not work the way we want, and we might have to do some development to work around this problem. But FIELDPROC's overall benefits are excellent, and most IBM i users will be happy with this implementation.
DB2 FIELDPROC Encryption in 7.1
FIELDPROC automatic encryption is based on the Process While Encrypted model. You can think of FIELDPROC as a column-level exit point implemented in the DB2 database. And that's exactly how it works: After you activate the exit point, the database calls your program anytime there's a read, insert, or update operation—making it perfect for automating encryption tasks.
FIELDPROC has been a part of DB2 on the IBM System z mainframe for a few years. It is, however, new on IBM i since its introduction with release 7.1. FIELDPROC is unavailable on earlier IBM i releases. (See "Automatic Encryption Before 7.1," below, for information about some limited automatic encryption options available in 5.4 and 6.1.)
How do you implement this FIELDPROC capability? Registering a FIELDPROC exit program on a database file is easy. You need a SQL statement to alter an existing table, or you can define the FIELDPROC program when creating a new table. You can also use SQL to remove a FIELDPROC exit point. Here's an example:
ALTER COLUMN cardno
There's no way to activate the FIELDPROC exit point through DDS, which has confused some database developers. Can you use FIELDPROC exit points on databases created with DDS? The answer is yes, but you have to add and remove exit point programs using SQL; there's no DDS attribute that implements the FIELDPROC exit point.
So, what else do you have to do to use FIELDPROC for automatic encryption? Well, you need to write that exit point program. As with other IBM exit points, the burden of writing a FIELDPROC exit point falls on you, the developer, or on a software vendor. IBM doesn't provide the actual exit point program, although it does provide some examples in the 7.1 InfoCenter. Search on "FIELDPROC" to find the relevant information. Be aware that the logic in these exit points is fairly complex, and you need to be familiar with C language structures and concepts before you start.
Besides the actual complexity of FIELDPROC exit programs, the other big challenge is encryption key management. The topic of encryption key management is beyond this article's scope, but if you'd like an overview, you can watch a free 10-minute video I recently produced titled "Configuring FIELDPROC for Automatic Encryption and Key Management." The video demonstrates how to enable automatic encryption and key management on IBM i.
There's an important additional consideration: Before you start your FIELDPROC project, you should be knowledgeable about compliance regulations regarding encryption key management. The PCI Security Standards Council website has a lot of information about encryption and key management and is a good place to keep abreast of compliance laws.
Every automatic encryption technology has strengths and weaknesses, and FIELDPROC is no exception. The most obvious limitation in FIELDPROC has to do with encrypted columns that are used to index data. Because FIELDPROC uses the Process While Encrypted model, the sort order of encrypted data won't be what you expect. No encryption method preserves the sort order of the original data. Thus, reports and displays that use encrypted columns as an index won't operate the way you expect. This limitation may be a major obstacle for you, it may be a minor inconvenience for you, or it may affect you not at all.
There are some other database and operational limitations inherent to FIELDPROC, but in my opinion they're manageable. If you get past the encrypted index issue, you're probably clear for takeoff with FIELDPROC.
OpenSSH in 7.1
Let's turn our attention to using encrypted file transfer to encrypt data in motion. IBM i got support for SSL FTP (FTPS) a few releases back. We also have the Secure Shell (SSH) implementation in the OpenSSH application, which is an IBM no-charge licensed program feature, and this has been around for a while, too.
IBM tends to provide updates to the OpenSSH application with new releases of IBM i. Release 7.1 was no exception to this pattern. In 7.1, we got access to version 4.8 of OpenSSH, and we can expect new OpenSSH versions through IBM's technology refresh program.
SSH is becoming an important arrow to have in your security quiver! I've been seeing a lot more adoption of the Secure File Transfer Protocol (SFTP) by financial institutions over the past few months. Many companies are even migrating away from FTPS to the SSH SFTP.
SSH offers a firewall-friendly and secure encrypted implementation of file transfer to protect your data as it moves over the Internet or internal networks. SSH is based on well-accepted encryption schemes and implements PKI-based encryption keys. So, what are the challenges? I think there are two:
- Automating secure file transfers
- Keeping up with the latest security patches
Automating SSH SFTP transfers can be a big challenge. SSH is a Unix-type application that has been around a long time, and it implements a command-line model for the user interface. That's acceptable if you want to do an occasional file transfer. You can fire up QShell or the PASE command-line facility and transfer the file. But many IBM i shops have hundreds or even thousands of files to transfer every day. If you have serious automation needs, you'll either become really good at shell scripting, or you'll turn to a software vendor for an automated solution.
Because of the IBM strategy of delivering updated versions of SSH with new releases of IBM i, I think you should consider moving to 7.1 as soon as possible (if you're not already there). You'll get the latest version of OpenSSH with the latest security patches.
Rise to the Challenge with 7.1 Tools
I hope this article has helped you understand the security architectures for automatic encryption, IBM's implementation of FIELDPROC in 7.1, and the SSH OpenSSH facility to encrypt files in transit. These are important security features that you must have as you move forward in today's IT world. A typical IBM i shop performs an OS upgrade only every two years. But the security landscape changes rapidly and constantly—don't be without the tools that IBM i 7.1 provides to help you keep up!
Automatic Encryption Before 7.1
Was there any automatic encryption before IBM i 7.1? Well, sort of. You could perform automatic encryption with SQL views and Instead Of triggers. But you couldn't perform automatic decryption. SQL views with Instead Of triggers are not updatable, so they don't provide a complete system for automatic encryption. I remember how disappointed I was to learn that the hard way.
IBM i 5.4 and 6.1 customers have limited options for encryption. Because of the limitations of SQL views and triggers, most customers opt to modify their RPG or COBOL code to call encryption and decryption software libraries. That task is fairly simple once you get the idea, but for some IBM i customers, it's daunting. I know one customer who has 300 tables spread over more than 30 applications and about 25,000 RPG programs! Now that's overwhelming.
I'll bet FIELDPROC encryption in 7.1 is looking really good to that customer right now.