Bug #82987 SHOW CREATE USER doesn't show default role
Submitted: 14 Sep 2016 12:00 Modified: 15 Sep 2016 5:53
Reporter: Daniël van Eeden (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Security: Roles Severity:S3 (Non-critical)
Version:8.0.0 OS:Any
Assigned to:

[14 Sep 2016 12:00] Daniël van Eeden
Description:
The default role for a user is not in the output of SHOW CREAT USER or SHOW GRANTS.

This means that the effective privileges after copying this user to another server might be different.

How to repeat:
mysql> select * from mysql.default_roles;
+------+-------+-------------------+-------------------+
| HOST | USER  | DEFAULT_ROLE_HOST | DEFAULT_ROLE_USER |
+------+-------+-------------------+-------------------+
| %    | user1 | %                 | role1,role2       |
+------+-------+-------------------+-------------------+
1 row in set (0.00 sec)

mysql> SHOW CREATE USER user1\G
*************************** 1. row ***************************
CREATE USER for user1@%: CREATE USER 'user1'@'%' IDENTIFIED WITH 'mysql_native_password' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK
1 row in set (0.00 sec)

mysql> SHOW GRANTS FOR user1\G
*************************** 1. row ***************************
Grants for user1@%: GRANT USAGE ON *.* TO `user1`@`%`
*************************** 2. row ***************************
Grants for user1@%: GRANT `role1`@`%`,`role2`@`%` TO `user1`@`%`
2 rows in set (0.00 sec)
[15 Sep 2016 5:53] Umesh Shastry
Hi Daniël,

Thank you for the report.
I agree, default role for a user is not in the output of SHOW CREATE USER or at least shown(in SHOW GRANTS..) without expanding it to the privileges the role represents and thus copying user to another server might not work as "expected" with output of SHOW CREATE USER../SHOW GRANTS FOR... 

Per manual with "USING" clause naming the granted roles for which to display privileges i.e

mysql> SHOW GRANTS FOR 'dev1'@'localhost';
+-------------------------------------------------+
| Grants for dev1@localhost                       |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO `dev1`@`localhost`        |
| GRANT `app_developer`@`%` TO `dev1`@`localhost` |
+-------------------------------------------------+

with "USING" clause to expand the privileges the role represents:

mysql> SHOW GRANTS FOR 'dev1'@'localhost' USING 'app_developer';
+----------------------------------------------------------+
| Grants for dev1@localhost                                |
+----------------------------------------------------------+
| GRANT USAGE ON *.* TO `dev1`@`localhost`                 |
| GRANT ALL PRIVILEGES ON `app_db`.* TO `dev1`@`localhost` |
| GRANT `app_developer`@`%` TO `dev1`@`localhost`          |
+----------------------------------------------------------+

Noted from https://dev.mysql.com/doc/refman/8.0/en/roles.html

Thanks,
Umesh