Appendix B: DB2 Field Procedures

Introduction

DB2 Field Procedures (FieldProcs) were introduced in IBM i version 7.1 in order to help simplify field encryption.  A FieldProc can be placed on a database field, which will call a user-specified “exit” program whenever data is read from, inserted into, or updated in the field.  FieldProcs are somewhat similar to database triggers; however there are two distinct advantages:

1. FieldProcs allow data to be modified (by the exit program) on a “Read” operation.  This will allow the exit program to automatically decrypt the field value before it is returned to the user or application.  No application changes are therefore needed to decrypt the data, which can dramatically reduce the implementation time for field-level encryption.
2. FieldProcs provide a separate internal space in the existing file to store the ‘encoded’ (encrypted) version of the field values.  This internal space allows you to encrypt other field types (e.g. numeric, date, time) without having to store the encrypted alphanumeric values in a separate file.

While IBM provided this “hook” into the database with FieldProcs, they leave it up to 3^rd party solutions (e.g. Powertech Encryption for IBM i) to create the FieldProc exit programs and perform the encryption/decryption functions.

Powertech Encryption for IBM i simplifies the creation and management of FieldProcs through its innovative Field Encryption Registry commands and screens.  It encrypts and decrypts the field values (within the FieldProcs) while providing full integration to Powertech Encryption for IBM i’s policy and security controls, key management and audit trails for meeting strict compliance requirements.

 

Based on authorization lists assigned to the fields, users can be granted access to the fully decrypted field values, restricted to the masked values or can be denied access to any values.

Implementing DB2 Field Procedures

Follow the steps below to add a DB2 Field Procedure (FieldProc) to a database field for encryption:

1. Add the database field to the Powertech Encryption for IBM i Field Encryption Registry using the ADDFLDENC command. Prompt the ADDFLDENC command (in the CRYPTO library) and specify the name of the field, the file it’s located in, its length, the encryption key, mask format (if an alpha field), as well as the authorization lists to control access to the decrypted values. Then choose to use a FieldProc by specifying *YES on the USEFLDPROC keyword. Press F1 on any parameter for more help.
   Example of ADDFLDENC command:

   CRYPTO/ADDFLDENC 
     FLDID(SOCIAL_SECURITY_NUMBER)
     DBFLD(CMSSNO)
     DBFILE(PRDATA/EMPLOYEE)
     DBFLDTYP(*CHAR)
     DBFLDLEN(9)
     ENCKEYLBL(SS_KEY)
     ENCKEYSTR(LIB/KEYSTORE)
     FLDMASK('*****9999')
     AUTLDEC(SSFULL)
     AUTLMASK(SSMASK)
     USEFLDPROC(*YES)
     FLDPROCOPT(*AUTH)                                                                      
   

   
   
   NOTE: The ADDFLDENC command will only add the field entry to the Field Encryption Registry.  The FieldProc will not be created until the “activate” process is performed in step 2.
2. When you are ready to add the FieldProc to the field and perform a mass encryption of the existing field values, you should run the ACTFLDENC (Activate Field Encryption) command in batch.  There should be no locks on the file at the time.  This command will add the FieldProc to the file using the SQL “Alter Table Alter Column Set FieldProc” statement, which will add the internal storage area to the field (for storing the encrypted values) and will then encrypt (encode) the existing field values.
3. To verify that the FieldProc was added to the field, you can perform a DSPFFD command on the file.  The field should show the name of the FieldProc in the CRYPTO library.  Example:



4. You can verify that the field values were encoded (encrypted) by using the HEX_ENCODED function in SQL to view the hex values. 
   Example:
   

The key identifier occupies the first 5 bytes (in blue) and the encrypted values are in the remaining bytes.

DB2 Field Procedures Facts

Listed below are some things to know about DB2 Field Procedures (FieldProcs):

• FieldProcs are supported for both DDS-described physical files and SQL-defined tables.
• Several fields in the same file can have FieldProcs on them.
• FieldProcs are supported for files with multiple members.
• No one else can be using the file when a FieldProc is being added to it, since an exclusive lock is required to perform the field encoding (encryption).
• Adding a FieldProc will not change the format level identifier on the file, so you do not need to recompile the programs that use that file.  It will not generate “level check” errors in the programs.
• If you duplicate a file (e.g. CRTDUPOBJ), it will also duplicate any FieldProcs that are on the file.

DB2 Field Procedure Performance

A DB2 Field Procedure (FieldProc) exit program will be called whenever the field is updated, read or searched (for lookups).  Below is a detailed list of events (known at the time of this writing) that will cause a FieldProc exit program to be called:

Events that call the FieldProc program to encrypt (encode) the field:

