Movatterモバイル変換

MySQL lists user accounts in theuser table of themysql database. Each MySQL account can be assigned a password, although theuser table does not store the cleartext version of the password, but a hash value computed from it.

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

In other words, the serverchecks hash values during authentication when a client first attempts to connect. The servergenerates hash values if a connected client invokes thePASSWORD() function or uses a password-generating statement to set or change a password.

Password hashing methods in MySQL have the history described following. These changes are illustrated by changes in the result from thePASSWORD() function that computes password hash values and in the structure of theuser table where passwords are stored.

The Original (Pre-4.1) Hashing Method

The original hashing method produced a 16-byte string. Such hashes look like this:

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

To store account passwords, thePassword column of theuser table was at this point 16 bytes long.

The 4.1 Hashing Method

MySQL 4.1 introduced password hashing that provided better security and reduced the risk of passwords being intercepted. There were several aspects to this change:

The changes in MySQL 4.1 took place in two stages:

Compatibility Issues Related to Hashing Methods

The widening of thePassword column in MySQL 4.1 from 16 bytes to 41 bytes affects installation or upgrade operations as follows:

The 4.1 hashing method is understood only by MySQL 4.1 (and higher) servers and clients, which can result in some compatibility problems. A 4.1 or higher client can connect to a pre-4.1 server, because the client understands both the pre-4.1 and 4.1 password hashing methods. However, a pre-4.1 client that attempts to connect to a 4.1 or higher server may run into difficulties. For example, a 4.0mysql client may fail with the following error message:

$> mysql -h localhost -u rootClient does not support authentication protocol requestedby server; consider upgrading MySQL client

The following discussion describes the differences between the pre-4.1 and 4.1 hashing methods, and what you should do if you upgrade your server but need to maintain backward compatibility with pre-4.1 clients. (However, permitting connections by old clients is not recommended and should be avoided if possible.) This information is of particular importance to PHP programmers migrating MySQL databases from versions older than 4.1 to 4.1 or higher.

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 thePassword column:

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

The way in which the server generates password hashes for connected clients is affected by the width of thePassword column and by theold_passwords system variable. A 4.1 or later server generates long hashes only if certain conditions are met: ThePassword column must be wide enough to hold long values andold_passwords must not be set to 1.

Those conditions apply as follows:

The purpose of theold_passwords system variable is to permit backward compatibility with pre-4.1 clients under circumstances where the server would otherwise generate long password hashes. The option does not affect authentication (4.1 and later clients can still use accounts that have long password hashes), but it does prevent creation of a long password hash in theuser table as the result of a password-changing operation. Were that permitted to occur, the account could no longer be used by pre-4.1 clients. Withold_passwords disabled, the following undesirable scenario is possible:

This scenario illustrates that, if you must support older pre-4.1 clients, it is problematic to run a 4.1 or higher server withoutold_passwords set to 1. By running the server withold_passwords=1, password-changing operations do 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 ofold_passwords=1 is that any passwords created or changed use short hashes, even for 4.1 or later clients. Thus, you lose the additional security provided by long password hashes. To create an account that has a long hash (for example, for use by 4.1 clients) or to change an existing account to use a long password hash, an administrator can set the session value ofold_passwords set to 0 while leaving the global value set to 1:

mysql> SET @@SESSION.old_passwords = 0;Query OK, 0 rows affected (0.00 sec)mysql> SELECT @@SESSION.old_passwords, @@GLOBAL.old_passwords;+-------------------------+------------------------+| @@SESSION.old_passwords | @@GLOBAL.old_passwords |+-------------------------+------------------------+|                       0 |                      1 |+-------------------------+------------------------+1 row in set (0.00 sec)mysql> CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'newpass';Query OK, 0 rows affected (0.03 sec)mysql> SET PASSWORD FOR 'existinguser'@'localhost' = PASSWORD('existingpass');Query OK, 0 rows affected (0.00 sec)

The following scenarios are possible in MySQL 4.1 or later. The factors are whether thePassword column is short or long, and, if long, whether the server is started withold_passwords enabled or disabled.

Scenario 1: ShortPassword column in user table:

This scenario occurs when a pre-4.1 MySQL installation has been upgraded to 4.1 or later butmysql_upgrade has not been run to upgrade the system tables in themysql database. (This is not a recommended configuration because it does not permit use of more secure 4.1 password hashing.)

Scenario 2: LongPassword column; server started withold_passwords=1:

In this scenario, newly created accounts have short password hashes becauseold_passwords=1 prevents generation of long hashes. Also, if you create an account with a long hash before settingold_passwords to 1, changing the account's password whileold_passwords=1 results in the account being given a short password, causing it to lose the security benefits of a longer hash.

To create a new account that has a long password hash, or to change the password of any existing account to use a long hash, first set the session value ofold_passwords set to 0 while leaving the global value set to 1, as described previously.

In this scenario, the server has an up to datePassword column, but is running with the default password hashing method set to generate pre-4.1 hash values. This is not a recommended configuration but may be useful during a transitional period in which pre-4.1 clients and passwords are upgraded to 4.1 or later. When that has been done, it is preferable to run the server withold_passwords=0 andsecure_auth=1.

Scenario 3: LongPassword column; server started withold_passwords=0:

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. A change to such an account's password made using thePASSWORD() function or a password-generating statement results in the account being given a long password hash. From that point on, no pre-4.1 client can connect to the server using that account. The client must upgrade to 4.1 or later.

If this is a problem, you can change a password in a special way. For example, normally you useSET PASSWORD as follows to change an account password:

SET PASSWORD FOR 'some_user'@'some_host' = PASSWORD('password');

To change the password but create a short hash, use theOLD_PASSWORD() function instead:

SET PASSWORD FOR 'some_user'@'some_host' = OLD_PASSWORD('password');

OLD_PASSWORD() is useful for situations in which you explicitly want to generate a short hash.

The disadvantages for each of the preceding scenarios may be summarized as follows:

In scenario 1, you cannot take advantage of longer hashes that provide more secure authentication.

In scenario 2,old_passwords=1 prevents accounts with short hashes from becoming inaccessible, but password-changing operations cause accounts with long hashes to revert to short hashes unless you take care to change the session value ofold_passwords to 0 first.

In scenario 3, accounts with short hashes become inaccessible to pre-4.1 clients if you change their passwords without explicitly usingOLD_PASSWORD().

The best way to avoid compatibility problems related to short password hashes is to not use them: