Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
binggeli
Starting Member
20 Posts |
 Posted - 2005-12-06 : 12:20:13
|
| Just wondering if anyone could point me to a good resource (such as an article or a tutorial or a book, etc.) that would help me figure out best practices for the following situation:1) We are an HR services provider, storing data from more than 300 clients in one database.2) So far, we haven't had any security issues with people from Company A being able to look at data from people from Company B. We don't even have any security issues with people from the same company seeing each others' data (although they sometimes try, but the system catches those attempts and blocks them out).3) December 2005: Time for a new security audit of the entire system. The third-party security consultants say we have great potential risk of "cross company information access" and my boss is getting all panicky.So, would we be better off making copies of a MASTER database for each client and then sending all employees from each client to their own database?Has anyone else had similar issues in the past? And would you mind sharing with me how you resolved it to the satisfaction of all the people involved?Thanks much,Dee |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2005-12-06 : 14:06:07
|
| In my experience, having a database per customer is not the way to go.You have "connection string" stuff to worry aboutYou don't get to take advantage of connection pooling (because you have so many databasesYou have 300 backup jobs to completeRolling out code changes to the databases gets tricky (Did I get all 300, or only 299 of 300)There are some advantages thoughSecurityCode branching (different code / database per customer) but maintainability comes into playI could go on, but Single databases are the way for me.If they think there's a security concern, address that. Don't change the whole app.Michael<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda> |
 |
|
|
DTFan
Yak Posting Veteran
52 Posts |
Posted - 2005-12-06 : 14:43:21
|
Just out of curiousity (for my own knowledge), did the third party security consultants indicate exactly how your system was at risk? I'm new to databases and have just recently started using MSDE. I'm just curious as to how you're at risk? Again, let me clarify that I know next to nothing about d/b design (especially in relation to security) so there might be 1,587,974 different security holes that can be accessed ... I wouldn't know. But as someone who is playing around with a project for the sole purpose of teaching myself both .NET and SQL Server, I'd appreciate any information you can give.Thanks.DTFanEver-hopeful programmer-in-training  |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2005-12-06 : 16:03:11
|
| One thing to think about here too is a bank or something along those lines. They have a "small database" of data for each of their customers, but I doubt they have thousands of databases.I'd try to address their specific concerns. If they just made that statement and can't back it up with any relavent data, then I'd get your money back :)Michael<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda> |
|
|
|
jhermiz
3564 Posts |
Posted - 2005-12-06 : 16:26:39
|
Why not adding a CustomerID per row to each table and create views / sprocs that return relevant data to just that customer?Or is it just an open free for all look at all the data for everyone ???Than that is a huge security risk, I dont blame your manager!
Keeping the web experience alive -- [url]http://www.web-impulse.com[/url] |
|
|
|
binggeli
Starting Member
20 Posts |
Posted - 2005-12-06 : 16:38:18
|
| Just to clarify a couple of issues:We do have ClientIDs for each client, and UserIDs for each user. Additionally, we further specify the amount of information each user can see by Access Levels, Department, Location and Division.So it's not a big old "free-for-all" that puts everyone's data at risk.Next, we're still waiting for the final report from the security audit company; but every time they make a vague and general statement about something, I spend all day providing proof to my bosses that we're still secure, and that so far not even the security audit company has been able to compromise any data.Kind of annoying, I know, and I've already contacted them and told them to stop making such general and vague statements — unless they can back up what they casually utter.Thanks everyone, I was just wondering whether anyone else had had to deal with a similar situation.By the way, we are using parameterized Stored Procedures on top of our ASP and SQL security checks; so it's not as simple as spoofing somebody else's UserID and ClientID to gain access to anything meaningful. And we also have a fairly complex security logging mechanism: Every time somebody attempts to view data that they are not supposed to see, the bell goes off.Again, thanks for everyone's feedback. I'm really interested in finding out if there is such a thing as a "Best SQL Server Practices" document (or web site) that addresses those kinds of issues specifically.Dee |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2005-12-06 : 16:47:17
|
| This sounds like a little motto I've heard...Those who can, do.Those who can't, consult.No offense to the consultants here, because I know those guys CAN do a good job. Most of the consultants I've run across usually do well to get their shoes tied in the morning.Michael<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda> |
|
|
|
|
|
|
|
|
To Split Or Not To Split (secure db design) . . .