When looking at architecting for on-demand, the subject of single tenancy vs multi tenancy always comes up. When looking at multi tenancy, the subject of row level security often follows. Why? Well – multi tenancy at its extreme means all the tenants data co-exist in a single schema – mingling data from one tenant with data from others in the same tables.
Row level security is not something we have “out of the box” in SQL Server 2005. Oracle on the other hand does (thanks to Paul of Experian for the links):
We do have a whitepaper discussing an approach to row level (and cell level) security in SQL Server 2005 – but as you will see – it gets pretty involved, pretty quickly.
I was pleased to see my colleagues in the US have built a toolkit to simplify the implementation of this (thanks folks).
I also spotted a US based partner that has created a commercial tool for doing the same – DataNomad. I would love to hear from folks who have evaluated this tool.