Re: _cipher API vs Qc3Encrypt/Qc3DecryptData vs SQL

Chuck,

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.

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.

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?!?

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.

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


-mark

At 3/27/08 09:07 AM, you wrote:

M. Lazarus wrote:
>
> With all due respect, I disagree.
>
> 1) Does the official SQL state that a read trigger may only be used
> as an auditing function, thereby there must be a requirement to
> prevent changes to the buffer?

- Read Triggers are non-SQL. Their intent as designed, was for audit
purposes, for which ability to change the data between the trigger and
the program defeats that purpose. Also for not being validated by the
database, what is received by the program could be anything. If what is
coming from the database is desired to be written in any manner desired
as provided to a program [as compared to what the program was compiled
to receive], just add an abstraction layer between the database and the
program; prevent any access to the file except to the abstraction layer.

> 2) Using your logic, all other applicable triggers (add and update)
> should also prevent buffer updates. Since that is not the case, I
> believe that a read trigger should allow the same capability.

- Other triggers are not providing input to the program, but input
to the TABLE, with the typing rules enforced by the database.

> 3) As I pointed out in my original post, a read trigger would be the
> only way to implement encryption without application program changes.

- If the belief is that a read trigger is the *only* way, then there
is a failure in the understanding of what a UDF provides.

> CRPence wrote:
>
>> A UDF [User Defined Function] is what is used to redefine output [to
>> a program] reading data from a database file. Use the correct tool for
>> the requirement.
>>
>> The Read Trigger is used for auditing what was passed to the program
>> that issued the read request. If the Read Trigger could change the
>> data, then what was passed as data to the trigger for audit does not
>> match what was sent to the program; function defeated. <<SNIP>>

Regards, Chuck