Configuring Backward Compatibility for Password Handling

When a client connects to a MySQL server, it must supply a password that matches the value stored in the Password column of the appropriate account record in the mysql.user table. This column actually stores password hash values, not the actual plaintext passwords. The original password hash values were 16 bytes long. In MySQL 4.1, the authentication mechanism was changed to use more secure 41-byte password hashes. These are referred to as short-hash and long-hash values in the following discussion. If your installation of MySQL is from version 4.1. or later, the Password column in the user table already will be wide enough to accommodate long-hash values. However, if you upgrade an older pre-4.1 installation, you should update the grant tables to make sure that the Password column can hold long values. For instructions, see "Dealing with Changes to Grant Table Structure."

The existence of two kinds of password hashes can result in a problem if users attempt to connect to the server with an older client program but want to use an account that has a newer long-hash password. The problem manifests itself as the following error message:

% mysql
Client does not support authentication protocol requested by server;
consider upgrading MySQL client

To prevent this difficulty from occurring, use the following guidelines to decide what kind of authentication your MySQL server should allow or require:

• If you must support older clients that do not understand the newer password mechanism, start the server with the --old-passwords option to cause the server to operate in short-hash mode. This is a necessary precaution that prevents users from locking themselves out by changing their passwords and ending up with a long-hash password that no longer can be used with older client programs.

  If you have accounts used by older clients that already have been changed inadvertantly to long-hash passwords, you'll need to reset them to short-hash passwords. To identify such accounts, use the following statement:

  mysql> SELECT User, Host, Password FROM mysql.user
      -> WHERE LENGTH(Password) > 16;
  

  
  

  To change the password for any such account, use the OLD_PASSWORD() function, which generates short-hash passwords. If the account has User and Host values of user_name and host_name in the user table, the password-changing statement looks like this:

  mysql> SET PASSWORD FOR 'user_name'@'host_name' = OLD_PASSWORD('pass_val');
  

  
  

• If all clients that use your server are from MySQL 4.1 or later, you need not enable any kind of backward compatibility. This is the best scenario because it is the most secure. Start the server with the --secure-auth option to prevent clients from connecting except to accounts that have long password hashes. Also, start the server without the --old-passwords option so that password changes do not generate hash values in short format. In addition, it's a good idea to check for any accounts that currently have short password hashes and change their passwords so they have a long-hash password. You can identify such accounts with the following statement:

  mysql> SELECT User, Host, Password FROM mysql.user
      -> WHERE LENGTH(Password) = 16;
  

  
  

  Change the password for each such account using a statement like this:

  mysql> SET PASSWORD FOR 'user_name'@'host_name' = PASSWORD('pass_val');
  

  
  

Note: The meaning of "client" in the preceding discussion most obviously applies to client programs such as mysql and mysqladmin. However, it also applies to interfaces such as Connector/ODBC or Connector/J that connect on behalf of client programs, and it also can apply to a program such as a Web server if it uses modules like PHP that have MySQL enabled.