Townsend Security Data Privacy Blog

IBM i FieldPROC Encryption, IBM Query, and Encrypted Indexes

Posted by Patrick Townsend on Jan 29, 2018 8:31:08 AM

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.

IBM i Encryption with FieldProc 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

IBM i Encryption with FieldProc

Topics: Encryption, IBM i, FIELDPROC

IBM i Customers Lag the Industry When It Comes to Encryption

Posted by Patrick Townsend on Dec 4, 2017 11:29:40 AM

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.

IBM i Encryption with FieldProc 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

IBM i Encryption with FieldProc

Topics: IBM i, FIELDPROC

IBM i FieldProc Architecture and Implementation

Posted by Luke Probasco on Oct 19, 2016 10:33:47 AM

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


FieldProc Architecture

IBM i Encryption with FieldProc 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.

IBM i Encryption with FieldProc

Topics: IBM i, FIELDPROC

Encrypted Indexes - Overcoming Limitation with FieldProc Encryption on the IBM i

Posted by Luke Probasco on Aug 30, 2016 11:00:00 AM

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


Encrypted Indexes

IBM i Encryption with FieldProc 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.

IBM i Encryption with FieldProc

Topics: IBM i, FIELDPROC

Encryption & Key Management for the IBM i

Posted by Luke Probasco on Jul 25, 2016 10:38:04 AM

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.

IBM i Encryption with FieldProc 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. 

IBM i Encryption with FieldProc

Topics: Encryption, IBM i, FIELDPROC

Encryption Performance on the IBM i

Posted by Luke Probasco on Jul 12, 2016 11:11:00 AM

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

IBM i Encryption with FieldProc 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. 

It is important that the FieldProc application be properly optimized as it may be invoked many times during a typical interactive or batch request.

FieldProc for Multiple Columns in a Table

It is likely that you will need to protect multiple columns in a table. For example, in a medical setting a table might contain the following information for a patient:

  • Name
  • Date of birth
  • Address
  • Social security number
  • Email address
  • Etc.
In this case you will be protecting multiple columns in a single table. This is fully supported by FieldProc and is very common in FieldProc implementations. When FieldProc programs are properly optimized for performance you should find that the extra performance impacts per column are not excessive. Thanks to the re-entrant model of the IBM i operating system the protection of multiple columns has a smaller performance impact. Encrypting two columns is NOT twice the performance impact as encryption one column.

Key Management Performance Impacts

As mentioned above your FieldProc encryption solution should implement good encryption key management practices. It is important that the key management interface impose little performance penalty. This means that the FieldProc application should use intelligent and secure key caching to minimize the number of key retrieval operations that must be performed. Additionally keys should not be exposed in user applications, but should be protected in separate modules. When implemented properly good key management will impose extremely little performance impact.   

Audit Logging Performance

One common feature of FieldProc applications is the ability to collect audit information about user activity. This might include collecting information about which users accessed decrypted information, etc. Audit logging will always extract a performance price. If you need to collect audit logs of FieldProc activity be sure to measure the performance impact of audit log collection in your own environment.

IBM i Encryption with FieldProc

Topics: Encryption, FIELDPROC

How Does Data Masking Work with FieldProc Automatic Encryption

Posted by Patrick Townsend on Sep 29, 2015 8:24:00 AM

Many compliance regulations such as PCI-DSS and HIPAA require that data be completely hidden or partially masked for all users who are not authorized to see the data. When IBM i (AS/400, iSeries) customers implement DB2 Field Procedure (FieldProc) encryption, how do they implement data masking at the same time? In this blog I want to talk about general principles of data masking, how IBM DB2 FieldProc applications can implement data masking, and some limitations that you need to know.

Data Masking and the FieldProc Architecture
IBM i FIELDPROC Webinar There is no direct facility provided by IBM in the DB2 FieldProc architecture to implement data masking for you. This means that your FieldProc application must implement this for you. FieldProc applications are written by you or provided by your vendor (see below for information about the Townsend Security solution). Since data masking is designed to protect sensitive data in the clear, this means that data masking should be implemented in the decryption logic. When a row is read from a database table the FieldProc application is called by DB2 to perform decryption. At that point the determination must be made on whether the user is allowed to see the entire column value in the clear, whether it should be partially masked, or whether it should be fully masked. 

