MySQL Bugs: #10668: CREATE USER does not enforce username length limit

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

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

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.

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...

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.

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)

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

Pushed into mysql-5.0-maint tree.

Available in 5.0.25 and soon in 5.1.12-beta.

Noted in 5.0.25, 5.1.12 changelogs.

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