User authentication when using single database per client?

Question

My company is building an ASP.NET HR application and we have decided to create one database per client. This ensures that clients cannot accidentally view another client's data, while also allowing for easy scalability (among other benefits, already discussed here).

My question is - what is the best way to handle security and data access in such a scenario? My intent is to use a common login/account database that will direct the user to the correct server/database. This common database would also contain the application features that each user/role has access.

I was not planning to put any user information in each individual client database, but others on my team feel that the lack of security on each database is a huge hole (but they cannot articulate how duplicating the common access logic would be useful).

Am I missing something? Should we add an extra layer of security/authentication at the client database level?

Update:
One of the reasons my team felt dual user management was necessary is due to access control. All users have a default role (e.g. Admin, Minimal Access, Power User, etc.), but client admins will be able to refine permissions for users with access to their database. To me it still seems feasible for this to be in a central database, but my team doesn't agree. Thoughts?

Answer 1

I've always been of the opinion that security should be enforced at the application level, not the database level. With that said, I see no problem with your intended approach. Managing accounts and roles through a central database makes the application more maintainable in the long run.

Answer 2

You may want to look into using the ASP.NET membership provider for handling the authentication plumbing. That would work with your stated approach and you can still keep all of the authentication data in a separate database. However, I agree with Chris that keeping one DB will utlimately be more maintainable.

Answer 3

We have a SaaS solution that uses the one DB per client model. We have a common "Security" database too. However, we store all user information in the individual client databases.

When the user logs into the system they tell us three pieces of information, username, password and client-id. The client-id is used to lookup their home database in the "security" database, and then the code connects to their home database to check their username/password. This way a client is totally self-contained within their database. Of course you need some piece of information beyond username to determine their home database. Could be our client-id approach, or could be the domain-name requested if you're using the sub-domain per client approach.

The advantage here is that you can move "client" databases around w/out having to keep them synced up with the security database. Plus you don't need to deal w/cross-db joins when you're trying to lookup user information.

Update: In response to your update... One of the advantages to each customer having their own DB is also the ability to restore a customer if they really need it. If you've split the customer's data into two databases how do you restore it? Also, again, you'll need to worry about cross-db data access if the users are defined in a DB other than the home DB.