Townsend Security Data Privacy Blog

The IBM i DB2 database team implemented column level encryption through Field Procedures (FieldProc) in release 7.1 of the IBM i operating system. It was a great step forward for those IBM i customers who need to encrypt sensitive data to meet compliance regulations and to improve overall security. With release 7.1 it was now possible to implement DB2 encryption without modifying user applications.

Prior to this enhancement to DB2 in release 7.1, implementing encryption could be a laborious process of modifying applications and performing extensive regression testing. This approach did not work well with some types of fields (date, time, etc.) and many IBM and third-party query utilities just did not work correctly. So the DB2 enhancement for Field Procedures was a great step forward.

While FieldProc worked well with native SQL applications in release 7.1, there were limitations for older RPG and COBOL applications, and many IBM query utilities did not work correctly with encrypted indexes. Many IBM i customers use IBM and third-party query programs for rapid development of reports and displays of data. Some customers that I’ve talked to have thousands of queries in their application mix, so limitations of IBM query with FieldProc represented an insurmountable challenge for many. When FieldProc was used to encrypt an index or key field, queries just would not work correctly and data was missing or out of order in reports and displays.

But there is some good news!

Starting with the 7.2 release of the IBM i operating system, many of the IBM query applications were updated to work with the native DB2 SQL Query Engine (SQE) by default. The SQL Query Engine has never had a problem with encrypted indexes. This means that the IBM query applications now work seamlessly with data that is encrypted with FieldProc in DB2. You can fully deploy column level encryption across multiple index columns with FieldProc, and your queries will work fine.

Many IBM i customers experimented with FieldProc in the first release in version 7.1 of the operating system and decided to take a pass. If you had that experience it is time to take another look at DB2 FieldProc encryption. The current version of the IBM i operating system is 7.3 and most IBM i customers have upgraded to this release. You now have good support for IBM queries, the SQL Query Engine, and DB2 FieldProc encryption.

While some challenges remain for older OPM and ILE RPG applications, we’ve been able to help a number of customers meet these challenges.

Encryption of data is a core part of a defense-in-depth strategy. We have to do a lot of things to protect our IBM i data, and one of those things is to encrypt the data at rest with industry standard encryption algorithms. DB2 Field Procedures provides the path to achieving this.

To read more about IBM i support for SQL Query Engine in query applications such as RUNQRY, OPNQRYF, and others, see this link.

Our Alliance AES/400 Encryption solution provides full support for DB2 Field Procedures, is easy to deploy, and affordable for every IBM i customer. 

For industry standard encryption key management you can deploy our Alliance Key Manager solution which is seamlessly integrated with DB2 Field Procedure encryption.

Patrick

This year we undertook a survey of organizations to determine how well they are doing in deploying encryption to protect sensitive digital assets. We were curious to see the level of progress organizations were making on this core part of a defense in depth strategy. Because we have information about both IBM i and non-IBM i users, we also wanted to see if there were differences based on the platform they used for their critical applications.

We received responses from over 300 technology users. We felt it was a large enough response to allow us to make some generalizations.

The results were shocking.

Approximately 80 percent of our Windows and Linux respondents had taken some concrete steps to implement encryption to protect sensitive data. In most cases they still had a ways to go to fully protect sensitive data, but that was a lot more progress than I would have imagined. We did not try to distinguish between the particular database in use to store data, but we know that it spanned commercial databases (SQL Server, Oracle, etc.) and open source databases such as MySQL, MongoDB and others.

More Windows and Linux users had made progress with encryption than I would have guessed (sorry).

The real shocker was how few IBM i organizations had made steps to deploy encryption. Only about 6 percent of IBM i users had made any progress in deploying encryption as a part of their security defense in depth. And many of these IBM i users said that they had no plans at all to use encryption for security.

My surprise arises from the fact that I think of IBM i users as generally being more diligent and deliberate around sound system management and security practices. But clearly this is not the case. So what could explain this lapse on the part of IBM i users?

