Re: Another FieldProc question

JSMITH reads the row using an RPG read.
The value of SSN is shown to him as *****1234.
The program does: ssn='123456789' then an RPG update.
Does it accept
that and store the encrypted value of '123456789' or what? Like, "so I
can't read it, can I set the value instead"?

I think Buck is right on with his suggestion to supply feed back at the
end of his long reply.
JSMITH reads the row using an RPG read.
The value of SSN is shown to him as *****1234.
The program does: ssn='123456789' then an RPG update. Does it accept
that and store the encrypted value of '123456789' or what? Like, "so I
can't read it, can I set the value instead"?


Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From: "Buck Calabro" <kc2hiz@xxxxxxxxx>
To: midrange-l@xxxxxxxxxxxx
Date: 08/30/2018 02:43 PM
Subject: Re: Another FieldProc question
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>



On 8/30/2018 1:08 PM, James H. H. Lampert wrote:

Now suppose user JSMITH, who is not authorized to see the field at all,
changes a record in the file, and user JDOE, who is authorized to see
partially redacted contents, each change a record. In neither case do
they enter a new value for the field that has the FieldProc on it.

What happens to the value of that field?

When are field procedures invoked?

'For field-encoding, when a column value is to be encoded. Encoding
occurs for any value that:

Is inserted in the column by an SQL INSERT statement, SQL MERGE
statement, or native write operation.
Is changed by an SQL UPDATE statement, SQL MERGE statement, or
native update operation.
If the data needs to be copied and the target column has a field
procedure, it is possible that the field procedure may be invoked to
encode the copied data. Examples include the SQL statements ALTER TABLE
or CREATE TABLE (with a LIKE or as-result-table clause) and the CL
commands CPYF or RGZPFM.
Is compared to a column with a field procedure. The QAQQINI option
FIELDPROC_ENCODED_COMPARISON is used by the optimizer to decide if the
column value is decoded or if the variable, constant, or join column is
encoded.
At CREATE or ALTER TABLE time for the DEFAULT value, if the column
has a field procedure.

If there are any after or read triggers, the field procedure is invoked
before any of these triggers. For before triggers, there may be multiple
invocations of the field procedure with encode and decode operations.
The number of calls to the field procedure depends on many factors
including the type of trigger and if the trigger changes the data in the
trigger buffer. The database manager will ensure that the field
procedure is called to encode the data that will be inserted into the
table.'


https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/sqlp/rbafyfpwheninvoked.htm


There is wiggle room with understanding what '...value that is
changed...' might mean. I don't see how this scenario will work:

Current encoded value: ABCDEF
Attempted update: 123456

How could Db2 know that the FieldProc will encode 123456 as ABCDEF?
That, the attempted update is, in fact, not changing the value?

It seems to me that the FieldProc /must/ be invoked for any update that
includes the column. We humans have a logical concept of 'update'
(Sarah didn't press any keys except F3=Exit, so she didn't change
anything) that differs from the database's action during an UPDATE
operation.

I'd ask the question on the Feedback section of the manual and get an
official response.

--
--buck

http://wiki.midrange.com
Your updates make it better!

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD