Password Hashing in MariaDB

MySQL user accounts are listed in the user table of the MariaDB database. Each MariaDB 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 MariaDB to provide better security and to reduce the risk of passwords being intercepted. However, this new mechanism is understood only by MariaDB (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.6 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 MariaDB client

Another common example of this phenomenon occurs for attempts to use the older PHP MariaDB extension after upgrading to MariaDB or newer. (See , "Common Problems with MariaDB and PHP".)

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 "Client does not support authentication protocol". This information is of particular importance to PHP programmers migrating MariaDB databases from version 4.0 or lower to version 4.1 or higher.Note

This discussion contrasts 4.1 behavior with pre-4.1 behavior, but the 4.1 behavior described here actually begins with 4.1.1. MariaDB 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 MariaDB 5.5 Reference Manual.

Prior to MySQL, 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 MariaDB 4.1.

As of MySQL, 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 MariaDB 5.6, the Password column is made 41 bytes long automatically.
Upgrading from MariaDB (4.1.1 or later in the 4.1 series) to MariaDB 5.6 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 MariaDB to version 5.6, you should upgrade to version 4.1 first, then upgrade the 4.1 installation to 5.6.

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.
- 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
or later client authenticating with short password hash
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_upgrade program 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 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 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.
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 using 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.
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.
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 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.

Password Hashing in MariaDB

Retornar