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?

I don't know the answer to your question, but there is [another limit](https://stackoverflow.com/questions/7465204/maximum-mysql-user-password-length) to look out for as well if you do replication. — Aaron, Feb 05 '21 at 21:47
I do not see any hard limitation in the code: https://github.com/MariaDB/server/blob/b4fb15ccd4f2864483f8644c0236e63c814c8beb/sql/password.c#L39 — Mircea Vutcovici, Feb 05 '21 at 22:00

score 31 · Accepted Answer · answered Feb 05 '21 at 22:41

31

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?)

answered Feb 05 '21 at 22:41

Håkan Lindqvist

33,741
5
65
90

I think they need to add the grant for "localhost", because "%" is not matching with "localhost" which is for connections via local file socket. – Mircea Vutcovici Feb 05 '21 at 22:54
2

Nice finding. I didn't know about the 80 chars limitation. – Mircea Vutcovici Feb 05 '21 at 22:58
4

We should open a documentation bug in MySQL/MariaDB client. – Mircea Vutcovici Feb 05 '21 at 22:59
@MirceaVutcovici Yes, assuming that the same limitation does not also exist somewhere in the server-side authentication code it seems that it should either be clearly documented for the password option in the client or maybe just bumped to some higher value that people will not actually hit. – Håkan Lindqvist Feb 05 '21 at 23:13
I just tested, and supplying the 81 char password as argument works, but pasting it on the mysql prompt is not working. I think it is truncated. Didn't test yet. – Mircea Vutcovici Feb 05 '21 at 23:47
4

MySQL client is truncating the password buffer to 80 characters. Any characters after the first 80 are ignored. Just tested. – Mircea Vutcovici Feb 06 '21 at 00:12
thanks guys! I felt like I was in the twilight zone when i first saw this... I had happened to choose a 80 character long password and thought I had lost just as many IQ points – Isaac Feb 08 '21 at 10:57

score 5 · Answer 2 · edited Feb 06 '21 at 13:24

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

This does not directly address the question, rather show behavior indicating that the question "should" not be a thing? — Håkan Lindqvist, Feb 05 '21 at 22:18
The password has no length limits, from what I can see in the source code: https://github.com/MariaDB/server/blob/b4fb15ccd4f2864483f8644c0236e63c814c8beb/sql/password.c#L39 I could be wrong though... — Mircea Vutcovici, Feb 05 '21 at 22:21

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

2 Answers2