Whats a good database Schema for Forms auth with OpenId?

Question

I'm trying to find a good standard for a database schema that will let me do a couple things. Mainly, I am writing a web app that needs to handle various types of logins. First being the standard ASP Application Services login, Second being OpenId/oAuth logins and third being Active Directory logins.

What's a good suggestion for a data schema that handles most of these? I plan on using DotNetOpenAuth for oAuth and OpenId. I already know how to get all of these items working individually, but I'm trying to work a way to get them done without a hack to tie them all together.

The application has to manage various permissions based on these users also. Basically, if there is an "Admin" group, then the user whether it's an AD account, OpenId account or Forms Auth account can be added to the group and the application can check permissions either at a page or method level (using MVC).

Open to Suggestions?

EDIT: Since I'm not getting any suggestions, I'll try to clearify. Basically if I get an Identifer (say it's either OpenId user key, oAuth user key or AD domain/user), how can I tie this to a standard ASP Membership Profider profile/user? Should I create a new Membership User with a random password and link the OpenId/oAuth/AD account to the profile via properties?

Basically, I'm looking for something similar to this site. User logs in via something, a profile gets created, that something is stored so we know what it is. Essentially I just need an idea how to get all these authentication methods to work together.

Thanks!

Answer 1

Hi Dave, this isn't so much an answer.. I can't see how i can comment directly to your question.. So have to add as an answer.

I am looking for exactly the same information. I'm no super genius.. most of the info i've found http://andrewblogs.com/blog/openid-for-asp-net-mvc-a-quick-setup/ , http://www.west-wind.com/weblog/posts/899303.aspx deal with simple authentication. rather than architecture dealing with user info persistance/storage for different providers.

The general direction i am leaning towards is thinking of ASPNetMembership as simply another provider. Which i am learning it is hahaha.

You would create a User table, with something like providerID which you would store the various identifiers in.. and maybe a providerTypeID which would have an Enum value identifying the provider.. Along with the rest of the details you'd like to store. hairColor, mothersMaidenName, bigToeLength, fullName etc. providerID in the case of an ASPNetMembership user would be the UserId from the aspnet_Membership table.

I think in your project you would create a Membership/User interface and a class for each provider/User type (especially in the case of oAuth sites like facebook and twitter) where different types can do some similar things like update status as well as things specific to the type.

Maybe this is all sh@#$t you already know. I seem to be getting a bit more confused as i go along. I am a bit of a novice but found this somewhat helpful http://tekpub.com/production/starter . it's a bit abstract and confusing because of some of the packages he uses.. Maybe you will get more out of it.

I will keep watching this thread but if you figure anything out please msg me! Thanks.

One more thing: I'm trying to understand that if you authenticate someone with OpenId, is the identifier the provider sends back permanent? Meaning if we store this in our local DB will it remain accurate forever? So when the user comes back to my site and connects with his openId (assuming the same provider like Google as an example), does the provider return the same identifier? I'm assuming yes, but If not then what I've written above will not work.

Answer 2

Figured I'll try to explain what I did to resolve this issue.

First, I decided to ditch the standard ASP Sql Services tables, for this I created my own User, AuthenticationServices and AuthenticationServicesUsers tables.

The User table is pretty much the "asp_Users and asp_Membership" tables combined. The AuthenticationServices table is a list where I plan on putting information containing the Authentication Services I wish to use (i.e. Facebook, MySpace, MyOpenId, Google, etc...)

Since I wanted users to link as many services as they wanted to thier accounts I setup a third table AuthenticationServicesUsers where I store the UserId and AuthenticationServiceId.

Since this implementation won't work with the standard SqlMembershipProvider, I went ahead and started coding my own custom membership provider. Here I extened the functionality to accept various methods such as a CreateUser method that lets me pass in no password information and instead an Identifier that I get back from the service.

This is where I am at now, and am debugging through atm. I have a few issues relating users to MembershipUsers, but as soon as I can figure that out I think I'll be set.

Thanks for your reply though!