support for OLD_PASSWORD in user account manager

I have a few applications in PHP4 that I can't easily upgrade to PHP5 (classes, mostly) and the client in the old version of PHP uses an authentication method only available to MySQL 4.0 and earlier.
The method to allow it to connect to a MySQL 5 database is to change the password, using OLD_PASSWORD() to set it.

It may be helpful for those of us who still have projects with the old version of PHP or have to use older clients to add an option for it.
An example of implementation could be the current version of PhpMyAdmin, with a checkbox for activating this option.

If the server option OLD_PASSWORD is activated, a call to PASSWORD('123') gives the same result than OLD_PASSWORD('123').

If OLD_PASSWORD is disabled, an older client won't be able to connect, even if the account has its password set with OLD_PASSWORD('xyz').

The conclusion to these two theses is that PASSWORD('123') is always the method of choice, either if the OLD_ option is set or not. The function OLD_PASSWORD('123') should never be of any real use when it comes to update the password field of user accounts.

Or?

Hi, thanks for answering.

I used this workaround provided in the MySQL manual ( http://dev.mysql.com/doc/refman/5.0/en/old-client.html )

If you search for php "Client does not support authentication protocol" (on google) you can see this problem is quite common for php4 users, and the solution provided is the same.

Ah I see, you're talking about this bit:

Reset the password to pre-4.1 style for each user that needs to use a pre-4.1 client program. This can be done using the SET PASSWORD statement and the OLD_PASSWORD() function

Yes, that seems an option to implement in HeidiSQL's user manager.

What about instead setting the global server variable "OLD_PASSWORDS=ON" ?

Hi, thanks for answering.

I used this workaround provided in the MySQL manual ( http://dev.mysql.com/doc/refman/5.0/en/old-client.html )

If you search for php "Client does not support authentication protocol" (on google) you can see this problem is quite common for php4 users, and the solution provided is the same.

Servers are generally fixed installations with a slower upgrade cycle than clients, what is the reason that clients cannot be upgraded?

Hi, thanks for answering.

I used this workaround provided in the MySQL manual ( http://dev.mysql.com/doc/refman/5.0/en/old-client.html )

If you search for php "Client does not support authentication protocol" (on google) you can see this problem is quite common for php4 users, and the solution provided is the same.

Servers are generally fixed installations with a slower upgrade cycle than clients, what is the reason that clients cannot be upgraded?

I see you have older applications, but not being able to authenticate with legacy code, seems like a fairly small issue when it can be remedied with a 5 minute server config change. More likely the application has other issues after the authentication which will require an upgrade of the client anyway.

I didn't know about the old_password global variable, and I'll try it.
But if i set it to ON, does it affect newer clients?

Newer clients will continue to work if you set old_passwords=true in my.{ini/cnf}.

Effectively, setting the above option causes the server to generate password hashes using the old rather than the newer scheme.

The MySQL wire protocol ensures that clients are backwards (but not forward) compatible. Newer clients first try to authenticate using the new hashing method; if that fails the server will inform the client to downgrade authentication. The client then tries again, using the old scheme.

The only problem you will encounter is latency; while connecting, a newer client binding to a server using old passwords will have an extra network roundtrip.

Security wise, even the new authentication scheme is outdated by modern security standards. If you want good security you should wrap the connection in SSL.

When using SSL, if your application is sensitive to response times (for example if it is a web application), you will probably want to keep a pool of open connections too, as the SSL handshake can take a short moment.

In PHP, connection pooling is called "persistent connections" and are accomplished using for example mysql_pconnect instead of mysql_connect.

SSL can be enabled using the right options to mysql_pconnect, but as far as I can tell, you can only validate one way, the server has a certificate and the client can validate that it's valid - not the other way around. Therefore I would, at the moment, suggest that if you require a secure connection, you do something else. For example carry the MySQL connections inside a SSL-VPN or similar tunnel. Just until MySQL server attains a better features set security-wise.

Of course, there's no need to waste CPU time encrypting and decrypting data if your application does not need security or is already protected by other means.

The details of password hashing in MySQL are covered here:
http://dev.mysql.com/doc/refman/5.0/en/password-hashing.html

There's a nice document showing how to tunnel MySQL connections inside SSL here:

http://www.stunnel.org/examples/mysql.html

Pretty simple, just run stunnel with a port to listen on, a server certificate, and a port to proxy the raw connection to.

It covers trusting the server via server certificates only. In order to also authenticate the client, you need to add "-v2" to the stunnel options on the server, which means "accept trusted certificates only". Then you need to add "-p client_cert.pem" to the stunnel command line on the client, to instruct stunnel to use a client certificate there. Et voila.

Creating a CA and a couple of certificates using OpenSSL is covered in the FAQ on stunnel.org, and in numerous other places via Google.