Pop Quiz: MySQL Password Hashing

Mar 24, 2008 / By Sheeri Cabral

Tags: ,

The answers to the last pop quiz are up: http://www.pythian.com/blogs/868/pop-quiz-mysql-cluster

So here’s another pop quiz. Given the following:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16450949 to server version: 4.1.14-standard-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select count(*),length(password) from mysql.user group by length(password);
+----------+------------------+
| count(*) | length(password) |
+----------+------------------+
|       49 |               16 |
|       31 |               41 |
+----------+------------------+
2 rows in set (0.00 sec)

mysql> select password('foo');
+-------------------------------------------+
| password('foo')                           |
+-------------------------------------------+
| *F3A2A51A9B0F2BE2468926B4132313728C250DBF |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> show variables like "old%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| old_passwords | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

Since the server has old_passwords set to OFF, you may think that you can delete all the entries in the mysql.user table whose passwords have a lenth of 16. So you do this for security’s sake, and then flush privileges, and none of your applications can connect to the server any more. You scratch your head, wondering how on earth those could even be used, because wouldn’t you get a “Client does not support authentication protocol” error if the old passwords were being used?

So, what is the answer to this question?

One Response to “Pop Quiz: MySQL Password Hashing”

  • Brian Papantonio says:

    old-passwords only applies to the creation of NEW user accounts. Old clients may still connect to the server as long as those 16 character passwords are in there.

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>