对Web应用程序使用单点登录（sso）时的数据库检查（用于凭据），提示？

Question

I would like to implement single sign-on (sso) in a management application we have but I would like to have a hint or two about it. In our current application, a client logs in our application with a typical user+pass combination (with CAPTCHA being an optional feature).

As far as I understand (if I did it correctly), what you do with sso is 'trust' an identity provider (namely... Google, Facebook, Yahoo!, Windows Live, etc.), so the client is performing the actual login in an external page and then it returns a value to me, so I know the client has been validated by the identity provider.

In my eyes, I would like to do something like this (zwibbler FTW!):

But in our current application, we check the username and his/her password against our database and that's where my understanding of SSO fails miserably... how can I match the information returned by the identity provider against my user data?

Or better expressed... what fields (as in 'typical' ala user, pass, email, etc.) could I use in my user account data to match the external authentication? My concern is about this being a commercial application, so I want to provide with users with the ability to use their own login data, but I have to match it somehow with our data to let (or not) him/her log in, so... how can I do it?

Answer 1

As a general design you would have the following tables

• Users
• Roles
• auth_OpenID
• auth_Google
• auth_Facebook
• auth_Windows_Live

(Roll any auth tables that share the same structure into a single table).

The users table would identify all the accounts on your system. The roles table would identify all the sets of permissions you need to deal with. Then the auth_* tables would contain the information you need for the remote sign on (such as Google account id and auth token).

You then have a many-to-many relationship between the users table and each of the other tables.

If someone signs in with an unknown third party authentication system you prompt them to either link it to an existing account (in which case they have to sign in again with a recognised set of credentials) or create a new account.