Single Sign On: Is it necessary/preferred to keep a table of linked accounts?

user_1742777 0
Viewed 86

I build websites and authentication/account management systems. In our database, we have an user table that (conceptually) looks

TABLE: USER
 id |      name |   email_address (unique)
====|===========|=========================
 88 | Ben Ghazi | bgazi@gmail.com

We wanted to allow Single-Sign-On from Google. So we added a Database table that (conceptually) looks like this:

TABLE: SSO_LINK
 id | our_account_id | sso_provider_name | sso_provider_account_id
====|================|===================|=========================
 99 |             88 |            GOOGLE |                  xyz983

The row in the above table indicates that if Google account #xyz983 ever tries to login, we should log them in as our user #88.

I'm now implementing Google SSO on a new project from scratch. I'm designing how it will work. I am realizing that perhaps this SSO_LINK table was completely unnecessary. Instead of maintaining a list of linked accounts and checking against it, we could just match the email address that Google provides us. If that email matches any of our users, then simply log that user in. That should work. Right?

However, I suspect that most serious implementation of SSO maintain the SSO_LINK table. What is best practice in this case and why?

single-sign-on
2 Answers

Well, it depends.

In a simple world , you must take the token part as a successfull user authentication and check if the email is ok, then, you can proceed further and logged in your user.

In an enhanced and secured way, few tips :

  1. You can check the data around the connection if it smells logical about that user :
  • in the middle of the night, we usually sleep
  • in this is a new device/computer/cellphone inside a country reputed for bad users or an ip of any monkey private or public WLAN used as open proxies or anything like that.
  1. You can use any verifier service provider is a good way yo authenticate your users, but there is another ways : LDAP protocol with OpenLDAP (Free, Open Source Software) or Active Directory (if you are using a Windows Server).
  2. Please, I hope your are hashing or crypting with strong algorithms user passwords and use a smart secured system of your own to not expose privacy concerns. If you are doing that well, you're better than some very big IT companies in USA/Europe. Time is money and user concerns are not their concerns until the RGPD has raised inside the law, so we are working a lot about that since years, and we will continue.
user_3904620

It is recommended to have an accounts and linked_accounts table as you suggest. But how account linking works will vary depending on the provider and the customer use case.

EXAMPLES

At Curity we support many types of authenticator:

  • Some are standards based identity providers, OpenID Connect or SAML
  • Some have vendor specific behavior
  • Some are not IDPs but need to map an identifier, eg a WebAuthn key, to a user account

TECHNIQUES

Sometimes you can deterministically match based on a field such as email, if you are sure that they are the same in both cases. In such cases you can run scripted logic to link accounts, as in this Sign in with Apple tutorial, which matches by email.

Note that there are peculiarities to how this provider works, and not all providers will give you an email. Access to Personally Identifiable Information (PII) is a hot topic these days.

In order for account linking to be reliable, you will sometimes need to involve the user, eg to prove their identity via passwords when they first sign in using a different method, so that you can link the provider specific ID to the user account.

SUMMARY

Account linking, like many other areas of Identity and Access Management, is a toolbox, rather than an out of the box solution. Extensibility features, including custom fields, are often needed. One step at a time though - start by getting the data structure broadly correct and then adding support for one provider at a time.

As a final point, storing the provider's subject claim is more stable than matching on email, since it copes better with future scenarios where the email changes, eg if the user marries.

user_90198850
Related