Bug #10668 CREATE USER does not enforce username length limit
Submitted: 16 May 2005 12:42 Modified: 30 Aug 2006 20:04
Reporter: Disha Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.5 Beta OS:Windows (Windows 2000 Server)
Assigned to: Jim Winstead CPU Architecture:Any

[16 May 2005 12:42] Disha
Description:
If a user having name of 64 characters is created, then grant command does not accept the user name.

How to repeat:
1. Start the MySQL client and connect to the database with valid user and password.
2. Set the delimiter to //
3. Create a tables t1 as follows:
   create table t1(f1 int) engine=innodb//
4. Create a user with 64 characters name as follows:
   create user a123456789123456789123456789123456789123456789123456789123 identified by 'test'//
5. Now try to grant select access for the above user on table t1 as follows:
   grant select on t1 to a123456789123456789123456789123456789123456789123456789123//

Expected Results: 
1. If the user with 64 characters in name is allowed to be created then grant command should successfully execute and provide the necessary privileges to the user.

Actual Results: 
1. Error is displayed as:
ERROR 1145 (42000): The host or user argument to GRANT is too long
[16 May 2005 15:29] MySQL Verification Team
According the Manual:

http://dev.mysql.com/doc/mysql/en/grant.html

GRANT supports hostnames up to 60 characters long. Database, table, column, and routine names can be up to 64 characters. Usernames can be up to 16 characters.

Actually the create user accepts the commands without any warning. In this case you can change the subject of the bug:

mysql> create user a123456789123456789123456789123456789123456789123456789123
    -> identified by 'test'; 
Query OK, 0 rows affected (0.00 sec)

but it actually truncates the username:

mysql> select user from user where user like 'a%';
+------------------+
| user             |
+------------------+
| a123456789123456 |
+------------------+
1 row in set (0.02 sec)

and the error displayed by GRANT is correct.
[6 Jun 2005 14:58] Thomas Partsch
Why does mySQL use only usernames with 16 characters? Is it possible to use longer usernames so that the Grant also accept more than 16 characters? Is it planed to make the usernames longer? For my prupose I need usernames with 64 characters but it doesn't work because the Grant doesn't accept them...
[11 Apr 2006 9:20] Valeriy Kravchuk
I think that longer usernames support is a feature request, not a bug really. But that username truncation should be explicitely documented in the manual, http://dev.mysql.com/doc/refman/5.0/en/grant.html.
[12 Apr 2006 11:02] Stefan Hinz
Sorry guys, but this is not a documentation issue, it's a bug with CREATE USER; something goes terribly wrong (tested with 5.0.18 but probably present in many other versions):

mysql> CREATE USER this_user_name_is_too_long;
Query OK, 0 rows affected (0.05 sec)

mysql> SHOW GRANTS FOR this_user_name_is_too_long;
ERROR 1145 (42000): The host or user argument to GRANT is too long
mysql> SHOW GRANTS FOR this_user_name_i;
ERROR 1141 (42000): There is no such grant defined for user 'this_user_name_i' on host '%'

mysql> SELECT Host, User FROM mysql.user;
+-----------+------------------+
| Host      | User             |
+-----------+------------------+
| %         | replikator       |
| %         | stefan           |
| %         | this_user_name_i |
| localhost | stefan           |
+-----------+------------------+
4 rows in set (0.00 sec)
[24 Jul 2006 23:37] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/9522
[3 Aug 2006 17:59] Jim Winstead
Pushed into mysql-5.0-maint tree.
[17 Aug 2006 16:23] Chad MILLER
Available in 5.0.25 and soon in 5.1.12-beta.
[30 Aug 2006 20:04] Paul DuBois
Noted in 5.0.25, 5.1.12 changelogs.

CREATE USER did not respect the 16-character username limit.