Re: _cipher API vs Qc3Encrypt/Qc3DecryptData vs SQL

Chuck,

You make some good points about the results of a CPYF, but that is not an insurmountable obstacle. The same concerns can be had for an *INSERT trigger. If the target file of the CPYF has an *INSERT trigger, it can mess with the data, right? So to me the challenge is fine tuning the rules and capabilities of the changeable read trigger, not abolishing the concept completely.

Of course STRSST, *SAVF's and journals do not follow DB2's trigger rules. You can't put a trigger on them so that's a moot point.

Although I've tried to keep this discussion fairly generic, I had gone through this discussion a while back with a buddy that was trying to create a commercial utility to encrypt data when saving to tape. Since the encryption needs would vary from customer to customer, the application would have the flexibility to encrypt on a file by file and possibly on a field by field basis.

Naturally, the utility would have no expectation of access to source or the rights to change any DB file layouts. A changeable read trigger was the natural solution. Since that option was (and still is) not available, I believe that they ended up offloading the data to an external, dedicated piece of hardware to accomplish the task.

That solution has several drawbacks, including a lot of extra network overhead, performance issues (since the file is effectively being handled twice - once to send it to the box to be encrypted and then sending the encrypted version to tape) and assuming that there's sensitive data involved here (it is an encryption scenario, after all), there's greater exposure that the cleartext version can get sniffed.

I certainly appreciate that there are considerations to take into account, but I still don't think that the idea should be shelved.

-mark


At 3/27/08 11:55 PM, you wrote:

M. Lazarus wrote:
>
> The suggestions made provided some interesting alternatives that
> come close to a changeable read trigger, but don't go all the way.

I did omit mention INSTEAD OF triggers. But given an application
would be redirected to VIEW alternatives, that would be a requirement to
achieve the abstraction by VIEW when the application is other than
read-only. The topic did originate with /read/, so I was not really
commenting about Insert/Update/Delete.

> Since you have thrown down the gauntlet with: "No example has
> been provided of an application for which a Read Trigger changing
> the data sent to the program would be the _only_ solution.", I
> will attempt a scenario.

Remember as this unfolds, the situation is an application.

> 1) Application source is not available (canned application, no
> programming resources available, no time to change all programs
> that may change access a particular file, etc.)

Reasonable assumption about the source & canned application.
Reasonable expectation that not all programs that may access a
particular file will be changed; i.e. undesirable, and effectively
impossible without source, so the goal is to make the application
function unchanged by introducing an abstraction of the access to the
data. I will dismiss the /no programming resources/ comment as being in
regard to finding and changing all of the programs in the application
[even if this like /change all programs/ seems to contradict the noted
/no source/ part of the scenario], and assume that the specific set of
file(s) is known by names, to be those which are operated against by
those programs and for what action; i.e. what the application does with
regard to database files.

> 2) Mandate is to implement encryption in a few fields in some
> file(s).

Any one field is merely an extension of concept to more than one
field, and similarly for any one file, merely an extension to more than
one file.

> 3) Company policy allows user access to the encrypted fields
> based on some encapsulated rules

The policy rules? OK... I will make it up; extend the rule to many
rules, as noted above with fields & files. There is the one function
which determines if the encrypted field in the file being opened allows
the current process to access the data:

Is_Allowed() /* Returns '1' if process may effect Decrypt() */

> 4) Rules must be enforced regardless of access method, including
> SQL, DSPPFM, RPG programs, etc. Clear text gets returned to
> authorized users and blank (or maybe null or other special value)
> returned to unauthorized users.

Bzzzt! Gone too far. Changed the requirements to go beyond the
application... So here I am sure we will hear the claim that the Read
Trigger is thusly, the _only_ way to achieve the goal, since the new
requirements to affect DSPPFM and /all access methods/ has been thrown in.

These new requirements however are not only an inappropriate attempt
to bypass the /implied ground rules/ of /an application/ but suggests
that direct access to the physical data be visible in its decrypted
form, outside of the application. So should STRSST D/A/D also decrypt
for the authorized user? Should the saved physical data copied to a
data file have the decrypted data visible to the authorized user?
Should the journal data be decrypted for the authorized user? Sorry,
but the physical data is just that, and making the *application*
functional does not include every access method to the physical data.

If that is not clear enough to convince of the folly of the new
requirements, consider if the change-capable Read Trigger were active in
the following scenario which effects that result on both DSPPFM and CPYF
operating against the physical dataspace:

Given: PF L1/F1; CRTDUPOBJ F1 TOLIB(L2);

Action: DSPPFM L1/F1 /* as Aut User, see decrypted data; not actual
physical data -- Oh No! The data in the file is not encrypted according
to the business rules! */

Action: CPYF L1/F1 L2/F1 /* as Aut User, F1 in L2 now has decrypted
data, but the duplicate file has the same rules as the original file.
Oh No! The data in F1 is not encrypted */

