Chapter 1: Introduction

To help IBM i customers deploy encryption and other data protection technologies, IBM introduced the DB2/400 column level exit point named FIELDPROC in Version 7 Release 1 (V7R1) of the IBM i operating system. This type of column level exit point is not new: it has been available in DB2 UDB on the IBM System z Mainframe for a number of years. However, it is a significant advance in data protection for the IBM i platform. While the IBM i DB2/400 database has the ability to use SQL Views and Triggers, this approach had many limitations that made its deployment limited in scope. The FIELDPROC implementation removes most of those limitations.

FIELDPROC is an exit point architecture and interface. That is, it enables the use of column level encryption, but does not provide the actual application support for it. Customers must implement their own applications, or the applications of software vendors like Townsend Security, to take advantage of FIELDPROC to secure their data. This document discusses the Townsend Security implementation of FIELDPROC in the Alliance AES/400 product.

By itself, FIELDPROC does not guarantee good data protection in DB2/400 databases, nor does it assure that you can meet compliance regulations such as PCI Data Security Standards (PCI DSS). FIELDPROC must be combined with appropriate key management to meet these compliance regulations. When implemented correctly, FIELDPROC will ensure that data is encrypted when written to the database, and that it is decrypted when read from the database. This provides protection of the data on disk and on backup images (tape, save files, etc.). However, it does not provide protection for unauthorized use of the data by users or applications. For example, FIELDPROC will not prevent FTP or ODBC applications from accessing the data on your IBM i platform. Additional user and application controls should be implemented to provide this level of security. The Townsend software implements these controls, and this is described in more detail below.

Change log

Version Date Description
1.1.0.005 8/18/2015 Add information about logical files for version 6.41.
1.1.0.005 7/26/2016 Merge previous changes into version with new manual format. Add change log.

Chapter 2: FIELDPROC Architecture

FIELDPROC is an attribute of a DB2/400 field, or column. Just as a field has a data type (character, zoned numeric, etc.), length, and description, a field can have a FIELDPROC exit program associated with it. Once a field has a FIELDPROC program defined for it, the database will make a call to the FIELDPROC program for every file read, write, and update. It will also call the FIELDPROC program during certain query and file change operations.

It is not necessary to modify existing RPG and COBOL applications to use FIELDPROC, and it is not necessary that RPG and COBOL applications use SQL for database access. This means that legacy applications created with the Original Program Model (OPM) source and compilers can take advantage of FIELDPROC data protection without code modification. This is a significant advantage over other methods of column level data protection.

FIELDPROC is enabled through the use of SQL commands. You cannot implement FIELDPROC through DDS or other database definition methods. The Alliance AES/400 product performs this operation for you when you enable FIELDPROC controls, so you do not need to know SQL to implement this facility in Alliance AES/400.

FIELDPROC supported field types

IBM enables the use of FIELDPROC on most DB2/400 data types, but imposes constraints on date, time, and timestamp fields. You cannot implement FIELDPROC encryption on these field types when they have a default value of the current date and time. If you do not specify a default value in DDS or in your SQL statements, the default is automatically the current date and time. Alliance AES/400 provides an easy way to change the default on these fields, but you must understand the impact on any application that may be depending on the default value of the current date and time.

Alliance AES/400 supported field types

The Alliance AES/400 support for FIELDPROC includes support for all of the data types that are typical in RPG and COBOL applications. These include character, zoned numeric, packed numeric, binary numeric, date, time, timestamp, Double Byte Character Set (DBCS), hex, and binary character fields. These are the most common field types used by IBM i customers in DB2/400 databases. Additional field types may be added in the future.

Chapter 3: Character Fields with Blank Value

IBM recommends that FIELDPROC applications avoid the encryption of character fields that have a blank value. By avoiding the encryption of blank fields you can achieve better results from SQL operations that compare fields of different lengths in different tables. The Townsend software implements this recommendation and will not encrypt or decrypt a character field that contains all EBCDIC blanks (hex value 40). The FIELDPROC program will be called, but will not perform the operation.

Chapter 4: IBM FIELDPROC Limitations

You should be aware of the limitations of FIELDPROC before you implement it in your applications and databases. Please refer to the IBM SQL Reference and IBM SQL User Guide for a full description of these limitations.

Change Physical File Member (CHGPFM)

It is important to note that the use of the Change Physical File (CHGPF) command can result in the loss of FIELDPROC protection support in certain situations, with the possible corruption of your data. IBM recommends that you consider converting files that were created with DDS to SQL definitions before you implement FIELDPROC. Please refer to the IBM SQL documentation for a description of this process.

Default value for Date, Time, and Timestamp fields

FIELDPROC cannot be used with field types Date, Time, and Timestamp when these fields have a default value of the current date and time. Alliance AES/400 provides an easy method of changing the default without re-creating the files.

Encrypt on CHAIN or READE, or use SQL WHERE clause

