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.
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.