Data Masking Security Architecture 

One of the core security principles of data masking is that it should be based on user whitelists and not on user object authority. This is not an intuitive idea for most IBM i system administrators because so much of IBM i security is based on file object level authorities. So why would data masking not rely on object authority?

One of the main efforts of a cyber criminal upon gaining access to your system to escalate their privilege level in order to access sensitive data and achieve the ability to manipulate system configurations. On the IBM i platform the attacker will try to gain security administrator privilege or All Object (*ALLOBJ) authority. If your data masking strategy is based on object authority it is immediately defeated when the attacker gains greater privilege. This is why data masking should be based on a whitelist approach and not on native object authorities, and why we took this approach in our Alliance AES/400 FieldProc implementation. 

Users and Groups
Like many other operating systems the IBM i provides for user groups. On the IBM i platform user groups are defined by a Group Profile, and individual users can belong to this group or it can be included in their supplemental group. Group profiles are a convenient way for IBM i security administrators to define authority rights for anyone in the group and not have to define these rights for each individual user. If you are a user you inherit the rights to the groups you belong to. A data masking implementation should incorporate user groups into its implementation through a whitelist approach.

Data Masking Options
Data masking options should allow for masking of an entire field, or just a portion of the field. The PCI Data Security Standard (PCI DSS) is probably most clear on data masking requirements for compliance. Only authorized users (defined by PCI as “personnel with a legitimate business need can see the full PAN”) should see an entire field unmasked and all others should see only a portion of the field (The first six and/or last four digits) or the field should be fully masked. When establishing your whitelist of users you should be able to define the data masking options at the same time as you define the user or the group.

Data Masking for Numeric Fields
On the IBM i platform we have a challenge when it comes to masking numeric fields. Numeric fields can only contain a numeric digit and can’t contain an asterisk or other typical masking character. For numeric fields you must determine a masking pattern that can’t naturally occur in the data. Perhaps you could choose a masking option that filled a numeric field with all 9’s. This would work if the field could not naturally contain all 9’s. An example might be a salary field. If you have a 9.2 zoned numeric field it is unlikely that anyone would have a salary equal to $9,999,999.99 and masking with all 9’s would work. Just be aware that masking numeric fields requires some forethought and planning.

Limitations and Gotchas
Data masking with FieldProc encryption is generally very effective and easy to do. But there are a few limitations. Consider a program that reads data from table A and uses a column value to read a record from table B. And assume that the value read from table A is encrypted. If a user can only see masked data, the data will be masked when read from table A and the lookup on table B will fail. This is a subtle limitation but it has occurred in the real world!

Summary
Data masking is a powerful additional security control for your FieldProc applications. If you are aware of the limitations it is a great tool to help you get better security for your IBM i databases and applications.

IBM i FieldProc encryption by Townsend Security
Townsend Security provides a full IBM DB2 FieldProc solution for the IBM i server platform with Alliance AES/400. In addition to strong 256-bit AES encryption, it provides IBM i customers with flexible data masking options on decryption. It has full support for individual and group profiles, and the security administrator can specify a default masking rule to apply to any users not explicitly allowed to see unmasked or partially masked data.

FIELDPROC Encryption IBM i

Topics: Alliance AES/400, FIELDPROC

How Does IBM i FieldProc Encryption Affect Performance?

Posted by Patrick Townsend on Sep 14, 2015 8:49:00 AM

IBM i (AS/400, iSeries) customers have a great automatic encryption option with DB2 Field Procedures, or “FieldProc”. As with any encryption facility, users always have questions and concerns about performance. Performance impacts extend beyond just the impact of encryption itself, so let’s look at various aspects of performance when it comes to IBM i FieldProc.