When you chain (CHAIN) to a file, or use a read equal (READE) operation, or use SQL with a WHERE clause, the IBM DB2/400 database will first call the FIELDPROC application to encrypt the plaintext value, and then will perform a lookup on the database using the encrypted value. If there is an exact match, the FIELDPROC program may not be called again to perform decryption (See the discussion below about QAQQINI FIELDPROC_ENCODED_COMPARISON). The assumption of the database processor is that you already know the value on the search, and that decryption is not needed. However, this means that the Alliance FIELDPROC application cannot perform data masking after decryption. In this case you will need to perform your own data masking in your application. Alliance AES/400 provides APIs to help you accomplish this.

QAQQINI option for FIELDPROC_ENCODED COMPARISON

The DB2 query optimizer will attempt to improve query performance by not doing certain decode operations depending on the settings in the QAQQINI file. The option for FIELDPROC_ENCODED_COMPARISON is set to enabled by default. However, this means that some decryption operations will not be called on key equal operations. This can defeat the Alliance AES/400 option to mask decrypted data. You must turn this option off on a system or job level in order to have decrypted field masking. You can find more on this subject in the IBM I System information center at:

One way to implement an override to the FIELDPROC_ENCODED_COMPARISON option is to create a copy of the QAQQINI file into your application library, change the option, and then perform an override to the job to use this file. The steps are:

  1. Create a duplicate of the QAQQINI file to your application library. You must use CRTDUPOBJ to copy the file as there are triggers on the file that must exist in your application library. The file must also have the name QAQQINI. Here is an example of the command:
CRTDUPOBJ OBJ(QAQQINI) FROMLIB(QSYS) OBJTYPE(*FILE) TOLIB(MYLIB) DATA(*YES) 
  1. You must now change the FIELDPROC optimization option to *NONE. It will be *DEFAULT until you change it, and the default is to optimize queries. To change it you start an SQL session and use a command like this:
UPDATE MYLIB/QAQQINI SET QQVAL='*NONE' where QQPARM='FIELDPROC_ENCODED_COMPARISON'
  1. Now you must set the job to use the modified version of the QAQQINI file. Run a command like this:
CHGQRYA QRYOPTLIB(MYLIB)

This override will apply to your active job only.

Chapter 5: Encrypted Key Fields

When a field is encrypted it contains a binary value that has no sequential relationship to the original value. This is a natural part of the encryption process. These means that certain operations on an encrypted field will not work as expected with FIELDPROC. For example, if you use the SETLL or SETGT operation on a field, the IBM FIELDPROC implementation will first encrypt the plaintext value, and then use this encrypted value to position the file cursor. The encrypted value may not have the position you wish. Other operations like CHAIN will work as expected.

Joining files with an encrypted key

When you join multiple physical files with a Logical File or a SQL Join using an encrypted field, the encrypted values must be the same for each index. Alliance FIELDPROC supports this by implementing the option to use the same initialization vector for each field in the respective files. You can select this option when you copy a FIELDPROC definition.

FieldProc and Logical Files

IBM i FieldProc encryption with the Townsend Security Alliance AES/400 product helps many IBM i customers implement data protection quickly and with few or no application changes. While FieldProc works well with native SQL applications, there are some limitations in DDS implementations of logical files. These are discussed in the sections below.

Logical files and sort sequence

When you encrypt a field that is a key field in a logical file you will change the sequence in which records are retrieved by the logical file. The new sequence will be the ascending or descending sequence based on the encrypted value, and not on the original plaintext value. If you process the records from the logical file in sort order sequence, this will probably cause a problem for your application logic. You can still do key field lookups (CHAIN, etc) and these will work correctly.

The change in the sort sequence of logical files is the most problematic aspect for most IBM i customers. If your RPG logic depends on the order in which records are read from a logical file, you may find that display files and reports no longer have the correct information. This is because DB2 does not call FieldProc to decrypt the entire index. Instead, the records are processed by the order of the encrypted value. Just as for physical files your CHAIN operations to logical file indexes will work as expected because DB2 will call FieldProc to encrypt the search value and use the encrypted search value to retrieve a record. But reading a file by index will not be in the expected order.

Simple logical file over one physical file

Simple logical files that are used to define a new, secondary key for a physical file or which use the primary key field of the physical file, will not cause a problem when activating or deactivating a FieldProc definition. Simply activate FieldProc through the Alliance AES/400 configuration menu option or by using the Start Field Proc (STRFLDPRC) command. Please be sure to read the section about about logical files and sort sequence.

Multiple format logical files (multiple physical files)

A logical file can be built over two or more underlying physical files. The logical file will reference the physical file format identifier and the relevant key fields. In most cases you can activate FieldProc encryption without any problems. For some logical files of this type you may need to delete the logical file, start FieldProc, and then create the logical file again. Be sure to see the notes above about logical files and sort sequence for important information.

Join logical files (JFLD) will not work on encrypted indexes

