MySQL Bugs: #82987: SHOW CREATE USER doesn't show default role

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

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)

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

Posted by developer:
 
Fixed in 8.0.3.

CREATE USER now permits a DEFAULT ROLE clause enabling the account
default roles to be specified.

SHOW CREATE USER now displays the account default roles if the
default is not NONE.