Re: FIELDPROC Issues

On 16-Apr-2015 09:35 -0500, RWesh wrote:

On 13-Apr-2015 15:27 -0500, CRPence wrote:

On 13-Apr-2015 14:56 -0500, RWesh 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.
<<SNIP>>

<<SNIP>>

Anyhow, the sample programs at that URL 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*
..."

<<SNIP>>

<<SNIP>>

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>

<<SNIPped most of the code>>

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

Both Update Data (UPDDTA) [or Display Data (DSPDTA)] and the Run Query (RUNQRY) [or Option 9=Run or F5=Run w/in Work With Queries (WRKQRY)] are non-SQL interfaces. Try also one or more of Copy File (CPYF), Display Physical File Member (DSPPFM), and perhaps also a HLL /read/ such as CL Receive File (RCVF); these are also all non-SQL. If all of those consistently decode without masking... well, there is some value to consistency, even if a consistently undesirable effect.

Note: The DDL has apparently changed since the OP [column PASAAA vs IPIAAA] but the new CREATE TABLE and\or ALTER TABLE was not offered; from the described effects, a reader can infer that the program is compatible with the new definition, but best always to present the full picture to avoid misunderstandings.

I see nothing conspicuously wrong, with regard to the difficulty being described as being encountered. If the minimal code-level maintenance [as noted in the referenced article is required] is not applied, then I would expect that the lack of support for masking might be the problem [or a part of the problem]; I would not expect only the SQL data accesses to exhibit the masking. I would probably add logging to the code, and\or debug the code, hoping to discover the origin for the issue; be sure that debug is only ever run from a separate process.

Do note however, that the lack of testing for the condition of "If DB2 allows masking on this request" for which the otherwise unconditional masking is effected [per having commented-out the conditional logic], would result in corrupting the data for Reorganize Physical File Member (RGZPFM), ALTER TABLE, and ???; i.e. when the DB2 for i suggests via the parm-9 [SqFPInfo] that the database\system operation _requires_ that the data must be decoded and must *not* be masked, then do not perform the masking. Only because of both that conditional logic per the additional parameter+feedback [in decoding] and the use of SqlState='09501' predicated on a similar test per a contrivance of the data to enable "Check if value to be encoded is masked" [in encoding], can the masking even work properly; without those, the restriction is that the code unconditionally encode and decode; see the difference in the code from the first article to the second:
<http://www.mcpressonline.com/database/db2/enable-transparent-encryption-with-db2-field-procedures.html>
<http://www.mcpressonline.com/rpg/db2-field-procedures-finally-support-conditional-masking.html>