MySQL Bugs: #82956: DEFAULT_ROLE_HOST and DEFAULT_ROLE

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

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

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

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 ?

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/)

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.

Ok. so the real bug here is that 'ALTER USER ... DEFAULT ROLE...' doesn't check if the role(s) actually exists.

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.

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.