(mysql.info) password-hashing

 
 5.8.9 Password Hashing as of 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 MySQL 4.1 (and newer)
servers and clients, which can result in some compatibility problems. A
4.1 or newer 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 or newer server may
run into difficulties. For example, a 3.23 `mysql' client that attempts
to connect to a 5.0 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

Another common example of this phenomenon occurs for attempts to use
the older PHP `mysql' extension after upgrading to MySQL 4.1 or newer.
(See php-problems.)

The following discussion describes the differences between the old and
new password mechanisms, and what you should do if you upgrade your
server but need to maintain backward compatibility with pre-4.1
clients. Additional information can be found in old-client.
This information is of particular importance to PHP programmers
migrating MySQL databases from version 4.0 or lower to version 4.1 or
higher.

* 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 MySQL 3.23, 4.0, 4.1
Reference Manual.

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') |
+-------------------------------------------+
| *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 |
+-------------------------------------------+

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 5.0, the `Password'
column is made 41 bytes long automatically.

* Upgrading from MySQL 4.1 (4.1.1 or later in the 4.1 series) to
MySQL 5.0 should not give rise to any issues in this regard
because both versions use the same password hashing mechanism. If
you wish to upgrade an older release of MySQL to version 5.0, you
should upgrade to version 4.1 first, then upgrade the 4.1
installation to 5.0.

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
using accounts that have short hashes.

* 4.1 and later clients can authenticate using accounts that
have short or long hashes.

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:

* Pre-4.1 client authenticating with short password hash

* 4.1 or later client authenticating with short password hash

* 4.1 or later client authenticating 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 or later 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 enable 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 and later 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 and
later 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 or newer server without using the
-old-passwords option. By running the server with -old-passwords,
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 of the -old-passwords option is that any passwords you
create or change 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 or later 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 generates 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 and later clients can authenticate using accounts that have
short or long hashes.

* Pre-4.1 clients can authenticate only using 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:

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

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

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; 4.1 or newer server started with
-old-passwords option:

* Short or long hashes can be stored in the `Password' column.

* 4.1 and later 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) access-denied

(mysql.info) privilege-system

automatically generated byinfo2html

(mysql.info) password-hashing

Menu