Bug #91394 mysqlsh will not recognize root privileges if they are assigned with a role
Submitted: 24 Jun 2018 21:41 Modified: 19 Dec 2018 14:15
Reporter: Giuseppe Maxia (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:Shell AdminAPI InnoDB Cluster / ReplicaSet Severity:S1 (Critical)
Version:8.0.11 OS:Any
Assigned to: CPU Architecture:Any

[24 Jun 2018 21:41] Giuseppe Maxia
Description:
when the privileges of the cluster administrator user were assigned with a role, mysql shell complains that the user does not have privileges.

mysqlsh --uri=clusteradmin:msandbox@127.0.0.1:34024
Creating a session to 'clusteradmin@127.0.0.1:34024'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 9 (X protocol)
Server version: 8.0.11 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
MySQL Shell 8.0.11

Copyright (c) 2016, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type '\help' or '\?' for help; '\quit' to exit.

 MySQL  127.0.0.1:34024+ ssl  JS > dba.checkInstanceConfiguration()
Validating local MySQL instance listening at port 24024 for use in an InnoDB cluster...
ERROR: The account 'clusteradmin'@'localhost' is missing privileges required to manage an InnoDB cluster:
Missing global privileges: CREATE USER, FILE, GRANT OPTION, PROCESS, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SHUTDOWN, SUPER.
Missing privileges on schema 'mysql': DELETE, INSERT, SELECT, UPDATE.
Missing privileges on schema 'mysql_innodb_cluster_metadata': ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE.
Missing privileges on schema 'sys': SELECT.
Missing privileges on table 'performance_schema.replication_applier_configuration': SELECT.
Missing privileges on table 'performance_schema.replication_applier_status': SELECT.
Missing privileges on table 'performance_schema.replication_applier_status_by_coordinator': SELECT.
Missing privileges on table 'performance_schema.replication_applier_status_by_worker': SELECT.
Missing privileges on table 'performance_schema.replication_connection_configuration': SELECT.
Missing privileges on table 'performance_schema.replication_connection_status': SELECT.
Missing privileges on table 'performance_schema.replication_group_member_stats': SELECT.
Missing privileges on table 'performance_schema.replication_group_members': SELECT.
Missing privileges on table 'performance_schema.threads': SELECT.
For more information, see the online documentation.
Dba.checkInstanceConfiguration: The account 'clusteradmin'@'localhost' is missing privileges required to manage an InnoDB cluster. (RuntimeError)

However, the user has all the privileges:

$ mysql  -u clusteradmin
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.11 MySQL Community Server - GPL

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

node2 [localhost] {clusteradmin} ((none)) > show grants\G
*************************** 1. row ***************************
Grants for clusteradmin@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `clusteradmin`@`localhost` WITH GRANT OPTION
*************************** 2. row ***************************
Grants for clusteradmin@localhost: GRANT BACKUP_ADMIN,BINLOG_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SET_USER_ID,SYSTEM_VARIABLES_ADMIN,XA_RECOVER_ADMIN ON *.* TO `clusteradmin`@`localhost` WITH GRANT OPTION
*************************** 3. row ***************************
Grants for clusteradmin@localhost: GRANT `root`@`localhost` TO `clusteradmin`@`localhost`
3 rows in set (0.00 sec)

How to repeat:
# mysql as root
create user clusteradmin@'localhost' identified by 'msandbox;
grant root@'localhost' to clusteradmin@'localhost';
set default role root@'localhost' to clusteradmin@'localhost';

# mysqlsh as clusteradmin
dba.checkInstanceConfiguration()

Suggested fix:
It appears that the check for user's grants is performed by looking at the user table, where all privileges are unset.
The shell should also check which roles are assigned to the user and which ones are active.
[25 Jun 2018 5:28] MySQL Verification Team
Hello Giuseppe,

Thank you for the report and feedback!

Thanks,
Umesh
[19 Dec 2018 14:15] David Moss
Posted by developer:
 
Thank you for your feedback, this has been fixed in upcoming versions and the following was added to the 8.0.14 changelog:
The dba.checkInstanceConfiguration() operation did not recognize privileges when they were associated to a user through a role (available in MySQL server 8.0 and higher). In such a case, a missing privileges error was being incorrectly issued despite the user possessing all the required privileges. Now users with their privileges assigned by roles are recognized by AdminAPI operations correctly.