MySQL Bugs: #79680: Long user names prevent stored procedure/function creation

Bug #79680	Long user names prevent stored procedure/function creation
Submitted:	16 Dec 2015 20:54	Modified:	30 Jan 2017 15:58
Reporter:	Todd Farmer (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S2 (Serious)
Version:	5.7.10	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
MySQL Server 5.7 supports user name and host strings of increased length compared to earlier versions of MySQL.  However, this is not applied consistently across all places where user and host values are stored, which causes problems.  For example:

* The mysql.proc table has a "definer" column, declared as CHAR(77).  This stores the user@host string, which can now exceed 77 characters in length.  Trying to create a stored routine with a definer having a user@host value greater than 77 characters causes an error:

CREATE DEFINER=a12345678901234567890@h12345678901234567890123456789012345678901234567890123456789 PROCEDURE t() SELECT NOW();

mysql> CREATE DEFINER=a12345678901234567890@h12345678901234567890123456789012345
678901234567890123456789 PROCEDURE t() SELECT NOW();
ERROR 1607 (HY000): Cannot create stored routine `t`. Check warnings
mysql> show warnings;
+---------+------+--------------------------------------------------+
| Level   | Code | Message                                          |
+---------+------+--------------------------------------------------+
| Warning | 1265 | Data truncated for column 'definer' at row 1     |
| Error   | 1607 | Cannot create stored routine `t`. Check warnings |
+---------+------+--------------------------------------------------+
2 rows in set (0.00 sec)

* The tables_priv ACL table includes a "grantor" column, also defined as CHAR(77).  I haven't tested the impacts of trying to issue a table-level GRANT command using an account with long user@host values.

How to repeat:
See above.

Suggested fix:
Identify and fix all columns storing user and/or host data that do not support 5.7 increased lengths for these values.

There are also privilege tables in INFORMATION_SCHEMA with GRANTEE column definitions of CHAR(81).  This causes output to be truncated:

mysql> SELECT * FROM user_privileges
    -> WHERE grantee LIKE '_a1%'\G
*************************** 1. row ***************************
       GRANTEE: 'a12345678901234567890'@'h12345678901234567890123456789012345678
90123456789012345
 TABLE_CATALOG: def
PRIVILEGE_TYPE: USAGE
  IS_GRANTABLE: NO
1 row in set (0.00 sec)

mysql> SELECT user, host
    -> FROM mysql.user
    -> WHERE user LIKE 'a1%'\G
*************************** 1. row ***************************
user: a12345678901234567890
host: h12345678901234567890123456789012345678901234567890123456789
1 row in set (0.00 sec)

Posted by developer:
 
Noted in 8.0.0 changelog.

Some grant tables did not account for the increase in maximum user
name length from 16 to 32 characters in MySQL 5.7.8.