Understanding how MySQL manages passwords and user-authentication can be a little confusing if you are unfamiliar with modern encryption techniques.

For example, login to MySQL as the 'root' user and perform the following query:

  mysql> SELECT user,host,password FROM mysql.user;

This retrieves the username, hostname and password fields from the master 'mysql.user' table, which MySQL uses to authenticate  all users (including the 'root' user) that attempt login to the MySQL server - you should see the password fields containing values similar to:

  *AAB3E285149C0135D51A520E1940DD3263DC008C

These values are generated by the MySQL 'PASSWORD()' function and you might expect that they are an 'encrypted' version of your password, which can be decrypted back to its original value.

However, this is not the case.

User passwords within MySQL are actually 'encoded' using a 'one-way' algorithm - this means that MySQL generates the value above based on your password, but it is [theoretically] impossible to determine the original password from the generated value.

In other words the algorithm only works in one direction - generating a 'hash' from a password, but not the other way around. You will be not be able to determine the original password used to generate the value above, no matter how hard you try!

Why this approach?

The reason is for security - if an attacker were to obtain a list of 'password' values (such as those produced by the SELECT query at the start of this article) they will be of little use, as the attacker would be unable to determine the original passwords needed to login.

So how does MySQL authenticate a user if it cannot decrypt the 'password' stored against a username?

MySQL 'encodes' the password provided by the user using the same algorithm as before, then it compares the 'encoded' result to the value stored in the 'password' field of the 'mysql.user' table - if the two encoded values match then the correct password must have been provided and the user is allowed to connect. However, the 'encoded' value stored in the password field is never 'decoded'...

The MySQL server uses its internal 'PASSWORD()' function to generate these values, which is based on the 'SHA-1' hashing algorithm. MySQL actually applies this algorithm twice to the original password before storing it, together with some other adjustments.

MySQL recommend that developers use a different approach when storing their own password values in database tables - the reason for this is damage limitation. If a weakness were to be found in SHA-1 it would only allow attackers to determine the MySQL 'user' passwords and not the passwords stored in the developers own tables.

To assist with this MySQL provide another one-way hasing algorithm called 'MD5', as well as a symetrical algorithms, including AES.

Many security experts suggest that a higher level of security is achieved by 'salting' the appropriate algorithm with an arbitary value, as well as by combining different hashing algorithms together - this can be applied to MySQL as follows:

  mysql> UPDATE mytable SET mypassword = SHA1(MD5('pass123','19701231')))
      -> WHERE myuser = 'john';

In this example the 'mypassword' field of the 'mytable' table is set by hashing and salting the password 'pass123' for the user 'john'. In this example we have 'salted' the MD5 algorithm with the user's date-of-birth value, (an arbitary value, but one that will not change for that user). We have then applied the SHA-1 algorithm to the salted result of MD5, to protect against potential weaknesses in the MD5 algorithm.

Using this approach will protect against rainbow-table attacks and makes dictionary attacks on the encoded password fields very difficult - an attacker would first have to crack the SHA-1 algorithm, then crack the MD5 algorithm, as well as knowing the 'salt' value.

Quite an undertaking!