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

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?

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.

If you use email address as username you can match that way. You'll probably still need to build a system whereby an authenticated user can associate his user account with other auth providers (while still logged in), though. If you do it this way the relationship will be implicit.

You have a few options:

• Roll your own solution (no fun).
• Use a solution like HybridAuth. You need a couple extra fields or an extra table to map each provider + ID to a local user ID.
• Change tactics slightly and let an entirely separate system manage your sign in and permissions assignments (i.e. tags) such as: Single Sign-On Server/Client

I'm quite partial to the last approach. Especially if you have multiple, disparate login systems that need to talk to each other or if you even foresee that need.