Ah but What if?: An Insert trigger on F1 ensures data is encrypted
going in. Well if an Aut user inserts the rows copied from L1/F1 into
L2/F1 then the rows are correctly encrypted. Ahhh... that's better.
But what's this!? If an unAut user inserts the rows copied from L1/F1
into L2/F1 then those encrypted rows are an encryption of the already
encrypted data! That's not right!

It seems to me that the new requirement breaks down miserably
regardless of Read Trigger or otherwise. It is worth noting that even a
new application implementing this functionality will have similar
difficulties, so the physical dataspace can not be part of the game
plan. The physical dataspace is best unchanging for read, except by a
*function* as requested by, known to, or unknown to but compatible with,
the program. The triggers effect encryption on input, implementing the
encrypt business rule in the database instead of all access methods.

The typical encryption requirement is that all physical copies have
the encrypted data, and all views of the physical data are encrypted,
irrespective of the user Is_Allowed() to decrypt. Another reason why
the new requirement is bogus.

Nevertheless there are /solutions/ to some bogus requirements as
well, although surely unacceptable since they would not be _easy_ like a
change-capable Read Trigger. Something which if it existed would
/solve/ that requirement, while ignoring the obvious flaws in a change
capable Read Trigger concept, for its ever-probable horribly confusing
and disastrous outcomes.

Since the intent of DSPPFM is to show the content of the dataspace as
it is, that is just another example of where a normal Read Trigger
enabling the change of data is a bad idea. It is desirable for auditing
that a Read Trigger be invoked for DSPPFM, but undesirable for anything
which would equate to a change-capable Read Trigger.

The implied /ground rules/ were to make the *application* be agnostic
of the change. The DSPPFM is not an application, it is instead a
dataspace viewing utility. Any access directly to the dataspace,
outside of the application, should correctly see only the unchanged
physical data. To see the massaged data requires the application be
redirected to the access method which encapsulates the massaging of the
output.

> 5) Due to item #1, file formats must remain the same (LVLCHK(*NO)
> is not a consideration.)

Not sure what this means? CHGLF LVLCHK(*NO) is not allowed?

If that is a new rule, then as required, patching the LFs is going to
be a new rule from me. A patch is reasonable, because a compatible
format which is acceptable to fake out the application, sufficiently
such that the application remains truly unchanged, then it should not
matter if LVLCHK(*NO) is employed; this application is static anyhow,
there is no source. Any level checking could be done as a programmed
user-defined level check instead of common Data Management check, as
part of a user defined database open intercept, if some protection is
deemed an absolute requirement after defeating the DM LvlChk.

> 6) Also, since there's no source, the file
> names may not be changed in the programs. Changing the names of
> the files themselves is also not a practical solution.

I split this from five, and made this six. This is the easy part.
The VIEW objects replace the physical files, and thus all operations are
against the VIEW or a shared query ODP of the VIEW. Either overrides or
a VIEW with the name of the original physical file; in the latter case,
although the original file has its name /changed/, the application can
still operate against that same file name.

> The above scenario is not very farfetched.

No. But if the source was missing, IMO the better solution would be
to rewrite rather than to waste resources trying to fake out the
existing application; then there is source, and thus not only can fixes
be made, but so too enhancements.

Simplest case as read-only app on file non-key F with field C to
encrypt\decrypt, something like [pseudo-code]:

create table F_PF as
( select column_list_omit_C
, cast(NULL as varchar(encr_aligned)
for bit data ) as C
from F )
with no data
insert into F_PF
select column_list_omit_C
, encrypt(C)
from F
drop table F
create view F as
( select column_list_omit_C
, case Is_Allowed()='1' then decrypt(C) end
from F_PF )

> Would a VIEW and UDF fit the above scenario? I don't believe
> that it would.

VIEW and UDF fit. Note that would atypically be /a/ as in one, nor
one of each; each are created in combination with the requirements of
what the application does, for how to make the application function
unchanged, by adding implementation of database and common data
management functions outside of the existing application.

> Even if you think that the example is contrived, would you agree
> that the read trigger would certainly be the easiest to implement?

Easiest? Again the rules changed? I was contradicting the claim
that a change capable Read Trigger was the _only_ way.

Regardless, while easiest may be true in a narrow view, a Read
Trigger changing the data in the buffer is not a good idea.

So with what we have so far... Is it time to create a simple
read-only or CRUD application, even if just as detailed description of
the file, its data, its field encryption and decryption requirement
[Is_Allowed() should be sufficient as is], and the functions performed
against that file.? I should not write the scenario, to avoid any
accusation that I have oversimplified or did something specifically to
escape some trap. Then we can start a new thread with an appropriate
Subject, and let's write up the solution.

Regards, Chuck