Posted by Erland Sommarskog on 06/18/05 12:30

[posted and mailed, please reply in news]

Alex (avilner@gmail.com) writes:
> The database will house data for multiple customers, and the
> requirement is to have no customer see other customer data. Web server
> will be responsible for authenticating users (ids and passwords will be
> maintained there/possibly stored in the database, but will not be
> actual database logins) -- and establishing pooled connections to the
> database, using some sort of a proxy login.

If security is your prime concern, I would recommend having one database
per customer. In theory, it is not a big to deal to key tables with
user id etc to separate customers, but bugs occur, and the result
can be fatal.

Of course, having multiple databases brings on other problems, particularly
when it comes to maintaining tables, stored procedures etc. Good routines
for configuration management is essential.

> 1. Users can only see their own data and NEVER can see anybody else's.

This can be achieved with views relatively easy. However, it is possible
for a skilled person to get some information from such a view, even he
does not have direct access to the data. As long as access is only
through the application, you can probably ignore this risk. But next
you talk about users being able to create their own reports, which
would mean that they have access to some query tool and direct access
to the database.

> 2. Users' access to the database is logged (this includes updates as
> well as queries).

Look at http://www.lumigent.com. Their Entegra product can handle this.
It't may not work too well, if you use a proxy login though.

> 3. We would like to provide some sort of a reporting mechanism -- where
> users can form their own queries. So, something like a Crystal or an
> Access front end, that users are typically familiar with... The problem
> is that this requires users having direct access to the database (?),
> and opens up tables (unless, there is a way to create parameterized
> views that will always restrict access to a subset of records).

I can't see how a proxy login would work in this case.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]