Re: _cipher API vs Qc3Encrypt/Qc3DecryptData vs SQL

M. Lazarus wrote:

I guess I jumped the gun by insinuating that the read trigger is an SQL trigger feature. Since IBM calls the shots when it comes to DB2, they can make up any rule or restriction. SQL standards do have governing bodies / committees, so if a read trigger restriction is in place in the SQL spec, then I can (somewhat) understand that IBM might want to follow suit.

So find an ANS SQL spec and read what it has to say about Read Triggers. Whatever that is, will presumably be what finds its way to DB2 for i5/OS SQL. As of now, the Read Trigger is not an SQL interface. The Read Trigger function was provided as tooling that enables a [so-called] /native/ trigger to review a copy of the data that will be returned to the requesting program, and enables a Yeah or Nay decision; and any unknown variety of activity to perform, except changing the data that will be sent to the program.

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