Search the MySQL manual:

4.2.11 Password Hashing in MySQL 4.1

MySQL user accounts are listed in the user table of the mysql database. Each MySQL account is assigned a password, although what is stored in the Password column of the user table is not the plaintext version of the password, but a hash value computed from it. Password hash values are computed by the PASSWORD() function.

MySQL uses passwords in two phases of client/server communication:

• First, when a client attempts to connect to the server, there is an initial authentication step in which the client must present a password that matches the hash value stored in the user table for the account that the client wants to use.
• Second, after the client connects, it may set or change the password hashes for accounts listed in the user table (if it has sufficient privileges). The client may do this by using the PASSWORD() function to generate a password hash, or by using the GRANT or SET PASSWORD statements.

In other words, the server uses hash values during authentication when a client first attempts to connect. The server generates hash values if a connected client invokes the PASSWORD() function or uses a GRANT or SET PASSWORD statement to set or change a password.

The password hashing mechanism was updated in MySQL 4.1 to provide better security and to reduce the risk of passwords being stolen. However, this new mechanism is understood only by the 4.1 server and 4.1 clients, which can result in some compatibility problems. A 4.1 client can connect to a pre-4.1 server, because the client understands both the old and new password hashing mechanisms. However, a pre-4.1 client that attempts to connect to a 4.1 server may run into difficulties. For example, a 4.0 mysql client that attempts to connect to a 4.1 server may fail with the following error message:

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

The following discussion describes the differences between the old and new password mechanisms, and what you should do if you upgrade your server to 4.1 but need to maintain backward compatibility with pre-4.1 clients.

Note: This discussion contrasts 4.1 behaviour with pre-4.1 behaviour, but the 4.1 behaviour described here actually begins with 4.1.1. MySQL 4.1.0 is an ``odd'' release because it has a slightly different mechanism than that implemented in 4.1.1 and up. Differences between 4.1.0 and more recent versions are described later.

Prior to MySQL 4.1, password hashes computed by the PASSWORD() function are 16 bytes long. Such hashes look like this:

mysql> SELECT PASSWORD('mypass');
+--------------------+
| PASSWORD('mypass') |
+--------------------+
| 6f8c114b58f2ce9e   |
+--------------------+

The Password column of the user table (in which these hashes are stored) also is 16 bytes long before MySQL 4.1.

As of MySQL 4.1, the PASSWORD() function has been modified to produce a longer 41-byte hash value:

mysql> SELECT PASSWORD('mypass');
+-----------------------------------------------+
| PASSWORD('mypass')                            |
+-----------------------------------------------+
| *43c8aa34cdc98eddd3de1fe9a9c2c2a9f92bb2098d75 |
+-----------------------------------------------+

Accordingly, the Password column in the user table also must be 41 bytes long to store these values:

• If you perform a new installation of MySQL 4.1, the Password column will be made 41 bytes long automatically.
• If you upgrade an older installation to 4.1, you should run the mysql_fix_privilege_tables script to update the length of the Password column from 16 to 41 bytes. (The script does not change existing password values, which remain 16 bytes long.)

A widened Password column can store password hashes in both the old and new formats. The format of any given password hash value can be determined two ways:

