Secure/selective data access

[Alex]

Dear netters,

We are looking to build a SQL Server database that will be hooked up to
a Web server (WebLogic or a .NET).

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.

Ideally, we do not want to have the database itself visible to "the
world", it should reside behind a firewall, and be accessible through
the Web server, which will do all authentication and routing.

We want to have either tables or views defined in such a way that:

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

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

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

Any advice on how to best implement this is greatly appreciated.

Thank you in advance!

[Stu]

Step 1 will be to hire an experienced database developer. Not trying
to sound sarcastic, but with a project of this size, you'll need
someone with experience. Otherwise, you'll open up a lot of security
holes as you are attempting to figure it out.

Second, read up on stored procedures. These should be used as your
method of retrieving information from the database, and they should be
written to require user-specific parameters, therefore mitigating your
exposure.

Third, there are a number of reporting tools that offer client-side
GUI. We're working with XtraReports, but I'm sure there are others.
Your application design should retrieve the data (via stored
procedures) into client-specific resultsets. Your clients can then use
the reporting tool to analyze ONLY the data that you provide in the
structure that you provide.

HTH; I'm sure others have some opinions that they'll share.

Stu

[Alex]

Stu,

I totally agree with your comment about the experienced DB developer.
However, for us to be able to make the determination, we need to be
prepared and understand what architectural solutions others are using.
Therefore, my questions still remain -- what are the best practices
that people in the industry use for similar types of applications?

Thanks!

--Alex

[Stu]

I guess I don't understand your question; it's late, and I probably
shouldn't be trying to help anybody at this point :) I'll try to
answer the two questions you posed previously using some experience
from my past, but I don't know how much detail to provide.

We have our web server on a DMZ, and our database on the inside of a
firewall. We restrict port access from the DMZ to the firewall using
SSH, and we use SQL Server logins (as opposed to Windows
Authentication) to avoid opening up every freakin port in the book
(note to Microsoft; is there a better solution to Windows
Authentication across a DMZ?).

We use stored procedures to access the database; a primary component of
each stored procedure is a UserID (which is associated with a user's
login). Users are restricted to their data by that userID; in other
words, user X cannot view User Y's data because their id's don't match.
Our UserID's are GUID's, in order to prevent "ID guessing"

Logging gets a bit tricky, because every stored procedure is executed
using the context of the SQL Server ID used in the connection string.
Our logging needs have alwayse been simple, and so they are met by a
transaction queue, where the userID and data action details are
written.

Is that what you mean by "best practices?" I guess I'm just trying to
assure you that what you want to do is possible, and this is A method
for doing so. The actual mechanics of implementing this methodology
can be complex, depending on the nature of the database.

HTH,
Stu

[Erland Sommarskog]

[posted and mailed, please reply in news]

Alex (av*****@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, es****@sommarskog.se

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