Question

Our requirement is something like this.

We are building a multi-tenant website in ASP.NET MVC, and each customer should be able to create their own users as per predefined user roles.

We are thinking about to create a schema for few tables which would be common for customers. So customer can login to system according to their schema logins and we need not to alter any queries to serve all of them.

We are referring http://msdn.microsoft.com/en-us/library/aa479086.aspx Shared Database, Separate Schemas.

Can someone suggest on following 1. After creating schema how to authorize user against a particular schema 2. Is this possible that without any changes in queries db can serve multi-tenants

Thanks in advance Anil

Answer 1

After much research, I can say that, although it takes more development up front and more checks along the way, shared database and shared schema is the way to go. It puts a little bit of limits on how easily you can cater to a client s specific needs, but from my point of view SAAS isn t about catering to a single client s weird needs. It s about catering to the majority of clients. Not that it s a SAAS but take iPhone as an example. It was built to cater to the masses. Rather than focusing on doing everything it s built to be one-size fits all just by its simplicity. This doesn t help your case when it comes to authoriztion but it ll save you dev hours in the long run.

Answer 2

If you are asking this in the context of SQL Server authentication/authorization mechanism, i can asnwer this question with saying that every user has a default schema which helps query engine to find out required object in the database.

SQL Query Engine will look at the user s default schema first to find the required object (table). If it founds the object in user s schema then use it, otherwise goes to system default schema (dbo) to find it.

Check this article s How to Refer to Objects section to find out how it works. The article also has some information about security concepts related to schemas.

友情链接