Interesting idea. Personally we have similar issues, and I'm leaning
towards separate databases (if the data is truely separate). One reason is
that it's possible to extract data in insidious ways, for example:

Assume a multi-customer database with prices for each customer, so that if
the SQL says:

Select * from PRICES

We auto add "WHERE CUSTOMER_NUMBER = :CurrentCustomer"

However, if I can tweak the SQL a little I can write a sub-select like so:

Select * from PRICES P1
Where P1.PRICE > (SELECT MIN(P2.PRICE) FROM PRICES P2 WHERE P1.ITEM =
P2.ITEM)

Adding the customer number filter to the where clause means that I get a
list of all items where somebody's price is less than my price... with a
little more work this type of technique can produce just about any
information you want on other customer's prices.

Of course you can block this by making sure only trusted people can do SQL
(our system allows certain users to specify (subsets of) thier own WHERE
clauses), or by making sure your compile trigger adds the where clause to
the subselect as well as the outer select.

Of course if no one can do a select that crosses customers, then what's the
point of having it in the same database? So you need to have super users
for whom the triggers won't fire.

Another reason to use separate databases is the ability to do isolated
backup and restore of individual customer databases, and the knowledge that
it's impossible for one customer to do anything to trash another customers
data. Also, in our case we allow users to add fields. A single database
would make this impossible.

I think it all comes down to deciding to what extent each customer is
accessing a slice of a common database (e.g. E*Trade) versus to what extend
each customer is really running his own independent database (e.g. an
accounting system run on an ASP basis, which would mean each customer has
his own GL heirarchy, etc.).


-----Original Message-----
From: Jim Starkey [mailto:jas@...]
Sent: Friday, May 04, 2001 10:31 AM
To: IB-Architect@yahoogroups.com
Subject: [IB-Architect] Compile Time Triggers


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/