best practice for storing oauth AND local authentication methods?

Question

If I were to run a service that allowed users to authenticate via "local" username/password combinations and ALSO any number of OAuth services - what might that user data model look like?

Usually, if I were handling all logins myself, in the "user" database (assuming MySQL), the username and password fields would be required as non-null. But, if my users just wanted to log in with Facebook, I'd just store the Facebook auto token, and not have any username/password locally.

Further, what if they want to log in with Twitter creds, and then tumblr, and then whatever service-of-the-day? I could keep a field for each type, but that might get a little unwieldy. Would I be better off keeping another table of "authentication methods" for lack of a better term, so I could have a one-to-many relationship between users and how authenticate them?

Basically, I'm asking if anyone knows of an industry standard best practice for this scenario, or can point me in the right direction (or if someone has implemented something like this that works well for them). One user, multiple methods of authenticating - what's the best way to hold that info?

If any of the assumptions I've made are invalid, I apologize, please correct me.

Answer 1

I have no idea if my solution comes close to any sort of industry standard but I've done this in several apps before.

Identity within your application should be abstract from the authentication source. What I ended up setting up is something like this:

User table:
id int
username varchar
email varchar
password varchar

Authentication profile table:
user_id int
service enum('website','google','facebook')
token varchar

[ For further normalization, make service its own table with service meta fields. ]

Then your auth script does something like this:

1. Look for username / email
2. Identify known authentication profiles
3. See if the input validates for any known authentication profiles and auth, or return invalid credentials

In cases of some services, you will either need to autogenerate some of the user field values, or prompt the user to enter during the first authentication, depending on what sort of data is available to you from the service.