11

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?

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

7
  • 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 5 at 22:54
  • 2
    Nice finding. I didn't know about the 80 chars limitation. – Mircea Vutcovici Feb 5 at 22:58
  • 4
    We should open a documentation bug in MySQL/MariaDB client. – Mircea Vutcovici Feb 5 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 5 at 23:13
  • 4
    MySQL client is truncating the password buffer to 80 characters. Any characters after the first 80 are ignored. Just tested. – Mircea Vutcovici Feb 6 at 0:12
5

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
2
  • 3
    This does not directly address the question, rather show behavior indicating that the question "should" not be a thing? – Håkan Lindqvist Feb 5 at 22:18
  • The password has no length limits, from what I can see in the source code: github.com/MariaDB/server/blob/… I could be wrong though... – Mircea Vutcovici Feb 5 at 22:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.