26

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.

Hoopes
  • 3,943
  • 4
  • 44
  • 60
  • 1
    Personally, I wouldn't want to deal with more than one or two authentication schemes - trying to accept any and everything is just going to be a nightmare. If your app relates more closely to FB, then allow FB, etc. That being said... talking just OAuth + local, I'd think you would implement a method on your user class, something like oauthenticated? (I'm a ruby guy), that would tell you whether they were initially local-authed or o-authed. Then in your authentication code you'll need to check and branch based on which method they used to get them logged in. After that, it shouldn't matter. – Yardboy Jun 23 '11 at 18:19

2 Answers2

10

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.

Andy Baird
  • 6,088
  • 4
  • 43
  • 63
  • This is similar to what I was hoping it would look like. Also might have a default_profile in the user table so people can just hit /login and it knows what to do, or require a parameter that specifies which type of login to perform. So then auth_type = facebook doesn't require anything else, and I just use the stored token, but auth_type = website requires a login and password. Sound feasible? (what the...no newlines in comments?) – Hoopes Jun 23 '11 at 19:00
  • Yes, or in facebook's case you can just poll the FB.Auth event and auto-login as the user reaches the page. – Andy Baird Jun 23 '11 at 19:32
  • 1
    When a user relogs in, how does the app know whether this person has logged in before? Basically I do not understand how the id integer can be determined after I use facebook to login once, then use Google to log in the second time. Does my app consider these two different users? Or one user? To reiterate, how does the app know that these two logins are the same person (barring any sort of cookies). – CMCDragonkai Jul 09 '13 at 19:22
  • Just a suggestion, in OpenID Connect the entities that allow users to login are called Providers (OPs - OpenID Connect Providers), and the entities that want to request the user to login on the Providers, i.e. StackOverflow, Spotify, Netflix, are called Services. In the name of good nomenclature, I would suggest an update to your example, namely on the section "Authentication profile table:" to change **service** by **provider**. – Telmo Dias Sep 25 '18 at 15:39
2

I think what you want is a local authentication system (possibly for legacy reasons?) as well as support for users logging in using delegated authentication. The standard for delegated auth is OpenID. You might want to look at OpenID consumer libraries and samples, which should give you an idea of storing OpenID credentials. Unfortunately Facebook and Twitter do not support OpenID, but the flow is pretty much same, i.e. your data model will not change. We have implemented an OpenID consumer to support OpenID based login and registration. In order to support the local authentication, we have used an OpenID provider. In other words, we are both a consumer and a provider. That way, even the local auth system is standards based. Now to answer your question about schema - We have the source (local, twitter, facebook, google, yahoo, AOL) and the email as a composite key, along with the token and/or password in the authentication table. We let users change their display names, and have a unique vanity URL which is also a part of this schema. Users have an option to set up a password when coming in through OpenID, as for Mobile they'd need a password (not a whole lot of OpenID support on mobile). OAuth solves a little different use case where you're dealing with authorization more than authentication. Does this help? If you have any questions feel free to comment and I'd be glad to provide more details - I just do not want to confuse you with too much information at this point.

lobster1234
  • 7,679
  • 26
  • 30
  • Ya, the whole oauth vs. openid thing has been very confusing. If you go to facebook's authentication page, it doesn't mention openid, just oauth. So...must support both? (Assuming I'm required to support facebook) – Hoopes Jun 23 '11 at 19:07
  • OAuth can be used as a subset of OpenID, in that you're mixing the Identity Provider and the Resource Provider. I can tell you what we're doing - we are handling facebook and Twitter separately than other OpenID providers but not using OAuth, i.e. we're not accessing any of the resources for the user. In this case OAuth is used as more or less an identity assertion mechanism rather than resource access. – lobster1234 Jun 23 '11 at 21:20