MySql 5.5 intermittently allows access to command SHOW BIN LOGS

Question

I've written software that reads the MySQL bin log and uses it to sync data to another, external application. I did my development and testing on versions of MySql and MariaDB without issue. We've now deployed our application to a new client, they are using MySQL 5.5.

We created a new user using these commands:

CREATE USER 'myuser' IDENTIFIED BY 'password';
GRANT REPLICATION CLIENT ON *.* TO 'myuser';
GRANT REPLICATION SLAVE ON *.* TO 'myuser';
GRANT SELECT ON *.* TO 'myuser';
FLUSH PRIVILEGES;

This gave our new user the same permissions our application has needed everywhere else it has been run. And, sure enough, when starting the application and logging in as this user everything worked perfectly. The following day the application was still running strong but we needed to restart it because a separate heartbeat feature needed to be reset (we use this feature to determine availability). When we restarted it we started getting MySQL permissions errors:

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

The error occurs when the application tries to run this command:

SHOW BINARY LOGS

If I log into MySQL Workbench as our user and try to execute SHOW BINARY LOGS, sure enough, I get the same error. But this worked for nearly 24 hours before and there haven't been any configuration changes. I have double and triple checked my user permissions in various ways including:

SHOW GRANTS FOR 'myuser'@'localhost';

Which comes back as

GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO `myuser`@`localhost` IDENTIFIED BY PASSWORD '***************'

I have tried executing FLUSH PRIVILEGES again in case somehow the in-memory GRANT table had become corrupted, but that also didn't help.

Does anyone know what could possibly be wrong here? Is there an old bug in MySQL 5.5 that causes permissions to behave finicky? Or, maybe MySQL 5.5 really does require a SUPER permission for SHOW BINARY LOGS? And if so, how can it be explained that we received thousands of database updates over the 24 hours that the application was working? These are current theories I have but I haven't been able to substantiate them with research.

Answer 1

https://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-6.html says:

Important Change; Replication: The SHOW BINARY LOGS statement (and its equivalent SHOW MASTER LOGS) may now be executed by a user with the REPLICATION CLIENT privilege. (Formerly, the SUPER privilege was necessary to use either form of this statement.)

So this is a new change implemented in an early milestone of MySQL 5.6, circa August 2012 (more than ten years ago!).

It took some digging, but I found the manual page for SHOW BINARY LOGS in MySQL 5.5: https://docs.oracle.com/cd/E19957-01/mysql-refman-5.5/sql-syntax.html#show-binary-logs It does not mention which privilege is required, so I'll trust that the 5.6 release notes are correct.

If you must support MySQL 5.5 (which I still think is a dubious requirement in 2022), then you must ensure that the client has SUPER privilege.