Tuesday, June 05, 2007

mimicking Readers and Authors in SQL Server 2005

One of Notes and Domino's greatest strengths is the incredible security model. I've also come to appreciate how powerful it is to have that security be integrated with the application rather than having to rely on the Domino Directory. Another boon is being able to define readers and authors at the document level. Imagine if you will that Notes did not have Readers or Authors fields. Your only option for limiting access to Notes documents is solely at the view level, then relying on UI elements (computed framesets, computed embedded views, etc.) to control which view gets seen by each group of users. Ugly, right?

SQL Server 2005 does not natively implement row- or cell-level security, so that's exactly where you are in a MS development world. The security is only granular down to the table level, then you have to start using UI techniques to filter it and control access to data. And you're just praying that nobody realizes they can use Excel to connect to your database and bypass all your front end business logic, or you have to build in an interface layer that is aware of how the database is being accessed. All in all, it's pretty ugly to try to support the business use cases of letting people only edit certain records in a table.

It's not quite as bleak as I make it sound. Microsoft has published a set of instructions for hacking row-level security into SQL Server 2005. Basically it's roll-your-own authorization and while it's not horribly convoluted, it is tedious and does have to be reimplemented for every database where you want to use it. Oh, and there is no UI provided. You get to build that, and come up with a way to secure that, too. :-)

No comments:

Post a Comment