Using OpenID as the only authentication method

Question

I have read the other questions and they mostly talk about the security of doing so. That's not entirely my concern, mostly because the website is question is a browser-based game. However, the larger issue is the user - not every user is literate enough to understand OpenID. Sure RPX makes this pretty easy, which is what I'll use, but what if the user does not have an account at Google or Facebook or whatever, or does not trust the system to log in with an existing account? They'd have to get an account at another provide - I'm sure most will know how to do it, let alone be bothered to do it.

There is also the problem of how to manage it in the application. A user might want to use multiple identities with a single account, so it's not as simple as username + password to deal with. How do I store the OpenID identities of a user in the database? Using OpenID gives me a benefit too: RPX can provide extensive profile information, so I can just prefill the profile form and ask the user to edit as required.

I currently have this:

Users:
------

ID     Email              Etc.
--     ---------------    ----
0      [email protected]      ...
1      [email protected]    ...

UserOpenIDs:
------------

ID     UserID     OpenID
--     ------     ------
0      0          0
1      0          2
2      1          1

OpenIDs:
--------

ID     Provider   Identifier
--     --------   ----------------
0      Yahoo      https:\\me.yahoo.com\bob#d36bd
1      Yahoo      https:\\me.yahoo.com\alice#c19fd
2      Yahoo      https:\\me.yahoo.com\bigbobby#x75af

With these foreign keys:

UserOpenIDs.UserID -> Users.ID
UserOpenIDs.OpenID -> OpenIDs.ID

Is that the right way to store OpenID identifiers in the database? How would I match the identifier RPX gave me with one in the database to log in the user (if the identifier is known).

So here are concrete questions:

• How would I make it accessible to users not having an OpenID or not wanting to use one? (security concerns over say, logging in with their Google account for example)
• How do I store the identifier in the database? (I'm not sure if the tables above are right)
• What measures do I need to take in order to prevent someone from logging in as another user and happily doing anything with their account? (as I understand RPX sends the identifier via HTTP, so what anyone would have to do is to just somehow grab it then enter it in the "OpenID" field)
• What else do I need to be aware of when using OpenID?

Answer 1

Making it accessible

First concerning users that do not have an OpenID, you can make a little page which explains how to create an account (or even point to some providers). This way, it isn't really harder to create an OpenID account than a regular account.

For people that don't want to use OpenID you have two choices. The first: implement an old-style login beside your OpenID login and let the users choose which method they want. The second is to have only OpenIDs... this simplifies your job. Saying that some users trust more a website than a trusted OpenID provider to log in, is in my opinion quite strange as OpenID providers use often encrypted connections, etc...

Storing in the database

The schema proposed by johnny g is what you need. (I just don't know why are you storing URLs with backslashes instead of slashes)

You may want to normalize your URLs before using them so you can avoid things like http://openid.test.com/abc and http://openid.test.com/abc/ being handled as different URLs.

Additional measures to take

None. You should just use a library from http://openid.net/developers/libraries.

The confirmation of the identity of the user is the provider's problem. Only the user and the website know the password to the account.

If someone has your OpenID URL (which is public), he still needs the password (or another kind of authentification method such as a SSL certificate) to be able to log in.