Join logical files use the JFLD definition to join one or more physical files into one view. You will not be able to use join logical files over key fields that are encrypted by FieldProc. If a join logical file exists the operation to start FieldProc will fail. If you delete the join logical file and then start FieldProc, you will not be able to create the join logical file again. An error message will indicate an incompatible field type and there is no way to resolve this issue.

Customers with join logical files can use other techniques to index and access data, or can convert the applications to use a native SQL interface. The SQL interface does not have a limitation about joined tables.

Chapter 6: Protecting Multiple Fields in a File

FIELDPROC can be used to protect multiple fields in the same file. The FIELDPROC program will be called for each field separately. If you have many fields in a file under FIELDPROC control, this can have performance impacts. If you have a large number of fields in a file or table that need protection, you should consider moving these fields to a structure definition. When done properly you can greatly reduce the performance impacts of FIELDPROC with this technique.

Chapter 7: Encryption Algorithms

Alliance AES/400 implements the Advanced Encryption Standard (AES) for encrypted field protection. This encryption method is defined by the National Institute of Standards and Technology (NIST) as Federal Information Processing Standard 197, or FIPS-197. Alliance AES/400 encryption uses 256-bit AES encryption for data protection and the implementation is NIST certified on V7R1 of the operating system. This is the strongest level of protection available in the standard.

NIST certification

Many compliance regulations require that encryption be implemented to industry standards, and that you avoid home grown or proprietary encryption methods. The AES encryption software used for FIELDPROC has been independently tested and certified by NIST to the AES Validation standard. This certification was performed on V7R1 of the IBM i operating system. This certification is your assurance that the Townsend encryption software meets regulatory compliance requirements for industry standard encryption, and that the encrypted data will be compatible with other implementations of AES.

Chapter 8: Key Management

Proper encryption key management is crucial to your encryption strategy and security. Key management systems should generate strong encryption keys, protect them from corruption or substitution, and allow their use only by authorized users and applications. Data security regulations such as PCI DSS have become much more diligent and restrictive about key management practices, and you should give care to how key management is implemented.

The Alliance AES/400 software provides two approaches for encryption key management: a FIPS-140-2 compliant key management solution named Alliance Key Manager, and a local key store that resides in the Alliance AES/400 application library.

While the local key store provides security and protection of keys from corruption or substitution, modern compliance regulations look askance at encryption keys stored locally to the protected data. This is because it is not possible to implement Dual Control and Separation of Duties related to key management and protected data. You should strongly consider deploying Alliance Key Manager as your encryption key management solution to best meet compliance regulations. You can also use Alliance Key Manager to protect data in Windows, Linux, and IBM System z Mainframe environments.

Chapter 9: Enabling and Disabling Encryption

When you enable FIELDPROC encryption, the DB2/400 database will call the FIELDPROC program for an initialization of the FIELDPROC definition, and then immediately call the FIELDPROC program for every record, or row, in the table to encrypt the data. If the FIELDPROC exit point is successful all of the data in the file will now be encrypted.

When you disable FIELDPROC encryption, the DB2/400 database will call the FIELDPROC program for each record, or row, in the table to decrypt the field. After the FIELDPROC exit point is disabled, the data is no longer protected with encryption.

It may be difficult to know if FIELDPROC controls are in effect. If you display the file with the Display Physical File Member (DSPPFM) command, or with a file utility such as DFU or DBU, the data will be decrypted and appear to be unprotected. The data is, however, protected in the table. You can use the Display File Field Definition (DSPFFD) command to determine that the data is, in fact, protected. You can also dump the backup tape or save file to observe that encryption is protecting the data.

IMPORTANT: FIELDPROC controls should always be combined with user and application access controls to achieve adequate security of the protected data. While FIELDPROC provides protection of data on disk and on backup media, it is not adequate to protect data while in use by applications and users. You should implement the additional user controls provided by Alliance AES/400.

Chapter 10: User Access Controls

To achieve proper security of your protected data, you should consider implementing user access controls with FIELDPROC encryption. User access control gives you the ability to restrict access to protected data to just the users who should have access. This is an important control when you have automatic decryption of data by FIELDPROC. User access control is enabled in the Alliance AES/400 FIELDPROC definition.

You implement user access control using the Alliance AES/400 configuration option to Work With API User Controls. You can define by user and encryption key, who should be able to access the protected data. In essence, you are creating a “white list” of users who can access the protected data. This is a common approach in security systems to define access control lists (ACL).

IMPORTANT: Alliance AES/400 user access controls do NOT rely on object authorities or *ALLOBJ authority to files. Instead, Alliance AES/400 relies on a list of users that the security administrator defines to Alliance AES/400. This means that you can effectively exclude the QSECOFR profile, and any user with *ALLOBJ authority, from access to protected data.

Alliance AES/400 supports the definition of Group Profiles when defining user access. This makes it easier for the security administrator to apply controls. Rather than add all of the users in the Accounting department to the user access list, you can just add the Accounting department Group Profile to the access list, and all users who have that Group Profile on their user profile will immediately have access.