• When a new record is added to the file, regardless of the program or method used (e.g. RPG, COBOL, SQL, DFU, etc.).
• When a record is updated, even if the field value does not change.
• When a SQL lookup needs to be performed against a field (which is encrypted with a FieldProc).  For example, if a user issues the SQL statement of
       Select NAME where SSNO = ‘508998888’
  then the FieldProc will be called to encrypt the lookup value of ‘508998888’ so it can then search the encrypted SSNO field with that value.
• When a lookup needs to be performed on a keyed field (which is encrypted) using record-level operations (e.g. SETLL , SETGT, CHAIN, READE).  For example, if the user issues the RPG operation of
        SSNO  CHAIN  EMPMAST
  then the FieldProc will be called to encrypt the key field SSNO so it can look up the field with that encrypted value. 

Events that call the FieldProc program to decrypt (decode) the field:

• When a native record-level read of that field is performed from RPG (e.g. READ, READE, CHAIN) and COBOL.
• When the field is read in SQL Select and Fetch statements.
• When reading the field in a Query or through a Report Writer.
• When the field is downloaded through a File Transfer utility (e.g. Client Access, Surveyor/400).
• When reading the file with a CL command like DSPPFM or CPYF (if not creating the file).

DB2 Field Procedure Cautions

Before implementing DB2 Field Procedures (FieldProcs) in a production environment, you should be aware of the potential problems (as listed below) that may arise from their usage.  It is also highly recommended to apply the latest PTFs from IBM.

Logical and Keyed Physical Files – Not Sorted Correctly

If the field (having a FieldProc) is a key on a logical or physical, then it will be sorted by the encoded (encrypted) value on any READ operations.  For instance, if the records are normally read in key order of 1,2,3,4,5, the FieldProc may cause them to be read in the order of 5,1,3,2,4.  These “unordered” results may cause problems in your applications if they are expecting it to be sorted by the decrypted values. 

Solution A (recommended)

Fortra offers a utility to assist with encrypted key sorting when processed by native I/O (SETLL/READ).

Powertech Encryption for IBM i includes two commands that can be strategically embedded within your applications that provide a solution to the encrypted key sorting issue: STRRLASRT and ENDRLASRT. The commands can be applied against all native DB2 I/O operations. They offer a universal solution for RPG and COBOL applications, as well as other programs that use native I/O as opposed to SQL to access data.

1. Call STRRLASRT just prior to opening the file that the SETLL/READ will be executed on.
2. Call ENDRLASRT just after closing the file that the SETLL/READ was executed on.

Solution B

Alternatively, to sort the records properly (by their decrypted values), you can use an SQL SELECT statement with the ORDER BY clause on the field(s) you want to sort. The ORDER BY clause will call the associated FieldProc exit program(s) to decrypt (decode) the values for proper sorting.

Changing the Current User of the Job

When you need to change the authority for a user in order to allow them to see the full value of a field when they are normally only authorized to the masked value (or not authorized), you must change the Current User of the job. The CRRP015 program can be used to change the current user and notify the Field Procedure that the current user has changed.

Using the CRRP015 program

• The Program CRRP015 is compiled with User profile *OWNER.
• The owner of the CRRP015 program must be changed to a user that has *USE authority to the newly swapped User Profile.
• The newly 'swapped to' User Profile must be on the 'Full Authorization List' for whichever field entry is being used.

CRRP015 Parameters:

Name	Description	Type	Length	In/Out	Required
Process	Plain Text	Alpha	10	In	Yes
UserId	Current User	Alpha	10	In	Yes
MsgId	Message Id	Alpha	7	Out
MsgText	Message Text	Alpha	80	Out
Errors	Errors occurred (Y=Yes)	Alpha	1	Out

Example program call to swap the current user:

C* Change the current user
C           CALL 'CRRP015'
C           PARM '*SWAP'      PROCESS           10
C           PARM 'RBYRD'      UserId            10
C           PARM              MSGID              7
C           PARM              MSGTEXT           80
C           PARM              ERRORS             1
C* Success-> No errors occurred. Check if External Index was found (not 0)
C IF ERRORS <> 'Y'
C … Success logic …
C ELSE
C* Errors-> Display MSGID and MSGTEXT
C … Error logic …
C ENDIF

Example program call to reset the current user back:

C* Change the current user
C           CALL 'CRRP015'
C           PARM '*RESET'     PROCESS           10
C           PARM ' '          UserId            10
C           PARM              MSGID              7
C           PARM              MSGTEXT           80
C           PARM              ERRORS             1
C* Success-> No errors occurred. Check if External Index was found (not 0)
C IF ERRORS <> 'Y'
C … Success logic …
C ELSE
C* Errors-> Display MSGID and MSGTEXT
C … Error logic …
C ENDIF

