(mysql.info.gz) Password hashing

 
 5.5.9 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:

* When a client attempts to connect to the server, there is an
initial authentication step in which the client must present a
password that has a hash value matching the hash value stored in
the `user' table for the account that the client wants to use.

* After the client connects, it can (if it has sufficient
privileges) set or change the password hashes for accounts listed
in the `user' table. The client can 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 intercepted.
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 -h localhost -u root
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. Additional information can be found in Old client.

* This discussion contrasts 4.1 behavior with pre-4.1 behavior,
but the 4.1 behavior 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 further in Password hashing
4.1.0.

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 increase 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 short, only short-hash authentication is used.

* If the column is long, it can hold either short or long hashes, and
the server can use either format:

- Pre-4.1 clients can connect, although 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 of 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 is the behavior that occurs if you have
upgraded to 4.1 but have not yet 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' generate long hashes unless the server was started with
the `--old-passwords' option. That option forces the server to
generate short password 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. The option
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
undesirable scenario is possible:

* An old client connects to an account that has a short password
hash.

* The client changes its own 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 has a long password hash that
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, if you must support older pre-4.1
clients, it is dangerous to run a 4.1 server without using the
`--old-passwords' option. 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:* Short `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 short `Password' column, the server will generate only 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. A change to an account's password results in that
account having a long password hash.

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 via
`GRANT', `PASSWORD()', or `SET PASSWORD' results in the account being
given a long password hash. From that point on, no pre-4.1 client can
authenticate to that account until the client upgrades to 4.1.

To deal with this problem, you can change a password in a special way.
For example, normally you use `SET PASSWORD' as follows to change an
account password:

mysql> SET PASSWORD FOR 'SOME_USER'@'SOME_HOST' = PASSWORD('mypass');

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

mysql> SET PASSWORD FOR 'SOME_USER'@'SOME_HOST' = OLD_PASSWORD('mypass');

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

*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.

In this scenario, you cannot create accounts that have long password
hashes, because the `--old-passwords' option 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:

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

In scenario 2, accounts with short hashes become inaccessible to pre-4.1
clients if you change their passwords without explicitly using
`OLD_PASSWORD()'.

In 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.

Info Catalog

(mysql.info.gz) Access denied

(mysql.info.gz) Privilege system

automatically generated byinfo2html

(mysql.info.gz) Password hashing

Menu