The Alliance AES/400 FIELDPROC routines use system APIs to determine the current user for any file operation. When the current user is known, the user profile information is retrieved to determine the Group Profile.

Alliance AES/400 supports the definition of a *DEFAULT user access control. This is especially helpful when used with FIELDPROC encryption and decryption. If there is no *DEFAULT user definition for a particular encryption key or user, the Alliance AES/400 implementation of FIELDPROC will return a SQL state error message in the range of 38nnn to the application program. Normal RPG and COBOL applications will experience this as an I/O error. If there is no monitoring of the file operation the program will abend with error message CPF504D. However, if you define the *DEFAULT user you can impose decryption masking options and avoid the I/O errors. One of the decryption masking options is to completely mask a character field with asterisks (*) or the character you choose, or to mask a numeric field with all 9s. You can even define a *DEFAULT user for all encryption keys (*ANY for the key name) to provide a system-wide default action.

Chapter 11: Decryption Data Masking

Data masking is an important part of many compliance regulations. For example, PCI DSS recommends that users only see the last 4 digits, or only the first 6 digits, of a credit card number if they do not have the need to see the entire number. Alliance AES/400 FIELDPROC support lets you define user-specific masking, Group Profile specific masking, and default masking options for both character and numeric fields. For character fields you can choose to mask:

  • All but the last 4 characters

  • All but the first 6 characters

  • All of the characters

You may choose the masking character you want to use. You should only use a masking character this is unlikely to be used in character data.

For numeric fields including zoned, packed, and binary numbers you can choose to mask:

  • All numeric positions with 9s

For date, time, and timestamp fields, the following values are using for masking:

  • Date: 9999-12-31

  • Time: 23.59.59

  • Timestamp: 9999-12-31-23.59.59.999999

Data masking is implemented on the User Control a definition. You must also enable this feature on the Alliance FIELDPROC definition.

IMPORTANT: IBM has released a set of Program Temporary Fixes (PTFs) as an enhancement to FIELDPROC support to allow for the detection of masked data and prevention of an encryption and update of the masked data. Alliance AES/400 fully supports this facility when you apply the PTFs to your system and enable the Masked Data Option on the FIELDPROC definition.

IBM PTFs for masked fields

After the initial release IBM recognized the risks associated with updates to fields that are masked, and released PTFs that allow FIELDPROC programs to detect mask characters in fields and signal to the DB2/400 database that the field should not be updated. Alliance supports this implementation. In order to implement this facility you must install the following PTFs: Si43199, SI43184, SI43185, and MF52620 and all dependent PTFs. Please note that these PTFs have dependencies, and may have been superseded at the time you request them for installation.

Chapter 12: Application Impacts

Generally the implementation of Alliance AES/400 FIELDPROC support will have no impact or minimal impact on your user applications. The Alliance AES/400 implementation will not change the size of character or numeric fields, and applications should behave normally. However, there are certain situations that may affect the operation of your applications and the protection of sensitive data. Especially, you should be aware that using the Change Physical File (CHGPF) command and possibly other IBM commands, may have a negative impact on a FIELDPROC implementation.

Please refer to the IBM SQL Reference Manual and the IBM SQL User Guide for information about FIELDPROC and its limitations.

IMPORTANT: By using FIELDPROC protection you accept responsibility for understanding the limitations of FIELDPROC and for testing the implementation on your system before placing the solution into a production environment.

Chapter 13: Key Rotation

Both the Alliance AES/400 local key store and the Alliance Key Manager appliance support automatic and manual key rotation. However, the current implementation of Alliance AES/400 FIELDPROC will not automatically detect and handle a key rotation change. You should define encryption keys as non-rotating and perform bulk decryption and re-encryption of data using a new key.

Alliance AES/400 does make bulk key rotation (key change) easy to do. You can use the option on the Work With FIELDPROC definitions panel to change a key. When you change a key you remove FIELDPROC control from a file which causes the file to be decrypted, and you enable a second FIELDPROC definition that specifies a different encryption key. This causes the file to be encrypted with the new key.

The command Change FIELDPROC encryption (CHGFLDPRC) can be used to make this change in batch or under your program control.

Chapter 14: Multiple Member Files

Multiple member files are fully supported by IBM FIELDPROC and by the Alliance AES/400 FIELDPROC support. When you enable FIELDPROC the IBM DB2/400 database will automatically encrypt the field in every row of every member in the database. Likewise, if you disable FIELDPROC control all rows and members in the file will be decrypted.

Chapter 15: Performance

Encryption tasks are by nature CPU intensive. However, the Alliance AES/400 encryption APIs are highly optimized and should minimize the impact of encryption on your system. While each IBM I customer will have different processing capabilities and mix of interactive and batch workloads, the Alliance AES/400 encryption APIs should have the minimum possible impact your systems. Here is one example of performance of the Alliance FIELDPROC encryption support:

