Thank you for your response and it helped me to have a better
understanding of what FIELDPROC is doing.
I have adjusted my program and it is now set up to encode and fully decode
a password field (PASAAA) for authorized users while masking some of the
password to
unauthorized users when decoding.
I can see this when I perform the SQL Statement: SELECT * FROM
DBMLIB/UMAAAP00. The first four characters of the password are masked with
asterisks.
I also get the expected result when I perform a 'Data Transfer From
Iseries' on this particular file into Excel using the "Receive" button
The issue that I am having now is that I am not seeing similar results for
the commands: upddta DBMLIB/UMAAAP00 and wrkqry on that particular file.
Both of these commands seem to decode the passwords in full while I am
expecting it to be masked as well.
I have included my code below which I got help with via
http://www.mcpressonline.com/rpg/db2-field-procedures-finally-support-conditional-masking.html
UMAAAP00 Table
A R UMAAAF00 TEXT('-
A TEST ENCRYPTION')
A*
A NAMAAA 10A COLHDG('NAME')
A PASAAA 10A COLHDG('PASSWORD')
Encode / Decode Program (QGPL/ENCODE)
*==============================================================*
* Data Structure Definition
*==============================================================*
D OptParms DS LikeDs(SQLFOPVD)
D EnCodTyp DS LikeDs(SQLFPD)
D DeCodTyp DS LikeDs(SQLFPD)
D SqMsgTxt DS LikeDs(SQLFMT)
D SqFPInfo DS LikeDs(SQLFI)
DMYPSDS SDS
D PROC_NAME *PROC
D
D PGM_STATUS *STATUS
D PRV_STATUS 16 20S 0
D LINE_NUM 21 28
D ROUTINE *ROUTINE
D PARMS *PARMS
D USER 254 263
D currentUserfromPSDS...
D 358 367
*============================================================
* Standalone Variable Definition
*============================================================
D FuncCode S 2B 0
D p_FuncCode S *
D EnCodDta S 512
D DeCodDta S 512
D SqlState S 5
D i S 10I 0
D En_ary S 1 DIM(512) Based(En_ary_p)
D De_ary S 1 DIM(512) Based(De_ary_p)
D e S 10I 0
D d S 10I 0
/COPY QSYSINC/QRPGLESRC,SQLFP
C *Entry Plist
C Parm FuncCode
C Parm OptParms
C Parm DeCodTyp
C Parm DeCodDta
C Parm EnCodTyp
C Parm EnCodDta
C Parm SqlState
C Parm SqMsgTxt
C Parm SqFPInfo
*==============================================================*
* MAIN
*==============================================================*
/Free
SqlState = '00000' ;
If FuncCode = 8 ;
If DeCodTyp.SQLFST <> 452 and DeCodTyp.SQLFST <> 453 ;
SqlState = '38001' ;
Else;
EnCodTyp = DeCodTyp ;
EnCodTyp.SQLFL = DeCodTyp.SQLFL * 2;
EnCodTyp.SQLFBL = DeCodTyp.SQLFBL * 2;
Endif;
ElseIf FuncCode = 0 ;
If %Subst(DeCodDta:1:4) = '****';
SqlState = '09501';
Else;
En_Ary_p = %Addr(EnCodDta);
De_Ary_p = %Addr(DeCodDta);
e = 1;
i = 1;
For d = DeCodTyp.SQLFL downto 1;
En_Ary(e) = De_ary(d);
e += 1;
En_Ary(e) = %Char(i);
e += 1;
i += 1;
Endfor;
EndIf;
ElseIf FuncCode = 4 ;
En_Ary_p = %Addr(EnCodDta);
De_Ary_p = %Addr(DeCodDta);
d = 1 ;
For e = EnCodTyp.SQLFL-1 By 2 DownTo 1;
De_Ary(d) = En_ary(e);
d += 1;
Endfor;
// If SqFPInfo.SQLFNM = '0';
// If currentUserfromPSDS <>'QSECOFER' and
// currentUserfromPSDS <>'SECADMIN';
%Subst(DeCodDta:1:4) = '****';
// Endif;
// Endif;
Else;
SqlState = '38003' ;
Endif;
Return;
/End-Free
Sumitomo Drive Technologies
Sumitomo Machinery Corp. of America
Tel: +1-757-485-3355 ext. 8633
Cell: +1-757-822-4446
Fax: +1-757-485-7190
www.sumitomodrive.com | How are we doing?
From: CRPence <CRPbottle@xxxxxxxxx>
To: rpg400-l@xxxxxxxxxxxx
Date: 04/13/2015 04:27 PM
Subject: Re: FIELDPROC Issues
Sent by: "RPG400-L" <rpg400-l-bounces@xxxxxxxxxxxx>
On 13-Apr-2015 14:56 -0500, RWesh@xxxxxxxxxxx wrote:
I used Listing 3 in the following link to create a FIELDPROC program
QGPL/MOBHOMEPAS which should encrypt a variable char column
<
http://www.ibm.com/developerworks/data/library/techarticle/dm-1101encryptenhance/#icomments
I compiled the RPGLE program and I created a separate database
DBMLIB/UMAAAP00 for testing as follows
A R UMAAAF00 TEXT('-
A TEST ENCRYPTION')
A*
A IPIAAA 20A VARLEN(20)
A KYGAAA 11S 2 COLHDG('SALARY')
I then use STRSQL to alter the table and 'protect' field IPIAAA
ALTER TABLE DBMLIB/UMAAAP00
alter column IPIAAA set FIELDPROC QGPL.MOBHOMEPAS
ALTER COMPLETED FOR TABLE UMAAAP00 IN DBMLIB.
For some reason when I go in to add entries through UPDDTA directly
to the file itself and then do a WRKQRY to query the file and view
them I don't see them as encrypted.
Is this not how it's supposed to work? Is anyone able to assist me
with understanding?
Ultimately, I'd like to utilize this program to protect a particular
column in an existing database that we have, but I am having trouble
getting this 'simple' example to work. My assumption is that the
IPIAAA password field will be 'secure' and I will never be able to
see the contents once encrypted whether via query, UPDDTA, or other
means. Thank you for any help you can provide
The Field Procedure *can* allow the value to be decrypted; that is in
fact, the entire point of the feature. If the value was to be one-way
encrypted, then just use an INSERT and UPDATE trigger to take the
in-the-clear value and encrypt the value for storage.
Anyhow, the sample programs at that ULR will *unconditionally*
perform the /decryption/; e.g. for the "Listing 3" example program
[asterisks added for emphasis] does the inverse action that was
performed during the /encryption/ so the effects can be easily tested
for round-trip:
"...
ElseIf FuncCode = 4 ; // decode
...
// in this example, *reverse the characters as decryption*
..."
Thus the sample code could be revised to return a [special] value
such as *NOTAVL [like DSPSYSVAL QAUDLVL will do for a user missing the
required special authority] to indicate that the decrypted value is "Not
Available" to the user requesting the /read/ of the datum.
midrange.com
