Hi,
I'm glad your clients stick around when you reboot your system ;-)

It looks to me like this will more or less implement row level security?

I wrote a Delphi component once that did similar SQL rewriting, it worked
fine, but did require the programmer to use it. (In the environment in
question, we set it up so they had little choice).

The major problem I see is that in an enterprise application framework
(i.e. ejb container) all of the users may well be logged in using the same
db user, with security handled elsewhere. Here, there's no way for the
trigger to distinguish who's who. This might be less of a problem with
reauthentication support.

An aspect that makes me a little worried is that you are adding things
going on behind the scenes. The query you supply no longer has the same
meaning as the query executed. This might get extremely confusing.

Couldn't you get the same effect by preventing all users from reading the
tables in question, and supplying each user with a view showing them only
their records? You could either just allow update insert and delete on the
base table or add triggers that only allow them to modify their own rows.
It's more complicated for your client, but it is still a system solution as
much as the compile time triggers-- in both cases you are adding info to
the db specifying access privileges. You're still requiring a programmer
to set it up correctly, just now it's the dba, and they only have to do it
once.

Still, it seems like it might be a useful feature.

david jencks

On 2001.05.04 13:31:21 -0400 Jim Starkey wrote:
> 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. 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.
>
> After coming with with all sorts of unsatisfactory solutions, I
> eventually came up with the idea of a per-table compile time
> trigger. 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.
>
> 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.
>
> Any thoughts?
>
> [Yeah, I know it is unblessed by the decoder ring set.]
>
> Jim Starkey
>
> To unsubscribe from this group, send an email to:
> IB-Architect-unsubscribe@onelist.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>
>
>