At 06:29 PM 5/7/01 -0700, David Berg wrote:

>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:
>

The killer problem with multiple databases is that you can't roll
up reports on all users for users authorized to see that sort
of stuff.

>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.
>

The trigger would fire for both the outer and inner selects, augmenting
each, so compartmentalization is not breached. Does the programmer
have to keep track of whether particular statement is in "application"
scope or "user" scope? Yes, absolutely. What the programmer does not
have to know, track, or maintain is the rule set that applies to each
scope.

>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.
>

I don't think it could be implemented without applying the trigger
to the inner 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.
>

The security model that makes this work is multiple roles, each of
which may be absent, latent, or active. A reasonable compile time
trigger would definitely be role-sensitive.

>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.
>

Different database design, different databases definitely. My
operational model is a law firm litigation system where clients
must be firewalled, attorneys see their cases, and partners and
administrators see everything. Separate databases isn't an option.

>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.).
>

I think of ASP as the CISAM on web technologies -- it works if your
standards are low enough and don't care about performance.

Jim Starkey