Example program call to release the profile handles:

There is no need to release the handles until finished.

There is a limit in a job of how many profile handles you can create. The maximum number of profile handles that can be created in a job is approximately 20,000.

C* Change the current user
C           CALL 'CRRP015'
C           PARM '*RELEASE'   PROCESS           10
C           PARM ' '          UserId            10
C           PARM              MSGID              7
C           PARM              MSGTEXT           80
C           PARM              ERRORS             1
C* Success-> No errors occurred. Check if External Index was found (not 0)
C IF ERRORS <> 'Y'
C … Success logic …
C ELSE
C* Errors-> Display MSGID and MSGTEXT
C … Error logic …
C ENDIF
C
C* Do not set on LR
C RETURN;

LF and SQL View Limitations

• Within an LF (DDS created), a Select/Omit statement related to an encrypted field is not allowed.

• In an SQL View, a WHERE clause that conditions the View on the value of an encrypted field is not allowed. This is due to the fact that the Select/Omit, or the WHERE clause, is based on a decoded value, and the data at rest in the file is encoded after encryption. An example would be a situation in which the field SSN must be encrypted, but the view is conditioned on ‘WHERE SSN <> 0’.

• There is a potential for a delay when fully processing certain SQL requests, since Powertech Encryption for IBM i must decode the entire file prior to being able to retrieve the data. For example, this may be the case while performing an SQL statement such as the following:

  SELECT title FROM books WHERE title LIKE ‘%RPG’

  In this case, if the field ‘title’ is encrypted, and there are 2 million records in the table, all 2 million values of ‘title’ must be decrypted before Powertech Encryption for IBM i can know what is to be included in the selection. So, the execution of this SQL statement, post-encryption, could be far more time consuming than it was pre-encryption.

• Be sure to thoroughly test your SQL when implementing Encryption and Field Procedures.

Join LF Considerations

When a logical file is joining based on an Encrypted Key Field, you must first remove the Join LF, then Encrypt both physical files. Then you are able to recreate the Join LF over the top of the newly encrypted physical files. Within a Join LF, the JDFTVAL keyword is not supported if you are joining on an Encrypted field.

LF, PF, and SQL-created Table Limitations

When a key field on a file/table is being encrypted, the following attribute keywords are not valid: ALTSEQ, ABSVAL, ZONE, or DIGIT.

CHGPF – Dropping FieldProcs

The CHGPF command (using the parameters of SRCFILE and SRCMBR) has been commonly used to add new fields or change existing field definitions on files.  Unfortunately, this use of CHGPF will drop any existing FieldProcs on the file and return any encoded (encrypted values) back to their decrypted state, which may cause the unintended exposure of sensitive data in the file.

Solution: In order to add a field to a file or change an existing field definition, it is recommended to instead use the SQL ALTER TABLE statement since it will retain any existing FieldProcs.  Before you use ALTER TABLE on a DDS-described file, you should convert the file’s DDS syntax to SQL DDL syntax using a tool like Surveyor/400^tm from Fortra.

Referential Constraint Considerations

When referential constraints (also known as foreign constraints) are set up between files, and you plan to encrypt the fields referenced by the constraint, ensure that the correct APAR PTFs for your IBM i O/S are installed. Please see System Requirements in the Powertech Encryption for IBM i Installation Guide. Otherwise, the ALTER TABLE statement that runs on the activation job for the field encryption entry will silently drop the referential constraint when the fields are encrypted.

With the relevant PTF applied, the ALTER TABLE statement will then run without dropping the constraint.

Any new tables with constraints that are activated, following the application of the PTF, need not do anything extra as the PTF handles the issues during activation.

However, any constraints and field procedures that existed prior to the application of the PTF, would be considered as pre-existing conditions and as the PTF does not re-establish previously broken constraints, you must first deactivate encryption, ensure the constraints are established correctly, then reactivate encryption.

Duplicating Files – Field Encryption Registry Library

Since the Field Encryption Registry library name is “hard-coded” into any fields with FieldProcs, then duplicating the file (using CRTDUPOBJ or CPYF CRTFILE(*YES) ) into another environment will also copy these hard-coded library names into the new file.  This may cause the new file’s FieldProcs to point to the Registry in the wrong environment library, causing unpredictable results.

Solution: First create the file in the target environment from scratch using DDS or SQL. Then add the field to the Field Encryption Registry in the target environment and activate the field. Then use the CPYF command to copy the data from the file in the source environment to the target environment. The encode and decode functions will run for each record and field with FieldProcs.