Pop Quiz: MySQL Password Hashing

Posted in: Technical Track

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?

Interested in working with Sheeri? Schedule a tech call.

1 Comment. Leave new

Brian Papantonio
March 24, 2008 10:40 am

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

Your email address will not be published. Required fields are marked *