On 20-May-2015 15:09 -0500, Darlene Wright wrote:
We have been tasked to encrypt several fields in a data file the
AS400. I have been researching on the best approach with the least
impact, which has brought my attention to SQL encryption using
Instead of Triggers. Does anyone have any insight with this method?
It seems somewhat straight forward using insert, delete and update.
First, the topic seems little or nothing to do with programming
RPG[LE]. Even if some RPG might be [intended to be] used, the topic
still seems non-RPG.
Second, the idea of encrypting fields in the database with the
apparent intent to minimize impacts to existing applications [at least
without FieldProc support] is quite specific to the data that would be
encrypted and how\what the applications access and update the data.
While Instead Of Triggers (IOT) on a VIEW can be helpful in that regard,
the standard *BEFORE and *AFTER triggers might suffice with even less
impact; all depends on the file layouts, keys, and relations, the
applications accessing the data and how, the intention for both the
encrypted data and the decryption of that data, and surely other things
that did not just now pop into my head.
One method of encrypting the data is to create a _sibling_ TABLE into
which to store the encrypted data; typically because the impacts to
change the columns from plain-text into encrypted binary-data requires
expansion and becomes generally problematic for applications. So the
original TABLE could remain identical, but get modified to replace the
/private/ data with something like '*PRIVATE' or '*NA' or '*ENCRYPTED'.
Necessarily there is value to create a VIEW as a join by the Primary
Key (PK) of the original TABLE to the matching PK of the new sibling
TABLE to access the since-encrypted data for each of the /private/
columns. With the addition of the IOTs, that VIEW is then
update-capable, and each /update/ [insert, update, delete] is replaced
by the same I/O to the based-on TABLEs; applications can see effectively
what was the old format, but accessing the VIEW. And when the
decryption is enabled, a query of the VIEW returns the decrypted data.
However standard insert\delete\update triggers on the original table
would necessarily want to ensure that the sibling TABLE receives the
same I/O but with encrypted data [for any application not referencing
the VIEW], while also ensuring the /private/ data columns are left
unmodified or will get the new /default/ value that exposes the true
datum is not visible [e.g. '*NA'] via this TABLE. The encryption of the
data in the sibling could be deferred to insert\update triggers. That
of course effectively renders the IOT redundant, as the triggers on the
TABLEs already handle the work... and the IOT doing an INSERT of
ENCRYPT(plain_text) would then have the effect of the sibling TABLE's
insert trigger doing ENCRYPT(ENCRYPT(plain_text)).
Do I need to use API's in order to read this data?
If using IOT, then already SQL is involved, so perhaps best to just
use what the SQL provides to encrypt and decrypt\read the encrypted
data.? Also FWiW, an old topic "Subject: _cipher API vs
Qc3Encrypt/Qc3DecryptData vs SQL":
<
http://archive.midrange.com/rpg400-l/200803/threads.html#00533>
Is there a "Read" trigger?
Yes, but of no help for showing decrypted data; see FieldProc support
in later release(s). In that message thread in the prior link, I tried
[and seems likely I failed] to explain why a Read Trigger could be of no
help, and that a new feature from the database would have to relieve the
insistence that a Read Trigger should allow presenting a program with
something other than what was in the database.
And that relief eventually came, with a new feature, but only with
the *Enhanced* FieldProc /with _conditional_ masking/ support\feature.
See my response in "Subject: Having trouble with FIELDPROC on a database
(Column Encryption on Iseries)"
<
http://stackoverflow.com/questions/29438380/having-trouble-with-fieldproc-on-a-database-column-encryption-on-iseries/29712381#29712381>
What type of Master key.
For SQL, a /password/. "The password used for decryption is either
the password-string value or the ENCRYPTION PASSWORD value assigned by
the SET ENCRYPTION PASSWORD statement."
<
http://www.ibm.com/support/knowledgecenter/api/content/nl/en-us/ssw_ibm_i_61/db2/rbafzscadecrypt.htm>
The data file remains static once we have it on our disk, just need
to retrieve it upon request.
Is the effect perhaps implicit decryption, solely to meet a
requirement to have at-rest-data encrypted?
Any insight on encrypting data fields in a file on the IBM AS400
would be much appreciated. We are currently at 6.1.
Although I have not used the support, clearly upgrading to a new
release and using the FIELDPROC feature is almost surely the /easiest/
way to make the encrypted field-data happen... with the minimal impact
to the application(s).
p.s. Notice how there was no mention of RPG in any followup commentary.
midrange.com
