Re: User Data Segregation
- From: "Adam Machanic" <amachanic@xxxxxxxxxxxxxxxxxx>
- Date: Tue, 26 Jun 2007 14:43:06 -0400
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" <aaronakin@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?
.
- Follow-Ups:
- Re: User Data Segregation
- From: Aaron
- Re: User Data Segregation
- References:
- User Data Segregation
- From: Aaron
- User Data Segregation
- Prev by Date: User Data Segregation
- Next by Date: CLR Stored Procedure "Failed to get permission to execute"
- Previous by thread: User Data Segregation
- Next by thread: Re: User Data Segregation
- Index(es):
Relevant Pages
|
|
