14

I want to allow my users to login using OpenID, Twitter and Facebook, if they choose not to register directly to my website.

After they have logged in with oAuth/OpenID (and before you wonder: yes I have looked up the difference *), I will ask them to choose a username.

All I need from the user is their email address and fullname, (along with an IP Address but that's not relevant now).

The current table I use now is:

author (id, fullname, username, password, salt, ip_address, email_hash, verified, created, deleted)

Password is hashed, and email_hash is used to recover lost usernames.

How would I change this structure to support OpenID/oAuth accounts with one username?

* or OAuth? ,OpenID? Neither? Which one should my site support?

Community
  • 1
  • 1
jonnnnnnnnnie
  • 1,219
  • 3
  • 15
  • 24

1 Answers1

7

You need 2 additional fields in your table, one is the login source (twitter, openID, facebook) and the other is the source user id (the id provided from twitter, openID, facebook) I recommend making this a string as some log in sources have letters in their user IDs and not just numbers.

Then when they login, you already know the source they have tried to log in from, you can marry it up with the remote id from your table and if it exists log them in, if not, send them to your register form to get them to fill out their name and email.

Another thing you can do in this situation is have a join table:

remote_source_users (user_id:integer, remote_source_id:string, remote_source:string)

This means you can support multiple remote accounts for the same user.

Gazler
  • 83,029
  • 18
  • 279
  • 245
  • should remote_source_id be varchar(255) or text? How long do you think it should be? – jonnnnnnnnnie Apr 10 '11 at 22:51
  • varchar(255) should cover all cases. – Gazler Apr 10 '11 at 22:55
  • @Gazier: Can you elaborate on your second paragraph a bit? What do you mean by `you can marry it up with the remote id from your table and if it exists log them in` and what is remote.id? However, I would prefer the second situation with the join table. –  Jul 14 '15 at 15:31
  • to support social media login, should we consider to generate a fake password or leave it null for these accounts? usually we get a token from external site and use it to fetch data only, and do we need to compare anything with our system for subsequent login? – jokerday Aug 29 '17 at 07:52
  • what are the indexes on remote_source_users, does it have its own primary key autoincrement? would it be safe to make the remote_source_id as primary key? what about a composite primary key as (source_id, source) since twitter and facebook might accidentally give the same ids? – PirateApp Nov 21 '19 at 07:26