We have some comment data from the survey that points to a general view that IBM i users think their systems are more secure than other platforms. This view is probably a result of IBM’s early investment in security on the platform, and historical messaging about this. Of course we know now that the IBM i platform is subject to data breaches in the same ways that Windows and Linux are, but I believe that this outdated view of the security of the platform now works against IBM i users and leads directly to avoidable data breaches.

Unfortunately, I think there is another source for this lag in security by IBM i customers. There are still too many IBM employees and IBM consultants carrying the message to customers that their systems are more secure than they actually are. I even read a recent comment by a senior IBM engineer that IBM customers should not give much attention to encryption of data on their systems. They should, instead, put more attention on access controls and system security.

This unfortunate message and the myth that the IBM i platform is so secure that you don’t need to worry about encryption is still an unfortunate reality in this community. We know that IBM i users have lost data in breaches. We know that the IBM i server can be breached through weaknesses and vulnerabilities in adjoining networked PCs and servers using classic hacking techniques. A poor implementation of a defense in depth strategy leaves IBM i customers exposed. Because the IBM i server often hosts many back-office applications with rich sources of sensitive data, it is an especially egregious lapse of security.

As a community, IBM i users must do better in deploying a proper defense in depth for their sensitive data. And hopefully thought-leaders inside IBM and outside IBM will recognize the danger of overstating the platform’s native security.

Patrick

Excerpt from the eBook "IBM i Encryption with FieldProc - Protecting Data at Rest."

FieldProc Architecture

FieldProc is a type of column-level exit point that is implemented directly in the DB2 database. As is typical with any of the other IBM exit points, IBM provides the architecture for the exit point to invoke a user application, but IBM does not provide that application. Customers or vendors can create a FieldProc application based on the documented architecture of the exit point. Townsend Security is one vendor who provides such software.

A note on terminology:
We will use the term “column” when referring to a field in a file, and we will use the term “table”
to refer to a physical file. For the purposes of discussing FieldProc these terms are interchangeable and we will use the more modern terms “column” and “table”. In a similar way we will use the term “index” to refer to a column that is either a primary or secondary key. A secondary key would include a key de ned in a logical file.

The exit point architecture is very simple. There are only two commands and three functions that are supported. The two commands are:

• Start FieldProc
• End FieldProc

The three functions that are handled by a FieldProc program are:

• Initialization
• Encode (Encrypt)
• Decode (Decrypt)

When FieldProc is started on a column the FieldProc program is called for Initialization, and then called for each row in the table to provide for the encryption of the column. When FieldProc is ended the FieldProc program is called for each row to decrypt the data. All other normal read, change, and insert operations call the FieldProc program to provide for encryption or decryption as needed. FieldProc is not invoked for a delete operation on a row.

FieldProc Implementation

FieldProc is a SQL feature and is implemented through SQL commands. That is, FieldProc is not implemented through DDS, but only through SQL. Through SQL you create or alter a table to have the FIELDPROC attribute like this:

ALTER TABLE employee alter column
salary set FieldProc Userpgm

Likewise you can remove a FieldProc attribute using a SQL DROP statement like this:

ALTER TABLE employee alter column
salary Drop FieldProc

As noted above when you start FieldProc with the CREATE TABLE or ALTER TABLE commands, the effect is immediate. Starting FieldProc causes the FieldProc program to be called for each row in the table to perform encryption. Dropping the FieldProc attribute of a column causes the FieldProc program to be called for each row to decrypt the data.

Certain operations will cause FieldProc to be invoked even if the column data is not being used. For example, a legacy RPG program might read a file from beginning to end but not use a particular column that is under FieldProc control. Even though the column is not used in the RPG program FieldProc will be invoked to decrypt the value of the column for each row.

Note that a native SQL SELECT statement that does not include the encrypted column will NOT invoke FieldProc for decryption. Some work management operations are difficult with the current IBM implementation of FieldProc. For example, save and restore operations when the restore library is different than the save library can be problematic. Additionally, change management operations where there are changes to column attributes can be difficult to manage and require decrypting the database, applying the change, and then re-encrypting the database. These limitations are especially true in legacy RPG applications.

