Bug #62992 Regression with user names.
Submitted: 28 Oct 2011 12:08 Modified: 28 Oct 2011 14:21
Reporter: Peter Laursen (Basic Quality Contributor) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S2 (Serious)
Version:5.0.95, 5.1.60, 5.5.17 OS:Windows (7 - probably any OS affected)
Assigned to: CPU Architecture:Any
Tags: qc, regression

[28 Oct 2011 12:08] Peter Laursen
Description:
"To make things even more precise, I will now send a server patch to our development management. This patch adds a code that will truncate user column at 16 chars and other columns to their nominal sizes. This will ensure that future 4.1 and 5.0 versions will not work with longer names, whatever changes some application could envisage."

This is from an official statement we had from a MySQL executive years ago.  Refer for details: http://www.webyog.com/faq/content/23/76/en/i-have-a-very-long-username-for-the-mysql-datab...

Unfortunately this is broken and we observe that ssome hoster have resumed practices with using user names longer than 16 characters for their customers.

How to repeat:
step 1) hack the user table:

ALTER TABLE `mysql`.`user` CHANGE `User` `User` CHAR(20); 

INSERT INTO `mysql`.`user`(`Host`,`User`,`Password`,`Select_priv`,`Insert_priv`,`Update_priv`,`Delete_priv`,`Create_priv`,`Drop_priv`,`Reload_priv`,`Shutdown_priv`,`Process_priv`,`File_priv`,`Grant_priv`,`References_priv`,`Index_priv`,`Alter_priv`,`Show_db_priv`,`Super_priv`,`Create_tmp_table_priv`,`Lock_tables_priv`,`Execute_priv`,`Repl_slave_priv`,`Repl_client_priv`,`Create_view_priv`,`Show_view_priv`,`Create_routine_priv`,`Alter_routine_priv`,`Create_user_priv`,`ssl_type`,`ssl_cipher`,`x509_issuer`,`x509_subject`,`max_questions`,`max_updates`,`max_connections`,`max_user_connections`) VALUES ( 'localhost','peterpeterpeterpeter','','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','','0','0','0','0'); 

FLUSH PRIVILEGES;

SELECT USER, HOST FROM mysql.user WHERE USER LIKE 'peter%';
/*
USER                  HOST     
--------------------  ---------
peterpeterpeterpeter  localhost
*/

step2) connect from client:

MySQL 5.0.90:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -upeterpeterpeterpeter P3309
Enter password: 
ERROR 1045 (28000): Access denied for user 'peterpeterpeterp'@'localhost' (using password: NO)

MySQL 5.5.17:
C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -upeterpeterpeterpeter
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.16 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

So connection with long username is possible with MySQL 5.5 and not 5.0 (did not try 5.1).

I am setting category to 'server'. But I am not sure about code internals here.

Suggested fix:
Restore 5.0 behaviour.
[28 Oct 2011 12:11] Peter Laursen
I forgot to mention that "CREATE USER peterpeterpeterpeter@localhost;" fails on all versions.
[28 Oct 2011 12:19] Peter Laursen
updated 'OS' info.
[28 Oct 2011 12:56] Valeriy Kravchuk
Looks like with recent 5.0 versions it is possible to log in without any problems in this case:

macbook-pro:5.0 openxs$ bin/mysql -upeterpeterpeterpeter test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.5.17-debug Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> exit
Bye
macbook-pro:5.0 openxs$ bin/mysql --version                  
bin/mysql  Ver 14.12 Distrib 5.0.95, for apple-darwin9.6.0 (i386) using readline 5.1
[28 Oct 2011 13:17] Peter Laursen
I don't have any 5.0 higher than 5.0.90 (and don't know where to find any higher than 5.0.92).  And it does not matter either if 5.0.95 has the same regression bug. 

As long as CREATE USER username > 16 chars returns an error and as long as docs state what they do this patch that our FAQ refers (and what was applied to late 4.1 tree) to must be in place. Also where in changelogs between 5.0.90 and 5.0.95 do I find this listed? What about early and late 5.1x?

I also don't understand why you ask me to try a newer 5.0x version as you already did. That would not reveal anything new.
[28 Oct 2011 13:29] Valeriy Kravchuk
I had not asked you to check anything, just informed that recent 5.0 client is also affected. 

This is a client bug IMHO, as manual, indeed, clearly says (http://dev.mysql.com/doc/refman/5.5/en/user-names.html):

"* MySQL user names can be up to 16 characters long. Operating system user names, because they are completely unrelated to MySQL user names, may be of a different maximum length. For example, Unix user names typically are limited to eight characters.
Warning

The limit on MySQL user name length is hard-coded in the MySQL servers and clients, and trying to circumvent it by modifying the definitions of the tables in the mysql database does not work.

You should never alter any of the tables in the mysql database in any manner whatsoever except by means of the procedure that is described in Section 4.4.7, “mysql_upgrade — Check Tables for MySQL Upgrade”. Attempting to redefine MySQL's system tables in any other fashion results in undefined (and unsupported!) behavior."

So, it says "does not work", while it works in recent versions.
[28 Oct 2011 13:47] Peter Laursen
I don't think it is an issue with the client. At least I did not understand it like that.  Read this " I will now send a *server* patch to our development management. This patch adds a code that will truncate user column at 16 chars and other columns to their nominal sizes".

I understand that what happens with late 4.1 and 5.0 up to at least 5.0.90 is that the truncation to 16 chars happens *server side* when a client sends a username > 16 chars.  Next authentication is attempted with the truncated username, what fails as it does not match the user table. And the failed auth details are next returned to the client in the error message. That is what I understand and think. But you know very well that I am not familiar with server code.
[28 Oct 2011 14:21] Peter Laursen
Let me add that the quoted statements in the SQLyog FAQ referred dates back to when we had a paid 'Gold Partnership' agreement with MySQL AB what included up to a certain number of support tickets to MySQL supporters and developers per year.

We found the practice hacking the user table very common at the time. We had restricted the username to 16 chars in SQLyog connection settings as MySQL docs stated that was the max_length.  So SQLyog could not authenticate with more than 16 chars to server that had been 'hacked' like this. Some other clients (that were our competitors of the time) could.  We used our support option with MySQL AB to ask for advice as regards this.  We did not change anything after receiving the reply (of which most important parts are quoted in this FAQ) stating that this hacking practice would be stopped by server code change in forthcoming versions . And I *DO* read the release notes of every released server version and I do not think this change has been documented (probably because the significance of the code change was not realized by later MySQL developers and this effect of some new code change after 5.0.90 was unintentional).