Implementing multi-database, multi-provider authentication system...

Question

We have started building an asp.net mvc application. Application will consist with one main database with users, projects, common tables etc... and many databases (all with the same structure) with a data relevant to a particular project. Use can have some global roles (stored in a main database) and some project specific roles (stored in a project database) and each user can be linked to many projects.

My goal is to build an authentication system that will support classical username/password authentication and also an OpenID authentication (we are using DotNetOpenAuth for this purpose) and authorization system that will support the roles system which I described above.

But I run into several question: 1.) I think that we should support both (username/password and Ppenid) authentication options for a single user, so that username/password users won't need to create additional account when they decide that they will use an OpenId and I think that we should support several OpenId's for a single user like SO does (if some provider is down).

2.) I think that the best database for this would be:

table Users (UserId (PK), LastActivityDate)
table UsernameLogins (UserId (PK,FK), Username, Password, IsApproved, IsLockedOut, LastLoginDate, LastLockedOutDate, etc...)
table OpenIdLogins(OpenIdUrl (PK), UserId(FK),LastLoginDate)
table Profiles(UserId(PK,FK), DisplayName(Unique), Email (Unique), FirstName, LastName, Address, Country, etc...)
table Roles(RoleName (PK), RoleType(1=GlobalRole,2=ProjectRole).
table UserRoles(UserId(FK,PK), RoleName(PK)).

3.) Should I create my own providers (MembershipProvider, ProfileProvider, RoleProvider)? Its seems that MembershipProvider is not so appropriate for an OpenId authentication (and of course I can only support just basic methods (GetUser,ValidateUser))? Should I implement MembershipProvider just for username/password logins? I think that ProfileProvider and RoleProvider wouldn't be that hard to implement? Should I just use FormsAuthentication and use my own "services"?

We are also using NHibernate and Spring for DI.

Any advice will be appreciated.

Thanks!

Answer 1

1.) I think that we should support both (username/password and Ppenid) authentication options for a single user, so that username/password users won't need to create additional account when they decide that they will use an OpenId and I think that we should support several OpenId's for a single user like SO does (if some provider is down).

That seems reasonable. I like how you're designing in for users to have multiple OpenIDs. StackOverflow limits users to just two, but users often have more than that and may want to bind them all. I think username/password is a fine option if your target audience demands it OpenID. StackOverflow is a great example of how simple login can be when its pure OpenID. It can make login less busy to not offer username/password. But again, providing both as options seems most customer-focused since it gives them choice. A future version of DNOA will offer an integrated version of the InfoCard Selector into its OpenID login system so that you can even accept InfoCards directly, but have it look and feel just like an OpenID so your system won't require any changes.

2.) I think that the best database for this would be: <snipped/>

That looks like a reasonable schema. As you've discovered, separating the credentials tables gives you the greatest flexibility.

3.) Should I create my own providers (MembershipProvider, ProfileProvider, RoleProvider)?

MembershipProvider certainly doesn't fit OpenID very well. If you were only supporting OpenID login I'd say throw it out and don't bother implementing your own. The RoleProvider works perfectly with OpenID so that's a keeper. I've heard from others that ProfileProvider needs a MembershipProvider in order to function. I don't know if that's true. But ProfileProvider requires that you use the ASP.NET Membership SQL database schema, which I think is poor if you can write your own db schema which you've done. And if you're writing your own db, storing additional data about your users should be trivial so you shouldn't need the profile provider.

If you go with both username+password and OpenID, then having a MembershipProvider that you implement yourself would likely be possible, but in my experience most MembershipProviders that include any OpenID code are kludgey and even have security holes. So I'd still avoid the MembershipProvider if OpenID has any place in your system.

Answer 2

I wonder...is support for multiple OpenIDs that important. It seems like this is more the role of the OpenID provider. For example, I use ClaimID and I get what essentially amounts to "identify forwarding" (in the sense of email forwarding) so that I can rebind it to different identities. Now I don't rebind providers frequently but a provider could do this (i.e. when you get redirected to their login page they could ask you which identity you'd ultimately like to present). So the question is...is this really the applications job to implement?