One of the things I've dealt with, and seen lots of questions about in our forums, is the best way to deal with multiple clients using the same schema and lookup data for an application. I've seen two basic ways that this is handled in SQL Server: some people put everything in one database and use some type of ID to filter results. Others use separate databases for each client and either keep lookup data in a central database, so each client has two databases they access, or use some copy method, such as replication, to ensure the lookup data is in every client's database. Both of these have pros and cons and each company needs to examine their situation to determine which method makes sense.
I was browsing the World Wide Web the other day and say another option, Oracle's Virtual Private Database technology, which seems to handle this very issue. It's touted as a great way to allow custom access to each individual user, though I do see that it does require some table design changes that allow you to map users to data.
It's a fancy way of setting up Row Level Security, and while I haven't done a lot of work digging into the Oracle system, it seems like something that would be handy to have in SQL Server as a feature. It's complicated, and it might confuse lots of people, but implementing your own homegrown row-level security is complicated and confusing as well.
Of course I thought that Service Broker was a great idea as well and that doesn't seem to have taken off very much in SQL Server 2005 to date. Perhaps some automated Row-Level Security feature would end up the same way.
More and more as we move to a personalized world where every application and data set should be customized to the user, I tend to think that having robust, well-tested row level security options in the platform would be a good idea. Perhaps we'll see something along these lines in SQL Server 11.
Steve Jones
The Voice of the DBA Podcasts
The podcast feeds are now available at sqlservercentral.podshow.com to get better bandwidth and maybe a little more exposure :). Comments are definitely appreciated and wanted, and you can get feeds from there.
or now on iTunes!
- Windows Media Podcast - 28.6MB WMV
- iPod Video Podcast - 23.8MB MP4
- MP3 Audio Podcast - 4.9MB
Today's podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.
I really appreciate and value feedback on the podcasts. Let us know what you like, don't like, or even send in ideas for the show. If you'd like to comment, post something here. The boss will be sure to read it.