用openid替换mysql用户身份验证

Question

So, I'm working with a really old system which uses a person's mysql database credentials to authenticate to a web site (the database was originally only accessed from the command line, but is now accessed from a php frontend). Because of some internal reasons (and to preserve the user's history), I have to leave the old authentication intact. I've been charged with adding openid authentication to this system. Somehow I need to be able to retrieve a users mysql username and password upon logging into the site through openid (using the Zend framework, by the way). I've thought of simply requiring registration at the first login, where the user must provide their mysql credentials, but I'd rather not store the password plain text. I've also considered blanking everyone's mysql passwords, and just setting the user's mysql username manually (rather than having the user provide this, since they could provide any username). This is turning into a security nightmare. Does anyone have any suggestions for alternatives?

This is running on a Linux server, by the way. Also, I can't use mysql pluggable authentication because the mysql version is 5.0 (pluggable authentication requires mysql 5.5), and no, I can't update it.

Answer 1

MySQL passwords are hashed, so you will not be able to extract the plaintext password from MySQL. I guess that leaves you with two options, both of which you considered in your question:

• The first time the user logs in with OpenID, replace the user's password in MySQL with a new password that is known to your application. Your application uses that password to log into the account of any user who is using OpenID.

  Con: A user who uses OpenID cannot go back to using direct MySQL authentication because they don't know their own password anymore. Not even using MySQL command line tools. That implies that users, having once used OpenID, must use OpenID forevermore.

• When the user registers to use OpenID, your application momorizes their password.

  Con: Your application keeps a list of plaintext passwords

  Con: Your application breaks when the user changes their MySQL password by themselves.

Third option:

• For each user who uses OpenID, create a second MySQL user for them, which has a generic password. Copy all of the permissions assigned to the normal account to this "shadow" account.

  Con: the normal user and shadow user must be kept in sync, so that if some MySQL permissions are added or revoked from one, they should also be added or revoked from the other one at the same time. Forgetting do to this will result in a mess.