Supported Field (Column) Types

Most column types are support for FieldProc including character, numeric, date, time, and timestamp  fields. You can use FieldProc on null-capable  fields and double-byte character  fields. Some type of derived and counter  fields do not support FieldProc, but IBM i customers will  find that all normal application  fields are supported. With FieldProc there is no need to change the  eld size or attribute, nor is there any need to change or re-compile applications.

Legacy DDS Files

Many IBM i customers have the impression that legacy DDS  files will not support FieldProc encryption. This is not true! You can readily implement FieldProc encryption on  files created with DDS and it is not necessary to convert them to DDL and SQL tables. As IBM notes there are some advantages to doing so, but it is not necessary and the large majority of FieldProc users continue to use DDS  files. As noted above, you can only start and stop FieldProc using SQL commands, but these SQL commands work  ne on DDS-created  files.

Encrypting Multiple Fields in One File

DB2 FieldProc control can be started on multiple columns in one database table. Three is no practical limit on the number of columns you can select for FieldProc implementation. Of course, there are performance implications for encrypting multiple columns as the FieldProc program will be called independently for each column under FieldProc control. But many IBM i customers place multiple columns in a table under FieldProc encryption control. In some cases customers place hundreds of columns under FieldProc control. FieldProc’s support for multiple columns includes columns that are Primary or Secondary keys to the data. Please see the following sections for a discussion of limitations related to encrypted indexes and legacy RPG applications.

Excerpt from the eBook "IBM i Encryption with FieldProc - Protecting Data at Rest."

Encrypted Indexes

The DB2 FieldProc implementation fully supports the encryption of columns which are indexes (keys) to the data in a native SQL context - this includes RPGSQL applications. However, there are some severe limitations with legacy RPG applications around encrypted index order. If you are like most IBM i users it is important to understand these limitations to implement DB2 encryption in RPG.

Legacy RPG applications use record-oriented operations and not set-oriented operations that are typical of SQL. Many record oriented operations on encrypted indexes in RPG will work as expected. For example, a CHAIN operation on an encrypted index to retrieve a record from a DB2 table will work. If the record exists it will be retrieved and FieldProc will decrypt the value. However, many range and data ordering operations will not work as expected with legacy RPG programs leaving you with empty reports and subfiles. Consider the following logic:

SETLL (position to an particular location in the index for a file)
WHILE (some condition)
READ (Read the next record by the index) END WHILE

The SETLL (Set lower limit) operation will probably work if the particular index value exists. However, the program logic will then read the next records based on that position in the file. IBM i developers are surprised to learn that they will be reading the next record based on the ENCRYPTED value, and not on the decrypted value which is what they might expect. The result is often empty subfiles and printed reports. This is very common logic in applications where indexes are encrypted. Note that your RPG program will not get a file I/O error, it just won’t produce the results you expect.

In simpler applications this side-effect of encrypted indexes is not significant, or easily programmed around. However, in some applications where sensitive data is encrypted across a large number of tables (think social security number in banking applications) this can be a significant limitation.

Don’t despair, keep reading.

Overcoming Encrypted Index Limitations in RPG

The limitations of encrypted indexes in legacy RPG applications often lead IBM i customers to abandon their encryption projects. The prospect of converting a large number of legacy RPG applications to newer SQL interfaces can be daunting. Their legacy RPG applications contain a lot of valuable business logic, and the effort to make the conversion can be quite large.

Wouldn’t it be great if you could wave a magic wand and make legacy RPG applications use SQL without any changes?

IBM opened a path to this type of solution with Open Access for RPG, or OAR. OAR allows for the substitution of traditional file I/O operations with user- written “Handlers”. In essence, you can replace the native file I/O operations of RPG with your own code. No change to program file handling or business logic! The OAR capability spawned a number of user interface modernization products, and other solutions that take advantage of this. Imagine if your RPG screen handling I/O with Execute Format (EXFMT) could be replaced with a web-based GUI library. Instant modernization of the UI! There are now many solutions that leverage OAR for UI modernization.