• The obvious difference is the length (16 bytes versus 41 bytes)
• A second difference is that password hashes in the new format always begin with a `*' character, whereas passwords in the old format never do

The longer password hash format has better cryptographic properties, and client authentication based on long hashes is more secure than that based on the older short hashes.

The differences between short and long password hashes are relevant both for how the server uses passwords during authentication and for how it generates password hashes for connected clients that perform password-changing operations.

The way in which the server uses password hashes during authentication is affected by the width of the Password column:

• If the column is narrow, only short-hash authentication is used.
• If the column is wide, it can hold either short or long hashes, and the server can use either format:
  • Pre-4.1 clients can connect, though because they know only about the old hashing mechanism, they can authenticate only for accounts that have short hashes.
  • 4.1 clients can authenticate for accounts that have short or long hashes.

For short-hash accounts, the authentication process is actually a bit more secure for 4.1 clients than for older clients. In terms of security, the gradient from least to most secure is:

• Pre-4.1 client authenticating for account with short password hash
• 4.1 client authenticating for account with short password hash
• 4.1 client authenticating for account with long password hash

The way in which the server generates password hashes for connected clients is affected by the width of the Password column and by the --old-passwords option. A 4.1 server generates long hashes only if certain conditions are met: The Password column must be wide enough to hold long values and the --old-passwords option must not be given. These conditions apply as follows:

• The Password column must be wide enough to hold long hashes (41 bytes). If the column has not been updated and still has the pre-4.1 width (16 bytes), the server notices that long hashes cannot fit into it and generates only short hashes when a client performs password-changing operations using PASSWORD(), GRANT, or SET PASSWORD. (This behaviour occurs if you have upgraded to 4.1 but have not run the mysql_fix_privilege_tables script to widen the Password column.)
• If the Password column is wide, it can store either short or long password hashes. In this case, PASSWORD(), GRANT, and SET PASSWORD will generate long hashes unless the server was started with the --old-passwords option. This option forces the server to generate short passsword hashes instead.

The purpose of the --old-passwords option is to allow you to maintain backward compatibility with pre-4.1 clients under circumstances where the server would otherwise generate long password hashes. It doesn't affect authentication (4.1 clients can still use accounts that have long password hashes), but it does prevent creation of a long password hash in the user table as the result of a password-changing operation. Were that to occur, the account no longer could be used by pre-4.1 clients. Without the --old-passwords option, the following scenario is possible:

• An old client connects to an account that has a short password hash.
• The client changes the account's password. Without --old-passwords, this results in the account having a long password hash.
• The next time the old client attempts to connect to the account, it cannot, because the account now requires the new hashing mechanism during authentication. (Once an account has a long password hash in the user table, only 4.1 clients can authenticate for it, because pre-4.1 clients do not understand long hashes.)

This scenario illustrates that it is dangerous to run a 4.1 server without using the --old-passwords option if you must support older pre-4.1 clients. By running the server with --old-passwords, password-changing operations will not generate long password hashes and thus do not cause accounts to become inaccessible to older clients. (Those clients cannot inadvertently lock themselves out by changing their password and ending up with a long password hash.)

The downside of the --old-passwords option is that any passwords you create or change will use short hashes, even for 4.1 clients. Thus, you lose the additional security provided by long password hashes. If you want to create an account that has a long hash (for example, for use by 4.1 clients), you must do so while running the server without --old-passwords.

The following scenarios are possible for running a 4.1 server:

Scenario 1) Narrow Password column in user table

• Only short hashes can be stored in the Password column.
• The server uses only short hashes during client authentication.
• For connected clients, password hash-generating operations involving PASSWORD(), GRANT, or SET PASSWORD use short hashes exclusively. Any change to an account's password results in that account having a short password hash.
• The --old-passwords option can be used but is superfluous because with a narrow Password column, the server will be generating short password hashes anyway.

Scenario 2) Long Password column; server not started with --old-passwords option

• Short or long hashes can be stored in the Password column.
• 4.1 clients can authenticate for accounts that have short or long hashes.
• Pre-4.1 clients can authenticate only for accounts that have short hashes.
• For connected clients, password hash-generating operations involving PASSWORD(), GRANT, or SET PASSWORD use long hashes exclusively. Any change to an account's password results in that account having a long password hash.
• OLD_PASSWORD() may be used to explicitly generate a short hash. For example, to assign an account a short password, use UPDATE as follows:

  mysql> UPDATE user SET Password = OLD_PASSWORD('mypass')
      -> WHERE Host = 'some_host' AND User = 'some_user';
  mysql> FLUSH PRIVILEGES;
  

As indicated earlier, a danger in this scenario is that it is possible for accounts that have a short password hash to become inaccessible to pre-4.1 clients. Any change to such an account's password made via GRANT, SET PASSWORD, or PASSWORD() results in the account being given a long password hash, and from that point on, no pre-4.1 client can authenticate to that account until the client upgrades to 4.1.

Scenario 3) Long Password column; server started with --old-passwords option

• Short or long hashes can be stored in the Password column.
• 4.1 clients can authenticate for accounts that have short or long hashes (but note that it is possible to create long hashes only when the server is started without --old-passwords).
• Pre-4.1 clients can authenticate only for accounts that have short hashes.
• For connected clients, password hash-generating operations involving PASSWORD(), GRANT, or SET PASSWORD use short hashes exclusively. Any change to an account's password results in that account having a short password hash.

In this scenario, you cannot create accounts that have long password hashes, because --old-passwords prevents generation of long hashes. Also, if you create an account with a long hash before using the --old-passwords option, changing the account's password while --old-passwords is in effect results in the account being given a short password, causing it to lose the security benefits of a longer hash.

The disadvantages for these scenarios may be summarized as follows:

Scenario 1) You cannot take advantage of longer hashes that provide more secure authentication.

Scenario 2) Accounts with short hashes become inaccessible to pre-4.1 clients if you change their passwords without explicitly using OLD_PASSWORD().

Scenario 3) --old-passwords prevents accounts with short hashes from becoming inaccessible, but password-changing operations cause accounts with long hashes to revert to short hashes, and you cannot change them back to long hashes while --old-passwords is in effect.

4.2.12 Implications of Password Hashing Changes for Application Programs

An upgrade to MySQL 4.1 can cause a compatibility issue for applications that use PASSWORD() to generate passwords for their own purposes. (Applications really should not do this, because PASSWORD() should be used only to manage passwords for MySQL accounts. But some applications use PASSWORD() for their own purposes anyway.) If you upgrade to 4.1 and run the server under conditions where it generates long password hashes, an application that uses PASSWORD() for its own passwords will break. The recommended course of action is to modify the application to use another function such as SHA1() or MD5() to produce hashed values. If that is not possible, you can use the OLD_PASSWORD() function, which is provided to generate short hashes in the old format. (But note that OLD_PASSWORD() may one day no longer be supported.)

If the server is running under circumstances where it generates short hashes, OLD_PASSWORD() is available but is equivalent to PASSWORD().

Password hashing in MySQL 4.1.0 differs from hashing in 4.1.1 and up. The 4.1.0 differences are:

• Password hashes are 45 bytes long rather than 41 bytes.
• The PASSWORD() function is non-repeatable. That is, with a given argument X, successive calls to PASSWORD(X) generate different results.

User Comments

Add your own comment.