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

Sounds logical.  RPG uses the index, which is over the encrypted data, 
SQL sorts after it reads, almost sure it does not use the index.  It can
make a difference in speed.

Creating an index over the plain data will store the information in the
index, which is not easy to read, but not so hard either.

On 5/7/19 6:49 PM, Vernon Hamberg wrote:

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

-- Este e-mail fue enviado desde el Mail Server del diario ABC Color --
-- Verificado por Anti-Virus Corporativo Symantec --