Join Logical Files with DDS

One limitation of logical files created with DDS specifications involves join logical files. You will not be able to create DDS join logical file where the join involves an encrypted  eld with FieldProc. You will get an error about invalid data type usage. This is an IBM limitation and there is no known workaround for this issue. Note that this limitation only applies to DDS join logical  les and does not apply to SQL joins using encrypted indexes. Most IBM i customers will need to change their RPG or COBOL program logic to avoid the use of DDS join logical  les which use encrypted indexes.

Application Changes

Legacy RPG applications that use encrypted indexes often need re-design and re-programming to avoid the problems of encrypted indexes. You can avoid these issues if you are using an Open Access for RPG (OAR) solution that maps the legacy RPG record-based file operations to native SQL and the SQL Query Engine (See the note above about Townsend Security’s OAR/SQL offering).

If you need to re-design your RPG applications to avoid encrypted indexes consider putting all of your sensitive data in a table that is indexed by some non-sensitive value such as a sequence number or customer number, and use FieldProc to encrypt that table. There are many approaches to application re- design and you should be able to find a method that works for you.

Change Management

IBM i customers who have deployed change management solutions can encounter challenges with FieldProc implementations. While most of these challenges are surmountable, it will take effort on the part of the systems management team to integrate FieldProc controls into their change management strategy. Unfortunately the implementation of FieldProc does not provide much in the way of support for change management systems. The activation of FieldProc changes an attribute on the column in the table, but change management systems generally are not aware of this attribute. It can be challenging to promote table and column level changes and properly retain the FieldProc attribute of the data. Look for a FieldProc implementation that provides a command-level interface to stop, start, and change FieldProc definitions. These commands can help you integrate FieldProc encryption into your change management strategy.

Excerpt from the eBook "IBM i Encryption with FieldProc - Protecting Data at Rest."

Encryption in FieldProc

It goes without saying that your FieldProc application will need to use an encryption library to perform encryption and decryption operations. IBM provides an encryption software library as a native part of the IBM i operating system. It is available to any customer or vendor who needs to implement encryption and decryption in their FieldProc programs.

Unfortunately the native IBM encryption library is very slow. This might not be noticeable when encrypting or decrypting a small amount of data. But batch operations can be negatively impacted. The advent of AES encryption on the Power8 processor has done little to mitigate the performance issue with encryption. IBM i customers and third party vendors of FieldProc solutions should use caution when implementing FieldProc using the native IBM i AES software libraries. They are undoubtedly accurate implementations of AES encryption, but suffer on the performance front.

Key Management

An encryption strategy is only as good as the key management strategy, and it is difficult to get key management right. For companies doing encryption the most common cause of an audit failure is an improper implementation of key management. Here are a few core concepts that govern a good key management strategy:

• Encryption keys are not stored on the same system as the sensitive data they protect.
• Security administrators of the key management solution should have no access to the sensitive data, and database administrators should have no access to encryption key management (Separation of Duties).
• On the IBM i system this means that security administrators such as QSECOFR and any user with All Object (*ALLOBJ) should not have access to data encryption keys or key encryption keys.
• More than one security administrator should authenticate before accessing and managing keys (Dual Control).
• All access to encryption keys should be logged and audited. This includes use of encryption keys as well as management of keys.
• Encryption keys should be mirrored/backed up in real time to match the organization’s standards for system availability.

Encryption Key Caching

Encryption keys are often used frequently when batch operations are performed on sensitive data. It is not unusual that a batch program would need to perform millions or tens of millions of encryption and decryption operations. While the retrieval of an encryption key from the key server may be very efficient, performance may suffer when keys need to be retrieved many times. This can be addressed through encryption key caching in the local environment.

Secure key caching should be performed in separate program modules such as a service program and should not be cached in user programs where they are more subject to discovery and loss. Any module caching an encryption key should have debugging options disabled and visibility removed. Secure key caching is critical for system performance and care should be taken to protect storage.