System:	        IBM Model 515

Processors:		One

CPW rating:		3200

Disk:			Two 4327 drives, no RAID

Memory:			1 Gig

Database:		500,000 records

Field size:		16 characters (credit card)

Workload:		Dedicated system

Encryption results:

Time to encrypt:		41 seconds

CPU to encrypt:		    20.19 CPU seconds

Time to decrypt:		39 seconds

CPU to decrypt:		    20.32 CPU seconds

Time for key change:	78 seconds

CPU for key change:	    40.31 CPU seconds

Your results may vary from these depending on a number of factors including the number of processors on your system, the speed of the processors, the amount of memory available to your application, the overall workload of your system, and other factors.

Chapter 16: Backup and Recovery

IBM recommends that a FIELDPROC processing program be placed in the same library as the database file or table. Alliance AES/400 will copy the FIELDPROC program to the library that contains the protected file, and will give it a unique name. The name will follow the format of the FIELDPROC definition using an “A” prefix. So, a FIELDPROC definition of “0000000001” will result in a program with the name “A000000001” being created in your application library. You should be sure that you save this FIELDPROC program with the data that is being protected. On restore operations, you should restore this program with the protected file. If there are multiple fields under FIELDPROC protection, you must be sure that each corresponding FIELDPROC application is restored.

Because the FIELDPROC configurations are stored in the application library ALLAES100, you should be sure to back up this library every time you install FIELDPROC support for a file or table. The Alliance AES/400 library must be available on any system where you are restoring a FIELDPROC enabled application.

SECURITY ALERT: You should save the ALLAES100 library separately from your other applications libraries that implement encryption and/or FIELDPROC support. It is good security practice to place Alliance backups on separate backup tapes, and to move them to offsite storage in separate containers and shipments.

Chapter 17: High availability

The Alliance AES/400 product fully supports a High Availability strategy for the IBM i platform. The solution is compatible with a variety of mirroring products including Vision, iTera, MIMIX, and IBM DataMirror. You are responsible for enabling the data mirroring solution on the IBM i platform, and you should refer to the Alliance AES/400 reference manual for recommendations on objects that you should not mirror.

Note that you must have a licensed copy of the Alliance AES/400 product on your high availability or disaster recovery server. The configuration options for FIELDPROC and user controls must be identical to the production system. Most mirror solutions use remote journaling to move information to the secondary system, and then apply the journal entries. This will necessitate having the proper configurations of Alliance AES/400 on the target system before you start mirroring. The sequence of steps will be:

  1. Install and configure Alliance AES/400 on your primary system.

  2. Save the ALLAES100 library on the primary system, and restore it on the secondary system.

  3. Add a license to Alliance AES/400 on the secondary system.

  4. Create a mirroring configuration on the primary system, and exclude the license data areas AETRI2 and AEACT2.

  5. Start mirroring

The AKM server provides for its own real time mirroring of encryption keys. See your platform-specific deployment guide for information on how to implement key server mirroring.

The Alliance AES/400 FIELDPROC implementation provides for the ability to define a failover key server. For business critical applications it is recommended that you deploy a redundant key server, and define the key server in the Alliance AES/400 key manager definition. Alliance AES/400 FIELDPROC will automatically use the failover key server in the event the primary key server is unavailable.

Chapter 18: Encryption and Decryption Audit

Alliance AES/400 provides for global configuration of the audit of encryption and decryption tasks. These options are available through the Alliance AES/400 Configuration Menu. When enabled each encryption and/or decryption operation will be logged to the AEHIST file for compliance reporting and audit. You should be aware that enabling this option will have some performance impact on your application and database.

Encryption and decryption and security audit journal (QAUDJRN)

Alliance AES/400 also provides for the automatic recording of encryption and decryption tasks to the IBM security audit journal QAUDJRN. You also enable this on the Alliance AES/400 global configuration. When enabled entries will be written to the QAUDJRN journal. Note that Alliance AES/400 will not configure the system values, journal receivers, and QAUDJRN for you. You must enable these options before enabling this option in Alliance AES/400.

Compliance regulations may require that you collect security events in a centralized log database. Townsend Security provides the syslog-ng Premium Edition product as a centralized log collection server, and the Alliance LogAgent application for the IBM i platform to collect and transmit security events to the log collector. Alliance LogAgent can also send events to SIEM solutions such as ArcSight, LogRhythm, Trigeo, TripWire, Solutionary, and other SIEM products. Please contact your account representative if you need information about these solutions.

Chapter 19: QSECOFR and *ALLOBJ authority implications

Certain IBM i users have a high level of access to programs and files on the IBM i platform, and cannot be restricted from having this access. This includes:

  • The QSECOFR user profile

  • Any user with the QSECOFR user as the Group Profile

  • Any user with All Object (*ALLOBJ) authority