IBM FieldProc Architecture
IBM i FIELDPROC Webinar One of the largest impacts on performance comes from the actual architecture of FieldProc itself. IBM DB2 FieldProc is basically implemented as an event-driven exit point at the column level. What this means is that any insert, read, or update operation will trigger a dynamic program call to the FieldProc application program to perform encryption or decryption. There is definitely a performance penalty for this architecture. An application program that reads a large database on a modern IBM i server may be able to process hundreds of thousands of records per second. With FieldProc, that may be reduced to tens of thousands of records per second as the FieldProc program is invoked for each row in the table. You can still get good performance with FieldProc enabled (read on), but there will be an impact.


FieldProc Program Performance and Optimization
A FieldProc program is just an application program that you create or that your encryption vendor provides to you, so it can have its own performance issues. How much file I/O does the FieldProc program perform for each encryption or decryption task? How optimized is the application code? How optimized is the compilation of the program? Does the program perform any caching of internal information to improve performance? Like any program on any platform or operating system, a FieldProc program may perform well or not.

Encryption Performance
Surprisingly, there can be really big differences in the performance of encryption libraries even when doing the same type of encryption. You might think that 256-bit AES would have the same performance regardless of the vendor. And you would be really wrong about that. On the IBM i server platform I’ve seen a difference of more than 100 times between two different 256-bit AES encryption libraries. To put this in a practical context, this is the difference between 10 hours of batch processing versus 5 minutes of batch processing. That’s pretty dramatic. Encryption libraries can be optimized and should be optimized for performance. That is not always the case.

Number of Columns Under Encryption Control
The number of columns in a table will affect the performance of your FieldProc implementation. If you have three columns in a table under FieldProc control you will definitely see an impact on performance compared to a single column. Each read of a row in the table will result in three separate calls to a FieldProc program to perform decryption. This is not a linear impact on performance. That is, you won’t see an impact on the order of three times the impact of one column under FieldProc control. But there is a gradual impact as you add columns in the table. By the way, FieldProc will be called for each column even if your application does not use the column.

Encryption Key Management
Using encryption means using encryption keys. Assuming that you are not using a poor security practice such as storing the key on the same server as the encrypted data, the interface to your key management server represents another potential performance impact. How keys are retrieved and prepared for use by the encryption software can represent a hidden drag on performance. While a single key retrieval from a key server may take just a few milliseconds, the performance impact can be dramatic when thousands or millions of key retrievals are needed from a key server.

Encryption Key Caching
Because encryption key retrieval can slow the overall encryption process, it is important that a FieldProc application use secure key caching logic to minimize the number of key retrieval operations. If your nightly processing retrieves 10 million records for reporting, you definitely don’t want to retrieve encryption keys 10 million times. A good FieldProc implementation should securely cache encryption keys. This means that keys should not be exposed in program dumps or debug mode of operation.

CPU Performance
IBM i servers vary a great deal in CPU performance and the number of processors that are available to applications. Entry level servers may have a single processor that is shared between multiple partitions. High end IBM i servers can have a large number of processors and rival Mainframes in raw processing power. This will definitely have an impact on encryption performance. The number of processors is less important than the power of each processor. It sometimes surprises IBM i customers that adding a processor to a system might have minimal impact on encryption performance. But upgrading to a faster processor can make a big difference. Also, more modern IBM i servers have very powerful POWER7 and POWER8 chips and these will help with encryption performance.

POWER8 On-Board Encryption
The new IBM POWER8 systems now have built-in support for AES encryption. This is similar to the Intel AES-NI implementation. While this does provide some improvement in encryption performance, it won’t be as much as you might expect. The built-in chip support for AES encryption seems to be optimized for encrypting very large chunks of data at one time. If you are encrypting a credit card number of social security number, you won’t see a really dramatic improvement in performance. IBM i customers using ASP encryption should really benefit from the built-in encryption. In some cases such as with Townsend Security's Alliance AES/400 encryption for IBM i, the software implementation provides big performance advantages over the on-chip POWER8 implementation.

