Re: anyone with ANY experience with FIELDPROC's and at rest ENCRYPTION?

[Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

Jay

You've run into a strange beast. Here's what I think I know about this, 
after working with a company that does encryption.

In RPG, if you read the table using RLA, it is sorted using the 
encrypted value, not the original, decrypted value. The decrypted value 
is displayed, the order comes from the encrypted value.

This is different from SQL, which decrypts the value before doing the sort.

This will give very strange results in RPG. To avoid it, you can use 
embedded SQL to read the table.

I don't know why this was the design for RPG - it feels wrong-headed, 
but there it is.

HTH
Vern

On 5/7/2019 9:21 AM, Jay Vaughn wrote:
> So we have developed an in-house at rest  encryption solution for sensitive
> columns using FieldProcs and QC3 crypto api's...
>
> Though it has been a challenging feat, we are happy with where we are with
> it and how it is going to serve us.
>
> We have one strange "behavior" that I am hoping someone can explain to us.
> And though we know the challenges that come with encrypted key fields and
> masked key fields, we have overcome them and have made appropriate changes
> in our application layer for such.  Tested - works great.
>
> Here is what is strange...
>
> We apply the fieldproc to our column.
> It attaches and encrypts all data.
> IMMEDIATELY after this, if you access the column with an sql such as...
> (for a userProfile with masking selected)  .
> select encryptedColumn
> from encryptedTable
> where encryptedColumn >= someValue
> order by encryptedColumn
>
> The results are NOT correct and in fact appears to "alter" the last 3
> digits in the column with 0... Though when you decrypt, all data is
> restored to its true value.
>
> Now the solution we have found - though doesn't explain anything - is that
> we include one more step after the  2 above steps (attaching and
> encrypting) to lastly, call a pgm that uses RLA and accesses the index/LF
> via the encrypted column and 1.) do a chain with loval to the index/lf and
> then 2.) setll/read through all rows of the table.  Then, upon executing
> the above sql (for a masked user), the results show correctly.  It sounds
> like some kind of strange "initialization" process that is prep'ing the
> encrypted key column data.
>
> So... we have a solution... it works and is just another small step in
> applying the FIELDPROC... but why is this necessary?
> Based on our 2 year run at this, and lack of feedback we'd always get from
> online on this topic I'm not counting on much... but being here at the end
> and last "odd behavior" to be explained... i thought I'd see if any new
> experiences in this area could shed some light.
>
> Please save all the doom and gloom negativity about how this is for 3rd
> parties only to roll (such as Townsend)... And that encryption is like
> quicksand and it's going to suck us under. :)  Because it hasn't.
>
> TIA
>
> Jay