Re: User Data Segregation

Adam,

Thanks for your response.

Is it best practice to be using ONE SQL login account to connect to
the database from a single web application? The application has a
client-side portal as well as an admin-side portal, both of which are
currently using the same SQL login account for connections.

Have you ever heard of any security controls involving the use of
session IDs? It would be nice to find some examples/articles from
people who have implemented these kinds of controls so I can make sure
that we do it correctly.

Thanks again.
Aaron

On Jun 26, 1:43 pm, "Adam Machanic" <amacha...@xxxxxxxxxxxxxxxxxx>
wrote:
If done well, wrapping every call so that a session ID is required will do
the job and will not cause much of a performance hit--it will be a small,
exact-match kind of lookup on hot pages, so nothing too serious. As for the
question of guarantees, there are none, at least in SQL Server. Some other
DBMSs have row-level security built in, but I wonder if BOA or another
financial services firm could win a lawsuit against Oracle if someone sees
the wrong data? Probably not--so the only way to really know almost for
sure is to:

A) Carefully design and implement the system in order to reduce the
possibility of problems architecturally and from the outset

and

B) Test, test, test! Automated unit testing is your friend. Use it early
and use it often... If something breaks, you want to find out about it ASAP.

It sounds like you're working on a Web app, so you can encapsulate
everything in stored procedures. That's half the battle won right there --
a lot of problems with row-level security schemes come from ad hoc queries
and the chance to malform them just right to see some data you're not
supposed to. A properly designed stored procedure layer should render that
kind of attack impossible.

--

Adam Machanic
SQL Server MVP

Author, "Expert SQL Server 2005 Development"http://www.apress.com/book/bookDisplay.html?bID=10220

"Aaron" <aarona...@xxxxxxxxx> wrote in message

news:1182882380.027133.128570@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

My company has a web-based application that currently uses one SQL
Server login account to connect to a SQL Server 2005 database. We
have quite a few application-based security controls in place to
ensure that when a user logs into the site, they will only see there
own data and not someone else's, but we would like to beef up our
security.

I've been trying to find out the best way to segregate data..just in
case our application-based security controls failed for any reason.
Since we have hundreds of thousands of web-users who login through the
application, it doesn't seem logical to have a SQL login account for
each one of these users...nor does it seem logical to have views for
each user.

We've considered wrapping every SQL statement from the application in
an object that will pass in the session ID, user ID, etc for
verification, but we haven't worked out the logistics of this, and it
seems like this would cause a performance hit on the database.

I keep wondering how companies such as Bank of America handle data
segregation and ensure that when I login to my online banking account,
that I will NEVER see another customer's bank account info. Would
anyone mind sharing some suggestions please?


.