RPGLE Trigger - Return Changed Field
Home > IT Answers > AS/400 > RPGLE Trigger - Return Changed Field
rlsantucijr
355 pts.
0
Q:
RPGLE Trigger - Return Changed Field
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)
ASKED: Apr 6 2005  9:50 AM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
WilsonAlano
2005 pts.
0
A:
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
  • AddThis Social Bookmark Button
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 How trigger programs work and note that the two parms are both "Input". Also this V5R4 discussion of Trigger buffer sections 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
Last Answered: Oct 22 2009  2:13 PM GMT by WilsonAlano   2005 pts.
Latest Contributors: TomLiotta   8025 pts., password98   0 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

Karl007   0 pts.  |   Apr 7 2005  10:13AM GMT

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…

 

BigKat   2540 pts.  |   Oct 22 2009  2:42PM GMT

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.

 
0