Native SQL Applications
As most IBM i customers know, IBM has been on a tear to improve SQL in DB2 for some time. We’ve seen increasingly better performance of SQL applications over time. In the current release of the IBM i operating system and DB2 database the performance of SQL is impressive. Because SQL performs better, you will see better performance when implementing FieldProc in native SQL applications. Of course, you don’t need to convert your databases from DDS to DDL/SQL to use FieldProc, but if you do you will see better overall performance.

IBM i Navigator and SQL Plan Cache
When discussing database performance it is always important to mention the IBM i Navigator SQL Plan Cache function. This application comes with every IBM i server and is always available. It can show you how well your DB2 applications are performing, and can even recommend steps you can take to improve performance! When using FieldProc it can be a very helpful tool.

More about Townsend Security’s AES/400 FieldProc Solution for IBM i DB2
The Townsend Security solution for FieldProc encryption is called Alliance AES/400. It is the fastest performing FieldProc solution in the market and implements all of the FieldProc recommendations above. 

FIELDPROC Encryption IBM i

Topics: Encryption, Alliance AES/400, FIELDPROC

FIELDPROC Encryption and Backup Protection

Posted by Patrick Townsend on Jul 31, 2015 2:18:00 PM

IBM introduced Field Procedures (FIELDPROC, or FieldProc) on the IBM i (AS/400, iSeries) platform in V7R1 of the operating system. It is a strategically important implementation and is a permanent part of the DB2 for IBM i database going forward. The FieldProc implementation is an event-driven exit point directly in the DB2 database and is invoked for most of the standard CRUD operations (but not delete). While the FieldProc implementation can be used for many things, IBM i customers primarily use it as a mechanism to automatically encrypt and decrypt data at the column level. It is now a widely adopted and deployed method for data protection on the IBM i and IBM System z Mainframe editions of the DB2 database.

IBM i FIELDPROC Webinar While the benefits of the data at rest protection offered by FieldProc encryption is clear, our customers often ask us if FieldProc encryption will also protect their backups. It is a good question because there are times when making a copy of a file with FieldProc encryption causes the data to be decrypted by the copy. So does DB2 data remain protected with normal IBM i backups?

Fortunately, the answer is Yes - your backups will be protected with FieldProc encryption when you use any of the normal SAVE commands on the IBM i platform including commands like Save Object (SAVOBJ), Save Library (SAVLIB), Save Save File Data (SAVSAVFDTA), Save Changed Objects (SAVCHGOBJ), and the various IBM Backup Recovery and Media Services (BRMS) commands.

While it is rare, I have seen some uses of the Copy File (CPYF) command to copy data to backup tapes or files. In this case your data will be automatically decrypted during the copy operation and will NOT be protected in the backup image. To save data in encrypted format ALWAYS use one of the IBM save commands, the IBM BRMS application, or any third party backup solution that uses the IBM SAVE commands.

Another related question that we often get is how can I verify that the data is actually encrypted on the backup image? This is a good question because security auditors often want an independent verification of the encrypted status of the data. One way to verify the encrypted status of the data is to use the IBM Dump Tape (DMPTAP) command to dump the contents of the tape after a save operation. Try saving the file without FieldProc encryption, then save it with FieldProc encryption enabled. The Dump Tape command will show the contents of the data and you can easily see unencrypted values or encrypted values in the dump reports. Note that you may need to turn off save compression in order to view the data with this method.

Another way to verify the encrypted status of data is to use the same procedure, but save the file or table to a save file (SAVF). You can then use FTP to transfer the file to your PC in binary mode and use a file viewer to review the contents. Unfortunately, you can’t use the Display Physical File Member (DSPPFM) command as it does not display save files. On your PC you might like to use a utility like UltraEdit as it can view data in the EBCDIC character format. You can easily determine that your data is encrypted in the save file.

Either of these techniques can be used to verify the encrypted status of your files when saved with FieldProc active. You can rest assured that your data is protected on backup tapes and images and that the encryption key is not stored with the data!

Townsend Security provides a FieldProc implementation in our Alliance AES/400 solution. It integrates seamlessly with our Alliance Key Manager solution which manages encryption keys through the entire key life cycle. The Alliance AES/400 solution is the only IBM i FieldProc encryption solution that is NIST validated for the AES encryption library, and which combines this level of encryption with a NIST validated encryption key management solution, giving you provable compliance with industry standards.