Encryption Key Rotation

Periodically changing the encryption keys (sometimes called “key rotation” or “key rollover”) is important
to the overall security of your protected data. Both data encryption keys (DEK) and key encryption keys (KEK) should be changed at appropriate intervals. The appropriate interval for changing keys depends on a number of variables including the amount of data the key protects and the sensitivity of that data, as well as other factors. This interval is called the cryptoperiod of the key and is defined by NIST in Special Publication 800-57 “Key Management Best Practices”. For most IBM i customers rotation of data encryption keys should occur once a year and rotation of the key encryption keys should occur no less than once every two years. 

Excerpt from the eBook "IBM i Encryption with FieldProc - Protecting Data at Rest."

The performance of an encryption solution is one of the biggest concerns that an IBM i customer has when implementing FieldProc. There are many factors that can affect performance of a FieldProc application and it is wise to pay special attention to performance as you prepare to implement a solution. Let’s look at several factors that can affect performance.

AES 128-bit and AES 256-bit Performance

All key sizes for AES encryption (128-bit, 192-bit, and 256-bit) are considered secure for protecting all commercial sensitive data. Customers naturally wonder if the smaller key size of 128-bit encryption means better encryption performance when compared to 256-bit AES keys. In fact, the performance difference is much smaller than one might imagine. The smaller 128-bit key size uses 10 rounds during AES encryption, but the 256-bit AES keys use 14 rounds during AES encryption. The IBM i RISC processor optimizes cryptographic operations so the performance penalty for 256-bit AES encryption is very small. Considering that cybersecurity guidelines now recommend the use of 256-bit AES for protecting data in long-term storage, and for protecting against advances in quantum computing, you should always consider using 256-bit AES encryption for protecting IBM i sensitive data.

AES Encryption Software Performance

Encryption software libraries can vary greatly in performance even when using exactly the same methods and key sizes. Unfortunately the native IBM i AES encryption software library and APIs have a low performance profile and this can have a negative impact on your IBM i applications. Fortunately, there are high performance AES encryption libraries available from third parties that have a much better performance profile. The difference between the native IBM encryption libraries and third party libraries can be more than 100 times in processing speed. Use care when deploying an encryption solution to insure that the performance meets your minimum needs for processing time.

POWER8 On-Chip AES Encryption Performance

The new Power8 processor from IBM provides a hardware implementation of AES encryption to improve the performance of encryption. All new models of IBM i servers built with the POWER8 processor include this hardware implementation of AES encryption. In concept this is similar to the Intel processors which provide AES encryption through the AES-NI implementation.

While encryption performance has improved with the POWER8 processor, it is not a large improvement. Encryption speeds using the native IBM i APIs are about double the speed of the previous versions of the Power processor, but still greatly lag in performance compared to third party encryption libraries. It should be noted that this performance lag disappears when the IBM APIs are used to encryption very large blocks of information. For example, the AES implementation on the POWER8 processor can encrypt a megabyte of information at incredible speeds. Unfortunately this does not apply to the smaller blocks of data (credit card numbers, social security numbers, etc.) that are typically stored in DB2 database tables. It is likely that IBM i customers using iASP encryption will see a major performance bene t with the new POWER8 systems.

FieldProgram Program Performance

When FieldProc invokes a program to perform encryption or decryption it makes a dynamic all to a program executable. There is always a certain amount of overhead when making a dynamic call to an external program and this is true in FieldProc context, too. The more columns in a table that are under FieldProc control the more dynamic calls you will have to the FieldProc program as the FieldProc program is invoked independently for each column. It is important that the FieldProc program be properly optimized
for performance. Some key performance measures include:

• Minimized file I/O operations by the FieldProc program.
• Program modules are optimized on compilation.
• The program executable is optimized on compilation.
• Memory management is optimized for performance.
• Visibility of program objects is removed to improve performance.
• The FieldProc program is optimized for multi- threaded operation.
• Audit logging is optimized for performance.