> I've got a persistent client who's been bugging me for a solution
> for a specific security problem. He's got a web application with
> a bunch of clients none of which are allowed to see each other's
> data.

So just create separate database for each client ...
(probably not the solution they want).

> The standard way of handling this, of course, is to add a
> conjunct to the "where" clause where appropriate. He's not happy
> that he has to trust his programmers to do the right thing under
> all circumstance, and wants a system solution.

Accessing all tables not directly but via views with appropriate "where" clauses
does not satisfy the client ? Why ?

Each such view can be written with ib-optimizer-quirks in mind,
so it can deliver better possible performance than generic solution with compile-time-triggers (IMHO).

> After coming with with all sorts of unsatisfactory solutions, I
> eventually came up with the idea of a per-table compile time
> trigger.

Do you mean only per-table, or possibly per-table/per-view ?

> The trigger would be fired during compilation of any
> database statement referencing the table. The trigger would
> have access to the environment (login account, active roles,
> connection attributes) and could abort the compilation ("bad
> dog!"), add a conjunct ("and client = 'xyzzy'), or let the
> compilation go.

Jim, do you have any estimation when IB/FB optimizer will be enhanced
enough to deserve its name ("optimizer") ?
Currently it would be very dangerous to "blindly" modify statements
on the fly because it can cause severe performance degradation due to badly
chosen plans.

(Of course that client can also use where-clause that will perplex
optimizer, but modifying requests by new trigger would make
the task of creating reasonably-fast-running-queries even more difficult
than it is now).

> So far, sounds cool. The next question is "what else is this
> good for" (which is polite way to say what corners are the users
> like to paint themselves into). For example, the compile time
> trigger could be used to track the frequency that various queries
> are compiled and/or prepared.

I do not particularly like solutions that allow such kind of side effects,
like that simple prepare can in fact cause data modification.

I expect such trigger would run in user's transaction;
but what if (after prepare) that transaction is rolled back ?
Trigger's modifications to data would be lost,
but the command would remain prepared ?

----

Would new trigger be fired even for embedded queries ?

----

Ivan