Storing necessary OpenID information

Question

I'm trying to implement OpenID authentication for my site. Here's the scenario:
I want the user to be able to

• login using just openId(user can just get verified by visiting openid provider. no need to create a custom account with email-password),
• Via email/password (user has registered in site by filling out a form)
• Attach open id(s) to his/her accounts (openids + email for one account).

Now I don't know what credentials I should store for open id. and not sure about the DB schema. Here's the database schema:

Table: Users
UserId => PK
... => Custom info. Not related to authentication.

Table: Authentication  
AuthenticationId => PK
LoginId => (when custom site membership => email address) (when openId => openid unique address)

UserId  => FK to Users.
Provider =>(when custom site membership => "CUSTOM") (when openId => openid provider address)  
Password => filled when using custom membership. empty when using open id.

Now when a user logs in, whether by using openid/custom membership, I just look at authentication table and look for credentials and get the appropriate user. If no users exist, I create a new user and add an entry in authentication table.

• The main question: Is storing Provider and LoginId (see the above comments to see what is being stored in these fields) enough for storing openid authentication? Should I store any additional data so that when the user returns I can authenticate him/her based on my saved data?

• Do you suggest any other (more efficient) approach to implement this?
  Thank you.

Answer 1

Store the ClaimedIdentifier for the openid user--not the Provider address. The Claimed Identifier is what the OpenID protocol verifies is unique for the user and also potentially provides portability across OpenID Providers.

Also, because OpenID 2.0's Claimed Identifiers may be deprecated by OpenID Connect (an unfinished successor to OpenID 2.0), it may also be in your best interest to record the OpenID Provider Endpoint URI and the email address asserted by the Provider in the user record. For now, do not use these as part of your authentication flow, but by recording them, you'll be able to later determine which email addresses you 'trust' (i.e. suppose you decide email addresses asserted by Google are trustworthy) and allow the user to thereby migrate their account to an OpenID Connect one using that verified email address. This will also mitigate against the danger of your web site's Realm (usually http://yourdomainname.com) changing and causing all your Google's user Claimed Identifiers to change, which can only really be recovered from via their email address, tragically.

I also recommend you use different tables for the different auth types. There are a couple of advantages here. The most important one is that architecturally it makes it more difficult to have introduce a security hole into your web site that might allow someone to enter in (for example) an OpenID into the username field and a blank password and have it show up as a database match and login without any real authentication happening. Secondly, it provides a more flexible model in case you want to add a third authentication mechanism rather than making your 'Authentication' table grow horizontally for all users. For example, OAuth 2.0 and "OpenID Connect" will each probably introduce new types of authentication to your site when you add support for them over the years, and adding new tables to handle the new types of data seem to fit better.

Answer 2

We just store the openid claim url. You may want to request additional information from the provider such as the user's name. The most important thing is to separate membership and authentication.

Our schema was

Profiles
--------
UserId
FirstName
LastName
etc.

Users
-----
Username
Password

Profiles.UserId is simply a string property that stores either the users internal username or their openId claim url, depending on how they registered.

Upon successful authentication (either using an internal username/password or external provider) we just set their authentication cookie using either their internal username or their claim url. Getting the user's profile is then just a matter of finding the profiler where (UserId == User.Identity.Name).

This has the advantage that a user can choose to change how they authenticate at any point (perhaps switching to an internal account or using a different provider).