In my last article, I wrote how we can encrypt sensitive data in our DB2 database.

To recap; IBM has given us the ability to take a field, or column, and let us apply security, or encryption, to that field/column.  It is up to your FieldProc program to perform the measures you have taken to achieve this task.

For more information about FieldProc’s my last article can be found here.

So, moving on to testing.

If we have implemented FieldProc’s onto a column, how do we ensure that column is being correctly encrypted?

If we use SQL, or any other method, to display the column information, our FieldProc program will kick in and decrypt the contents.  We can never see the encryption!

This article will show you how we can put a few extra bits of code in our FieldProc program to capture its workings.

For this example, I will be using a National Insurance table which has a FieldProc associated with the National Insurance Number column.  RPG was used to code the FieldProc program.

As can be seen from the figure above, we will capture both the raw values of the column and the encryption details and store them in an audit table, which can then be queried to see the encryption workings.

The following SQL was used to create the National Insurance table:

— Create National Insurance Table   

Create or Replace Table FormaServe.NatIns (

  EmpID Integer Not NULL Generated Always As Identity ( Start With 1, Increment By 1, No Cache ),                                           

  EmpNat Char( 20 ) );

 

Now we associate our RPG program as a FieldProc on the National Insurance column.

Alter Table FormaServe.NatIns

   Alter Column Emp_Nat Set FieldProc Formaserve.Fs001fp ;  

 

If we need to check the FieldProc has been associated with the correct column, we can run the SQL below to list all the FieldProc’s on our server.

Select

FIELD_PROC AS Field_Proc, Column_Name as Column, TRIM(TABLE_SCHEMA) || ‘/’ || TABLE_NAME AS Table

   FROM QSYS2.SYSFIELDS    ; 

 

And yes, the National Insurance column has the FieldProc FS001FP1 associated with it.

Next, I created the audit log, again, I will use SQL to accomplish this, calling the table FormaServe/NatMapP.

 

— Create Mapping Audit Table

Create Or Replace Table FormaServe.NatMapP (

    NI_ID Integer Not NULL Generated Always As Identity ( Start With 1, Increment By 1, No Cache ),                     

    NI_Func SmallInt,                      

    NI_Raw Char( 20 ) ,                         

    NI_Enc Char( 40 ) ,

    NI_TS TIMESTAMP DEFAULT CURRENT_TIMESTAMP

 ) ;

 

Next, we need to amend our RPG program to populate the audit table.

1. Add a file spec to use the Audit Mapping tableDcl-F NatMapP Usage( *Output ) Rename( NatMapP : NatMapR ) ;

 

1. Write a procedure that populates the audit log NatMapP.

  // Write to Audit Log

        Dcl-Proc WriteAudit ;

 

         Dcl-Pi *N Int( 3 );

         End-Pi ;

 

            // Standalones

          DCL-S retField Int( 3 ) ;

          Dcl-S Pos Int( 10 ) ;

 

          retField = 0;

          ni_Enc = EnCodDta ;

          ni_Raw = DeCodDta ;

          ni_Func = FuncCode ;

          ni_Ts = %Date()+ %Time() ;

 

          Monitor ;

            Write NatMapR ;

            On-Error   ;

              retField = 1 ;

          EndMon;

 

         Return retField ;

 

       End-Proc ;

 

1. Call the procedure if functional code 0 (Encode) or 4 (Decode) is passed back from the DB manager.

  // Populate Audit Log

rc = WriteAudit() ;

 

By displaying the audit log, we can see both the encrypted and decrypted values.  All looking good!

Debugging

As we are talking of testing, it’s worth a mention of debugging our FieldProc’s.

As our field procedure programs often run in a secondary thread to our job, it is favourable to start a service job to debug our service program.

If we are using green screen to test our FieldProc program we have to start another interactive 5250 session and use the Start Service Job (StrSrvJob) on the job in our first screen, session A

Then the start debug command, on the B session, for example STRDBG FormaServe/FS001FP.

Once you run any database command on your table on the A session, it will break into the debug command on the B session.

These steps are detailed in the figure below.

You might find it easier to use the IBM i Graphical Debug utility.  I wrote an article on this product in a previous article on PowerWire.  This can be found here.

When starting debug, we use the details of the connection in our Run SQL scripts – Nice and easy!

Warning

Please be aware that anyone who can access this mapping table could gain significant insight into the encryption process you are using.  It is recommended you only use this testing method to ensure your FieldProc program is working as intended and should not be used within your production environment.

Conclusion

IBM has provided a very flexible method to allow us to control the encryption of our database columns, hopefully this article has given you an insight of one method to test our FieldProc’s.  I would be interested to know of any other methods you use to assist in testing this functionality.

A big thank-you to the guys at Developerworks for the explanation, and examples, of how FieldProcs work and also Kent Milligan’s articles that were used as a basis for these topics.

The next UK IBM i user group meeting is on Thursday 2nd November at The Mount Hotel in Wolverhampton; full details can be found on the iUG site here. I will be giving a presentation on Rational Developer for i – Hope to see you all there!

Andy Youens is an IBM i consultant/instructor at Milton Keynes, UK-based FormaServe Systems.