Regardless of the public authority settings on a file or program (such as *EXCLUDE), there is no way to exclude these users from accessing files, programs, or key management files. This is why native IBM i security is inadequate to meet PCI DSS version 2.0 standards. You cannot use native IBM i security to achieve Dual Control and Separation of Duties in your encryption and key management implementation.

Alliance AES/400 meets PCI DSS requirements in this area through its FIPS-140-2 compliant key management solution Alliance Key Manager. This solution fully implements Dual Control and Separation of Duties for data protection. IBM i security administrators can implement FIELDPROC encryption with no access to the key management appliance, and with no visibility to encryption keys.

Chapter 20: Problem Determination

Alliance AES/400 FIELDPROC support provides several methods of problem determination. You can use any one or all of these methods to troubleshoot encryption problems.

SQLSTATE messages in Joblogs

FIELDPROC provides the facility of returning SQLSTATE error codes and message text when errors occur. Alliance AES/400 takes advantage of the error codes and messages, and you can find them in the job log of any job that encounters an error. In the job log you will find a message that indicates a SQL error occurred. If you view the second level text of the SQL error, you will find the Alliance AES/400 SQL STATE error code and descriptive text. FIELDPROC SQLSTATE error codes are in the range of 38000 to 38999. Any SQLSTATE error code in this range indicates that the Alliance AES/400 FIELDPROC program encountered an error. You can find a list of these errors at the end of this document.

For interactive jobs use the DSPJOBLOG command. For batch jobs that are active, you can use the WRKJOB command and use the option to view the job log. For completed batch jobs you can view the QPJOBLOB output.

Alliance AES/400 application log

The Alliance AES/400 FIELDPROC definition provides an option for Application Logging. When you enable this option detailed diagnostic entries are written to the file ANLOGA. You should only enable this option when you are trying to troubleshoot a problem, and you should turn it off when you are finished. Clear the ANLOGA file using the Clear Physical File Member (CLRPFM) command.

Chapter 21: FIELDPROC Step-By-Step Guide

This section provides step-by-step instructions on how to set up Alliance AES/400 FIELDPROC on your system using a typical implementation.

Before you start

Before you begin the working with FIELDPROC encryption, please make sure these steps are completed.

Install IBM masking PTF’s

The following V7R1 PTFs are required to use Alliance AES/400 FIELDPROC with data masking. Please note that these PTFs may have been superseded and may have dependencies. Be sure to install and apply all related PTFs:

Product 5770SS1:

  • SI43199

  • SI43184

  • SI43185

Product 570999:

  • MF52620

Field identification

You will need to know the file, field, and library you want to encrypt. You can use the DSPFFD command to determine the name of the field you want to protect.

Digital Certificate Manager Application ID

On the IBM i platform you will need a DCM Application ID for your SSL connection to communicate to the key server. Please refer to the document AKM DCM Configuration Guide for IBM i for information on how to create certificates and an associated Application ID.

Key server requirements

If you are using the Alliance Key Manager as your encryption key repository, you will need the following information from your security administrator:

  • The name of a 256-bit AES key.

  • The IP address or DNS name of the key server.

  • The port number (the default is 6000).

Configuring and activating FIELDPROC encryption

Creating a Key Manager definition

Navigate to the Main Menu:

image alt text

Enter option 2 for Configuration.

The following panel is displayed:

image alt text

Enter option 3 to Work with key managers. Press F6 to add a new Key Manager definition.

The following panel is displayed:

image alt text

Key manager name: Assign a name to the key manager.

Key type: Select a key type. Enter option 3 for Alliance Key Manager.

The following panel is displayed:

image alt text

Description: Provide a description for the key manager.

Status: Enter 1 for Active.

Server name or IP address: Enter the DNS or the IP address of the primary AKM server.

Server port number: Provide the port number for key retrieval for the primary AKM server (6000 is the default).

Failover name or IP address: Enter the DNS or IP address the failover AKM server.

Failover port number: Provide the port number for key retrieval for the failover AKM server (6000 is the default).

SSL Application ID: Enter the name of the SSL Application ID.

AKM key name: Enter the AKM key name that you want to use. This will be the default key name for the key manager definition. You will specify the actual key name to use with FIELDPROC on the FIELDPROC definition below.

Press Enter to complete the definition. Press F3 to return to the Configuration Menu.

Validating the connection to Alliance Key Manager

image alt text

Enter option 40 to Validate AKM key retrieval.

The following panel is displayed:

image alt text

Key manager name: Enter the name of the key manager definition created in the previous step.

Press Enter to retrieve the default values for the remainder of the fields and validate the connection to the AKM server.

The fields are populated with the default values:

image alt text

Observe the message, “Successful key manager connection, key name is valid”.

If an error occurs, contact Townsend Security customer support with the following information:

  • The error code returned on the validation message. Provide this to Townsend Security customer support when creating the problem ticket.

  • Verify the default key is created on the key server. The key name is case sensitive.

  • Verify that you can ping the key server from your IBM i. Use the PING command.