Backup data protection is a great added benefit to FieldProc encryption on the IBM i platform. I hope this discussion helps resolve any question you have about FieldProc encryption and backup protection.

Patrick

FIELDPROC Encryption IBM i

Topics: IBM i, FIELDPROC

5 Common FAQs About IBM i Encryption Using FIELDPROC

Posted by Victor Oprescu on Mar 4, 2014 8:22:00 AM

With V7R1 IBM introduced FIELDPROC (field procedure) exit points which Alliance AES400 for IBM i uses to encrypt database files at a field level. Since the cryptographic process is called by the database directly upon access, rather than by the application, this means that the process will work regardless of what type of application uses it. No application changes are needed, which is something our customers really like to hear.

FIELDPROC Encryption These are five frequently asked questions we get around FIELDPROC encryption.

1. What type of fields are supported with FIELDPROC?

Surprisingly, virtually every field type is supported, whether it is character (even binary character), numeric (zoned, packed or binary) date, time, timestamp, Double Byte Character Set (DBCS), and hex. Some fields have certain restrictions, for example in order to implement fieldproc encryption on date, time, and timestamp fields you must specify a default value that is not ‘current date and time’.  You can define this in DDS or structured query language (SQL), and there is an option in the DB2/400 FIELDPROC encryption menu that will do this for you.

FIELDPROC will also handle blank fields by not encrypting them at all. This helps us achieve better results from certain SQL operations. FIELDPROC encryption however does not support fields with NULL values and they should be avoided or changed if necessary.

2. Will I need to make any changes to my applications?

As I mentioned above, it is not necessary to make application changes, but here is more detail as to why: In V7R1, AES/400 can create FIELDPROC exit points that tie to individual fields in a DB/2 file. When a file is opened for any operation, read, update, insert, or delete, the exit point calls one of our FIELDPROC applications, which calls our encryption or decryption APIs. It does this regardless of which application is accessing the file, thus creating application independent encryption.

A few things to consider are: when you backup your file you will need to also backup the FIELDPROC applications, and make sure you restore them at the same time as well. It is also important to remember that if the file is accessed through FTP it will be transferred in the clear.

3. Can I encrypt index fields?

In short, yes. However, encrypting index fields will affect the performance of your SQL operations, and the more indexes you encrypt, the more your performance will be affected. This happens primarily for the following reason: For faster performance DB/2 looks up records based on their encrypted values. This means that when you tell DB/2 you are looking for the record with social security number 111223333, and that field is encrypted, it will encrypt the search string and then retrieve the matching record for you. This is done as a performance enhancement especially when working with logical files on the IBM i. However for some SQL operations it decrypts all the records in order to read the index fields, so rather than encrypting single values to look up, it needs to decrypt a multitude of records.

4. What kind of performance can I expect?

In our test environment, which consists of a single processor IBM i platform, model 515, approximately 3500 CPW, V7R1 of the operating system with TR5 installed, we can process about 16,000 records per second. Systems with higher processing power should see better performance. This means that if we have a file with one million records and one field encrypted we can read the entire file in about 60 seconds. If they are 2 fields encrypted it will take us about 120 seconds because we are handling two million decryption calls.

5. Does using external key management affect performance?

In short, no. The time it takes AES/400 to retrieve a symmetric encryption key from our Alliance Key Manager server is approximately 0.0116 seconds. And this is through a secure TLS connection. There are network infrastructures in which this time may be slightly affected, firewalls, routers, switches, distance, however it should never create a noticeable difference in performance.

To learn more about automatic encryption on IBM i V7R1 using FIELDPROC, download the podcast "FIELDPROC Encryption on the IBM i" to hear IBM i security experts Patrick Botz and Patrick Townsend discuss encryption, key management, and meeting compliance regulations on the IBM i.

IBM i FIELDPROC Encryption

Topics: Encryption, Alliance AES/400, IBM i, automatic encryption, FIELDPROC