RPGLE Trigger – Return Changed Field

445 pts.
Tags:
RPG
I have a file with an DES encrypted field on the i5 - V5R3. I wrote the program below as a trigger to decrypt the field and return the clear field to the program that initiated the read. I know the decryption works in the trigger but the decrypted data is not returned to the reading program. Is what I am trying not possible? Thanks. Bob D* Start of TbBufDs D TgBufDs DS D TgFile 10 D TgLib 10 D TgMbr 10 D TgMbr 10 * EVENTS: 1=INSERT,2=DELETE,3=UPDATE,4=READ D TgTrgEvt 1 * TIME: 1=AFTER CHANGE,2=BEFORE CHANGE D TgTrgTime 1 D TgCmtLvl 1 D TgReserve1 3A D TgCcsId 8B 0 D TgReserve2 8A D TgBfrOfs 8B 0 D TgBfrLen 8B 0 D TgBfrNulOf 8B 0 D TgBfrNulLn 8B 0 D TgAftOfs 8B 0 D TgAftLen 8B 0 D TgAftNulOf 8B 0 D TgAftNulLn 8B 0 D TgBufChr 1 32767A D TgBufAry 1A Overlay( TgBufChr ) D DIM ( %Size( TgBufChr ) ) D* End of TbBufDs D PARM2 DS D LENG 1 4B 0 D BEFORE E DS EXTNAME ( ACCRECP ) D BASED ( beforePtr ) D QUALIFIED D AFTER E DS EXTNAME ( ACCRECP ) D BASED ( afterPtr ) D QUALIFIED D beforePtr S * D afterPtr S * C *ENTRY PLIST C PARM TgBufDs C PARM PARM2 C EVAL beforePtr = C %ADDR ( TgBufAry ( TgBfrOfs + 1 ) ) C EVAL afterPtr = C %ADDR ( TgBufAry ( TgAftOfs + 1 ) ) C SELECT C WHEN TgTrgEvt = '4' C EXSR @SubBefore C EVAL %SUBST (TgBufDs:TgBfrOfs+1:TgBfrLen) C = BEFORE C OTHER C ENDSL C EVAL *INLR = *ON C RETURN C @SubBefore BEGSR C EVAL Before.SomeField = ClearData C ENDSR ADDPFTRG FILE(mylib/myfile) TRGTIME(*AFTER) TRGEVENT(*READ) PGM(mylib/Decrypt) RPLTRG(*YES) ALWREPCHG(*YES)

Answer Wiki

Thanks. We'll let you know when a new response is added.

A trigger program in my experience cannot return a value.
What you are looking for is a UDF ( user defined function )
in SQL. Then, instead of doing a read or chain, you do a fetch on a cursor that looks somewhat like this :
select a, b, c, yourudf(encryptedpassword), from file
reagrds,
Antoon

================================================================

A trigger doesn’t return data to any program that causes the trigger to fire. See this V5R4 short description of <a href=”http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=/dbp/rbaforzahftrb1.htm”>How trigger programs work</a> and note that the two parms are both “Input”. Also this V5R4 discussion of <a href=”http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=/dbp/rbaforzahftrc.htm”>Trigger buffer sections</a> links to field descriptions where the ‘New record’ buffer subfield is defined as “A copy of the record that is being inserted or updated in a physical file as a result of the change operation. The new record only applies to the insert or update operations.”

The direct implication is that it doesn’t exist for read operations… even if the buffer was ever returned.

Tom

/////

To accomplish what you want (retrieve decrypted data from a file) you will need a SQL view with a UDF to that column to decrypt it. I had the same problem and the solution was create a UDF to call a subprocedure from a service program to decrypt the data, create a view and use that UDF to retrieve the data decrypted from the table.

To control access to decrypted data, the same subprocedure can make some validations over an authorization list or something like that.

Wilson

=================================================================

To accomplish it, I’d probably just use the builtin ENCRYPT_xxx/DECRYPT_xxx scalar functions of SQL rather than code a new UDF(). Available in current releases. Might as well have IBM on the hook for it.

Tom

/////////////////////

Tom, you are right but to add some code like control access over the view and the encryption key, the scalar function will not be enough.

Wilson

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Karl007
    I would put the "clear data" in the *AFTER* buffer instead of putting it in the *BEFORE* buffer. (just guessing though) On the other hand, I'm wondering what a database encripted field would be good for if you have a read trigger that unveals the original data anyway? Wouldn't this mean that anyone who takes a look at your file using ODBC or SQL or whatever, would see what's behind the encripted field? So what use would the encription have then? Except of course if your trigger will decript only if certain job environment conditions are met...
    0 pointsBadges:
    report
  • BigKat
    At a previous employer, they had this working where the trigger decrypted the data to the program on reads and encrypted it on writes and updates. The trigger also looked at the calling program and the userid to determine if it should decrypt. They had 2 fields in the file for each encrypted field. encrypted and clear. The clear field was always empty in the database. The trigger (if proper program and user) populated the clear field upon read (after). The user maintained the clear field (they never directly saw the encrypted field on the screens) and upon the write or update (before), the trigger encrypted the clear field into the encrypted field and cleared the clear field (for proper user and program). For non-proper user or program, the clear field was always made clear on read or write/update and the encrypted field was always set to the before image on write/update. Only a director or two had authority to the file that contained authorized users.
    7,935 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following