Granting authority to DCM key store

In order to create a TLS connection to the AKM server, a user must have *USE authority to the IBM Digital Certificate Manager key store. You can use option 20 (Grant user authority to DCM) on the Installation menu to give a user this authority.

NOTE: If you receive a 1424 or 5490 error code when attempting to validate a connection to the AKM server, this is usually due to the user not having authority to the DCM key store.

The following panel is displayed:

image alt text

Enter the user profile name to grant authority. You can use a group profile for this option. Press Enter to grant authority.

Create the FIELDPROC definition

Navigate to the Configuration Menu:

image alt text

Enter option 11 for DB2/400 automatic encryption. Press F6 to add a new definition.

The following panel is displayed:

image alt text

FieldProc name: The FIELDPROC name is automatically created.

Description: Provide a description.

Status: Enter 1 for Active.

Field protection type: Enter 1 for AES-CTR mode.

Press Enter to continue.

The following panel is displayed:

image alt text

Protected File: Enter the file name you want to encrypt.

Library: Enter the library name you want to encrypt.

Field/Column name: Enter the field or column name you want to encrypt.

Application logging: Enter option 2 for No. Application logging should be off unless an error is encountered and you are working with Townsend Security Support. Logs are created in file ANLOGA in the product library

Key management: Enter option 1 to use the Local key store, or enter option 2 to use Alliance Key Manager.

Key manager name: If you are using Alliance Key Manager, enter the name of the Key Manager definition you created and validated in the steps above. Leave this field blank if you are using the local key store.

Key name: Enter the key name to use for FIELDPROC encryption.

Key instance: Leave the key instance blank.

The FIELDPROC application name is automatically created for you. The library name is *SAME to indicate that the FIELDPROC application will be copied to the library that contains the file. IBM recommends that the FIELDPROC application exist in the same library as the data being protected.

Press Enter to continue. The following panel is displayed:

image alt text

Masked data option: Enter option 1 for Yes if you want to enable update controls on masked data. Note that you must install the IBM PTFs identified above to use this option. When enabled, this option will prevent masked data from being encrypted and replaced the actual value in the field.

Mask character: Enter your choice of character for masking if you want something other than asterisk (*). You must not choose a masking character that can be found in plaintext data.

User control: Enter option 1 for Yes to restrict user access to the decrypted data. When you enable this option only those users you define on the User Control menu will be able to decrypt and view the data. The User Control definition will also let you define which users can only see masked data.

Application control: Enter option 2 for No. This feature is not currently supported by the IBM FIELDPROC architecture.

Audit encryption: Enter option 1 to enable encryption auditing.

Audit decryption: Enter option 1 to enable decryption auditing. When enabled, an entry will be created in the IBM i security audit journal QAUDJRN. There is overhead in auditing. Most customers are only interested in auditing decryption, not encryption. Consult with your security administrator on the need to audit data access.

Press Enter to continue. The following panel is displayed:

image alt text

This panel is informational. Press Enter to complete the FIELDPROC definition.

Start FIELDPROC encryption

In the FIELDPROC definition list display, enter option 13 next to the FIELDPROC definition and press Enter. The following panel is displayed:

image alt text

Enter 13 in the option field and press Enter.

The following panel is displayed:

image alt text

Enter option 1 to overwrite an existing FIELDPROC program and press F10 to continue.

Alliance AES/400 will perform the necessary SQL steps to start FIELDPROC encryption and will immediately encrypt all records in the file. The file will be exclusively allocated during the process of enabling FIELDPROC and encrypting the data.

NOTE: If you have a large number of records in a file, the process of starting encryption may take some time. If you prefer, you can use the Alliance AES/400 Start FIELDPROC (STRFLDPRC) command. This command can be submitted to batch.

Verifying FIELDPROC encryption

To verify that FIELDPROC encryption is in effect, use the DSPFFD command (for example: DSPFFD MYLIBR/MYFILE). Scroll down to the field being encrypted and the FIELDPROC application name and library will be displayed:

image alt text

SECURITY ALERT: FIELDPROC encryption protects data stored on disk and on backup takes but does not, by itself, protect data exposure to unauthorized users. You must define User Controls in the next step to establish controls on which users can see decrypted values, which users can see masked values, and which users cannot see any portion of the decrypted information.

Defining User Controls

When the FIELDPROC definition enables User Control of decrypted data access, this option lets you define which users can view decrypted or masked data. Alliance AES/400 FIELDPROC will check this list of users and deny access to any user not defined in the list. Note that a user is defined as a User Profile, and includes Group profiles.

Users controls are enabled for each encryption key, or for all encryption keys. This gives you the ability to create different data access rules for different keys for the same user.

The user name *DEFAULT has a special meaning and is described below. This user definition gives you the ability to define access rules for any user not specifically defined. For example, you can create a *DEFAULT user control that enables masking of the entire field. Any user not specifically defined in user control will only see fully masked data.

Navigate to the Configuration Menu:

image alt text

Enter option 4 for Work with API user control. Press F6 to add a new user.

The following panel is displayed:

image alt text

User name: Specify a user profile. This can be a Group profile and any user in the group will be able to use this definition. Note that a User Control definition for a specific user profile will take precedence over a Group profile definition.

The user name *DEFAULT has a special meaning. When this value exists it becomes the default definition for field access. You should normally use this value and specify full field masking. When you specify the *DEFAULT user in this way, all non-defined users will only see fully masked data.

NOTE: Alliance AES/400 FIELDPROC support does not use object authority to determine decrypted data access. You can, for example, prevent the QSECOFR profile, or any user with All Object (*ALLOBJ) authority from viewing decrypted data, and you can force data masking for these users.

Key name: Enter a key name, or use the special value of *ANY. When you specify *ANY this user definition will apply to all encryption keys.

Key type: Enter option 1 for the local key store, or option 2 for the AKM server.

Description: Enter a description for this user.

Status: Enter a status for this user control specification. The value of “1” indicates that it is active, a value of “2” indicates an inactive record.

Character field masking: Enter option 1 if you want this user to only see masked data. If you select this option, specify a masking type.

Numeric field masking: Enter option 1 for if you want this user to only see masked data. Numeric fields are masked with an all 9’s value. This means that you cannot mask a numeric field if all 9’s is a valid, plaintext value. If you enable this option, specify a masking type.

Alliance AES/400 global configuration

Enter option 20 on the Configuration Menu to edit the global options. You must be the QSECOFR user or in the security group. Review the global settings and change them to meet your needs.

image alt text

Appendix A: Alliance SQLSTATE Error Codes

SQLSTATE Description
38001 SQLSTATE 38001 Error registering exception handler. This is an internal error with the FIELDPROC exit program. See the job log for more information.
38002 SQLSTATE 38002 Error initializing log. An error occurred when trying to initialize the application log. See the job log and any job output to QPRINT for more information.
38003 SQLSTATE 38003 Error setting the library list. An error occurred adding the library ALLAES100 to the library list as the second Product Library. Be sure the ALLAES100 library is available on your system. See the job log for additional information.
38004 SQLSTATE 38004 Error, invalid number of optional parameters. The Alliance AES/400 product assumes that it will be passed just one optional parameter. It received something else.
38005 SQLSTATE 38005 Error, optional parameter is null. The Alliance AES/400 product assumes that one parameter will be passed to the exit program. The parameter is null (missing).
38006 SQLSTATE 38006 Error opening file AEFLDP. An error occurred opening this file in the Alliance ALLAES100 library. See the job log for more information. Be sure these library ALLAES100 is available on your system.
38007 SQLSTATE 38007 Error reading profile record. The FIELDPROC program was not able to read the associated record in the AEFLDP file in the ALLAES100 library. Be sure that you have the ALLAES100 library available on your system, and that the file AEFLDP is not locked by some other process.
38008 SQLSTATE 38008 Field contains masked character. The encryption API failed because data masking control is enabled and the field contains a masking character. Change the data to omit the masking character, or disable masking controls.
38009 SQLSTATE 38009 Error in encryption request [nnnn]. An error occurred during encryption. The value nnnn indicates the cause of the error. You can view the error in the ANCMSG message file by using the option to view messages on the Support menu, or by using the WRKMSGF command. The message will be in the format “ATCnnnn” where “nnnn” is the error code.
38010 SQLSTATE 38010 Error in decryption request [nnnn]. An error occurred during decryption. The value nnnn indicates the cause of the error. You can view the error in the ANCMSG message file by using the option to view messages on the support menu, or by using the WRKMSGF command. The message will be in the format “ATCnnnn” where “nnnn” is the error code.
38011 SQLSTATE 38011 Error, unknown function [n]. The Alliance AES/400 FIELDPROC routine was passed a processing function that it does not recognize. Functions 0 (encryption), 4 (decryption), and 8 (initialization) are recognized functions. Contact your software vendor for more information.
38012 SQLSTATE 38012 Memory allocation error for plaintext. A memory allocation error occurred for the plaintext. Be sure your IBM i system has adequate memory. Try the operation again.
38013 SQLSTATE 38013 Memory allocation error for ciphertext. A memory allocation error occurred for the ciphertext. Be sure you IBM i system has adequate memory. Try the operation again.
38014 SQLSTATE 38014 ERROR, pPlainBuf memory allocation failure. A memory allocation error occurred for the plaintext buffer. Be sure your IBM i system has adequate memory. Try the operation again.
38015 SQLSTATE 38015 ERROR, pCipherBuf memory allocation failure. A memory allocation error occurred for the plaintext buffer. Be sure your IBM i system has adequate memory. Try the operation again.
38016 SQLSTATE 38016 Unsupported field type [nnn]. You have selected a field type for FIELDPROC protection that is not supported by Alliance AES/400. Contact your software vendor for more information.