Incorporating Open ID with my current login System?

Question

I am currently setting up Open ID authentication in my website and I am having troubles incorporating it with my current Login System and database... I've read the article at Plaxo & it recommends this type of table to store the openid info...

create table user_openids (
  openid_url varchar(255) not null,
  primary key (openid_url),

  user_id int not null,
  index (user_id)
);

This is my current Users-info table

Userid(PRIMARY) | username(UNIQUE) | password | Email

Userid is used to reference user-details for comments, ratings etc. (So it goes into the comments table and the ratings table as a User identifier)

I want a system similar to what Stack overflow uses just login using your Open ID and it gives you an unknown(OPENID-provider) display name.... while keeping my current login system intact.
1) How can I add Open ID details of users to my current Users-Info Table without affecting the current login setup?
2) Currently I use User-id(generated unique for every user) to store in the session to maintain Login. What should I do now in case of Open ID?

*My Thoughts(I don't know if I am right or not)
- Add an open-id field to store the unique open id url provided by the open id provider for each user and set it to null for non-open-id-users.
- Make User-id a text field and store a md5 of the open id url.(store this in session to maintain Login).
- I have no idea how can I handle Display-name/Username which is set to unique for each user because I would like to show unknown(OPENID_provider) (for users using open-id) which can be changed from the profile settings...

Any suggestions would be helpful....Thanks

Answer 1

What about this?

• Add a display_name column to the users table, which doesn't have to be unique.
• Make username and password in users optional
• When somebody registers with OpenID, create a row in users with empty username/password and display name set to "unkonwn (provider)".
• Allow users to set username/password, if they want to switch to password-based login.
• Allow users to manage their OpenIDs, so that existing users can switch to OpenID-based login.

This means that users can have username/password, but they don't have to. They also can have one or multiple OpenIDs, but they don't have to. They can use non-unique display name.

Answer 2

Option 1: Only one login type is allowed

Here's my suggestion (UPDATED to prevent the need to edit all business logic related to username):

1. Create a new column called loginid to allow the storage of OpenIDs and old usernames, Add the UNIQUE INDEX on this column as well.
2. Populate loginid with existing data from username
3. DELETE INDEX from username to allow them to be non-unique. When creating a new user from OpenID, set username value to unknown(google) as described.
4. Keep password for legacy logins, ignore it for OpenID.
5. Update only the AUTH portion of your code to look for loginid rather than username.

Option 2: Allow multiple logins (also easily extends to linking multiple profiles from various sources)

1. Create a new table to stand as a master user table and contain all required fields per user (maybe email from your example above).
2. Create a table to store authentication containing: userid (FK to master record), username (stores the username appropriate to the login scheme), password

There are obviously flaws with both of these options, but it will hopefully get you started in the right direction.

Answer 3

The idea with the table layout you show is to have a 1:many from users:openids.

Thus, no changes to the users table are needed.

When someone logs in with an OpenID, you check if that OpenID is in the openids table. If so, you have the user_id of the user and you are done.

Otherwise create a new user (with no username/password set) and insert an (openid,user_id) pair for them into the openids table.

You template can display whatever nice placeholder (such as their OP, or whatever) where it would normally display username for users whose username is blank.

Hopefully you already disallow logging in with blank passwords, so there should be no security issue there.