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.
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.