Dolphie Issue with MySQL Aurora on 3.04.0 with Backup_Admin Privilege Needed

[jonathancua]

On Aurora-MySQL version: 8.0.mysql_aurora.3.04.0, I get this error.

  Failed to execute query

  /* dolphie */
          SELECT
              STORAGE_ENGINES ->> '$."InnoDB"."LSN"' - STORAGE_ENGINES ->> '$."InnoDB"."LSN_checkpoint"' AS checkpoint_age
          FROM
              performance_schema.log_status

  Access denied; you need (at least one of) the BACKUP_ADMIN privilege(s) for this operation

[charles-001]

Hi @jonathancua - thanks for your bug report! What does SHOW GRANTS return? If MySQL is saying you don't have the permission, maybe you don't actually have it. I know I had to manually add it to my superusers.

[jonathancua]

It returns this (note: I just obfuscated the username):

+
| Grants for XXX@%                                                                                                                                                                                                                                                                                                                                      |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE ROLE, DROP ROLE ON *.* TO `XXX`@`%` WITH GRANT OPTION |
| GRANT APPLICATION_PASSWORD_ADMIN,CONNECTION_ADMIN,REPLICATION_APPLIER,ROLE_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,XA_RECOVER_ADMIN ON *.* TO `XXX`@`%` WITH GRANT OPTION                                                                                                                                                              |
| GRANT `rds_superuser_role`@`%` TO `XXX`@`%`                                                                                                                                                                                                                                                                                                           |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.11 sec)

But on an Aurora-MySQL 3.03.0, dolphie does not have issue at all. And this is the grant of the superuser on this version.

| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE ROLE, DROP ROLE ON *.* TO `XXX`@`%` WITH GRANT OPTION |
| GRANT APPLICATION_PASSWORD_ADMIN,BACKUP_ADMIN,CONNECTION_ADMIN,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,REPLICATION_APPLIER,ROLE_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,XA_RECOVER_ADMIN ON *.* TO `XXX`@`%` WITH GRANT OPTION                                                                            |
| GRANT `AWS_COMPREHEND_ACCESS`@`%`,`AWS_LAMBDA_ACCESS`@`%`,`AWS_LOAD_S3_ACCESS`@`%`,`AWS_SAGEMAKER_ACCESS`@`%`,`AWS_SELECT_S3_ACCESS`@`%`,`rds_superuser_role`@`%` TO `XXX`@`%`

When I compare the grants of the 2 versions, it is interesting that the BACKUP_ADMIN privilege is not present on 3.04.0.

[charles-001]

@jonathancua - that's interesting. I looked at the release notes and don't see anything about removing it, but I do see they added SHOW_ROUTINE. I'd create a support ticket to ask why the superuser has this privilege missing. As a workaround, you should be able to create a new user with the required permissions for Dolphie.