Re: _cipher API vs Qc3Encrypt/Qc3DecryptData vs SQL

Chuck,

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

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.

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.)

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

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

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.

5) Due to item #1, file formats must remain the same (LVLCHK(*NO) is not a consideration.) 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.


The above scenario is not very farfetched.

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

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

-mark


At 3/27/08 06:20 PM, you wrote:

M. Lazarus wrote:
> I understand what that the original intent was to use as an
> auditing tool, but to me, that's of little value as an everyday tool
> and as a foolproof auditing tool. That's because the trigger program
> receiving the buffer can and probably will do something with the
> data, which is being copied from the input buffer. At that point the
> program can do anything it wants to the data, right? So not allowing
> a buffer change so it can be a "secure" method is a red herring. For
> me it becomes an esoteric function that's nice to have when you
> really need it, but not something I can use every day.

There is no automatic logging of what the program was allowed to
read; that is the domain of the Read Trigger, if it were programmed to
do so, for example, as an audit trail. The Read Trigger can do nothing
to the data; the original data requested by the program is returned
without any change by the Trigger program -- and I never used the word
/secure/, so not sure what point is trying to be made with that mention.
The issue was that the Read Trigger program could not change the
data.?.? Was that not a complaint being expressed, to which I have
responded was by design?

> As to putting an abstraction layer between the database and the
> program, let's be realistic. It's not a trivial change to the
> application software design - and that's even when it's part of the
> initial design! Retrofitting an abstraction layer to an existing
> system? No way! Unless... there's some way that it could enforced
> by the database. Hmm... maybe a read trigger?!?

SQL VIEW can provide an abstraction layer. One or more UDF may be
active in the VIEW. A UDTF can produce whatever is desired, and even
provide a seamless abstraction layer to the program, as read from a
VIEW; even returning data in any sequence desired w/out parallel processing.

> Correct me if I'm wrong, but wouldn't the UDF method require
> accessing the data via SQL, not native I/O? If that's the case, then
> this method will not work without major rewrites and testing. Again,
> not acceptable for most shops.

Native I/O can read a VIEW, or an OPNQRYF ODP which queries a VIEW.
The SQL VIEW is the encapsulation of the query with the UDF, and the
VIEW redefines the Record Format for the native I/O. When a VIEW can
not effect ordering, then OPNQRYF queries the VIEW to define ordering.

> So my statement: "a read trigger would be the only way to implement
> encryption without application program changes" is still accurate!

An inaccurate and unproven claim, now, just as much so as it was
before. 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.

Regards, Chuck