Bug #82956 DEFAULT_ROLE_HOST and DEFAULT_ROLE_USER are probably too small
Submitted: 12 Sep 2016 19:10 Modified: 29 Aug 2017 9:01
Reporter: Daniël van Eeden (OCA) Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: Security: Roles Severity:S3 (Non-critical)
Version:8.0.0 OS:Any
Assigned to: Georgi Kodinov CPU Architecture:Any

[12 Sep 2016 19:10] Daniël van Eeden
Description:
From mysql.role_edges:
DEFAULT_ROLE_HOST CHAR(60) BINARY DEFAULT '%' NOT NULL,
DEFAULT_ROLE_USER CHAR(32) BINARY DEFAULT '' NOT NULL,

What's the maximum size of the text representation of an IPv6 address? Isn't it something like 45? And production_bugzilla_development_rw is 34 characters. So probably these are too small

How to repeat:
See description
[13 Sep 2016 13:48] Umesh Shastry
Hello Daniël, 

Thank you for the report and feedback. 
I assume you meant mysql.default_roles

mysql> desc mysql.default_roles;
+-------------------+----------+------+-----+---------+-------+
| Field             | Type     | Null | Key | Default | Extra |
+-------------------+----------+------+-----+---------+-------+
| HOST              | char(60) | NO   | PRI |         |       |
| USER              | char(32) | NO   | PRI |         |       |
| DEFAULT_ROLE_HOST | char(60) | NO   | PRI | %       |       |
| DEFAULT_ROLE_USER | char(32) | NO   | PRI |         |       |
+-------------------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)

Thanks, 
Umesh
[14 Sep 2016 10:09] Georgi Kodinov
I fail to understand why is this roles related.

The user name is set to 32 chars max in http://dev.mysql.com/doc/refman/8.0/en/user-names.html. So we're just using that length consistently for all user names in all ACL tables. See e.g. mysql.user etc.

The host is also consistently defined as 60 characters. And I believe an IPv6 address fits freely into it. 

If we are to change these constraints we'd change them across the board, for all tables that define user names or host names. 

Is this what you are advocating for ?
[14 Sep 2016 11:57] Daniël van Eeden
You're right, this just follows the sizes in mysql.user etc, which is good.
And IPv6 fits ok, I was mistaken there...

But in DEFAULT_ROLE_USER there can be more than one role...

mysql> CREATE ROLE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE ROLE bbbbbbbbbbbbbbbbbbbbbbbbbbbbbb;
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa TO user1;
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT bbbbbbbbbbbbbbbbbbbbbbbbbbbbbb TO user1;
Query OK, 0 rows affected (0.02 sec)

mysql> alter user user1 default role 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa, bbbbbbbbbbbbbbbbbbbbbbbbbbbbbb';
ERROR 1470 (HY000): String 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa, bbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' is too long for user name (should be no longer than 32)

Also note that the error message is wrong (s/user name/role names/)
[14 Sep 2016 13:03] Georgi Kodinov
It's never going to get more than one role IMHO:

mysql> create user u1@localhost;
Query OK, 0 rows affected (0.06 sec)

mysql> create role r1;
Query OK, 0 rows affected (0.03 sec)

mysql> create role r2;
Query OK, 0 rows affected (0.03 sec)

mysql> grant r1, r2 to u1@localhost;
Query OK, 0 rows affected (0.03 sec)

mysql> alter user u1@localhost default role r1,r2;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from default_roles;
+-----------+------+-------------------+-------------------+
| HOST      | USER | DEFAULT_ROLE_HOST | DEFAULT_ROLE_USER |
+-----------+------+-------------------+-------------------+
| localhost | u1   | %                 | r1                |
| localhost | u1   | %                 | r2                |
+-----------+------+-------------------+-------------------+
2 rows in set (0.00 sec)

Please review the syntax here: http://dev.mysql.com/doc/refman/8.0/en/alter-user.html

What you were trying is to set a single default role of 'aaaaaa...., bbbb...', not two default roles.
[14 Sep 2016 13:27] Daniël van Eeden
Ok. so the real bug here is that 'ALTER USER ... DEFAULT ROLE...' doesn't check if the role(s) actually exists.
[29 Aug 2017 9:01] Kristofer Pettersson
Posted by developer:
 
This is probably not a bug report but rather a feature request for making user name columns wider. Such a feature request can't be granted within the scope of roles alone, but would require a change for all authorization IDs. A worklog will be constructed as a placeholder for further discussions.
[29 Aug 2017 9:02] Kristofer Pettersson
Posted by developer:
 
This is probably not a bug report but rather a feature request for making user name columns wider. Such a feature request can't be granted within the scope of roles alone, but would require a change for all authorization IDs. A worklog will be constructed as a placeholder for further discussions.