Why does adding one character to my MySQL password lock me out?

Question

I am able to log in via mysql -u myuser -p mydb -h localhost with this:

grant all privileges on mydb.* to myuser@'%' identified by
  '1234567890123456789012345678901234567890123456789012345678901234567890123456789';

But not after I do this:

grant all privileges on mydb.* to myuser@'%' identified by
  '12345678901234567890123456789012345678901234567890123456789012345678901234567890';

Where is this hard limit of 79 characters for a database password coming from?

Answer 1

As has been covered by Mircea Vutcovici, the password is only stored after hashing, which means it will have fixed length when stored.
Ie, it's not obvious that there should be such a limitation.

I believe what was encountered may rather be a limitation imposed specifically by the mysql client application.

The get_tty_password function seems to read the password into char buff[80];, which would imply 79 characters + null termination.

https://github.com/MariaDB/server/blob/b4fb15ccd4f2864483f8644c0236e63c814c8beb/mysys/get_password.c#L155

(Does the limitation even exist if you use a different client?)

Answer 2

The stored passwords are based on the SHA-1 hash string of the supplied password. They are not encrypted, but hashed. This means that all passwords have the same length in the mysql.user table.

MariaDB [(none)]> grant all privileges on mydb.* to myuser@'%' identified by   '12345678901234567890123456789012345678901234567890123456789012345678901234567890';
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> select host, user, password from mysql.user where user='myuser';
+------+--------+-------------------------------------------+
| host | user   | password                                  |
+------+--------+-------------------------------------------+
| %    | myuser | *B3E74714C91FEC20BA4D5225155437727FBFD6CE |
+------+--------+-------------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> select password('12345678901234567890123456789012345678901234567890123456789012345678901234567890') ;
+----------------------------------------------------------------------------------------------+
| password('12345678901234567890123456789012345678901234567890123456789012345678901234567890') |
+----------------------------------------------------------------------------------------------+
| *B3E74714C91FEC20BA4D5225155437727FBFD6CE                                                    |
+----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> SELECT UPPER(SHA1(UNHEX(SHA1("12345678901234567890123456789012345678901234567890123456789012345678901234567890"))));
+--------------------------------------------------------------------------------------------------------------+
| UPPER(SHA1(UNHEX(SHA1("12345678901234567890123456789012345678901234567890123456789012345678901234567890")))) |
+--------------------------------------------------------------------------------------------------------------+
| B3E74714C91FEC20BA4D5225155437727FBFD6CE                                                                     |
+--------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]>

Compare the stored hash with the one computed as above:

select host, user, password from mysql.user;

For 'localhost' you need to add:

grant all privileges on mydb.* to myuser@'localhost' identified by   '12345678901234567890123456789012345678901234567890123456789012345678901234567890';

You need to add this grant too because '%' is not matching with 'localhost' connection.

To connect you need to supply the password in command line to overcome the 80 chars limitation mentioned by @Håkan Lindqvist in the MySQL client.

mysql -u myuser -p12345678901234567890123456789012345678901234567890123456789012345678901234567890 mydb