MySQL Error (FLUSH PRIVILEGES) For A New Admin User

When modifying users in RDM we grant/revoke rights to the underlying tables. To make sure those changes are in effect we tell the sever to reload the grant/revoke tables via the FLUSH PRIVILEGES command.

Now the reason you get this error is because the MySQL didn't accept the command for this user (the error log should have the details of the error). Usually this is due to insufficient rights granted to the executing user.

How to fix it? Well, users can't grant more rights than they have themselves so the issue is growing as admin-A create admin-B then admin-B creates admin-C. I would suggest you login to RDM using 'root' or a 'root' equivalent and go to the Administration -> User -> hit Edit the OK on each admin user. This will validate and execute any missing grant/revokes those fixing any missing rights. Now with all should be good with admin-A, admin-B & admin-C.

Best regards,

Hi,

thanks for the fast reply. I've checked it and the rights of the executing user seem to be fine... the executing user was the MySQL root account and has all rights.

Best Regards

This may not be the best place to report a bug, but I've just run into this with a new install as well. To recap:

ADMIN_A is a super user with all permissions on the MariaDB install, i.e equivalent to root
GRANT ALL PRIVILEGES ON *.* TO 'ADMIN_A'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Connecting to this database I've created (rdm), creating the schema etc. all goes as planned

I then used ADMIN_A to create ADMIN_B, with "User type" set to "Administrator". This completes with no errors, which makes sense as ADMIN_A has the correct privilege's to do a flush.

The issue is, if I then use ADMIN_B to try and create ADMIN_C or USER_X, I get the error discussed above:
"The save was successful but the following commands failed. FLUSH PRIVILEGES"

Looking at the GRANT options on the server, it's clear that ADMIN-B does not have the permissions necessary to perform the flush.
SHOW GRANTS FOR ADMIN_B:

+-----------------------------------------------------------------------------------------------------------------------------------+
| Grants for ADMIN-B@%                                                                                                           |
+-----------------------------------------------------------------------------------------------------------------------------------+
| GRANT CREATE USER ON *.* TO 'ADMIN_B'@'%' IDENTIFIED BY PASSWORD '*50378C3B9EBAEEC633B913A3FA2F14B439421D26' WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `rdm`.* TO 'ADMIN_B'@'%' WITH GRANT OPTION                                                             |
| GRANT SELECT ON `mysql`.`user` TO 'ADMIN_B'@'%' WITH GRANT OPTION                                                              |
+-----------------------------------------------------------------------------------------------------------------------------------+

To do a flush, it requires the RELOAD permissions which are absent. (https://mariadb.com/kb/en/grant/#reload)
But, most importantly, the flush command is not required for the grants above to be active for new admins/users. These are loaded into memory immediately. (https://dev.mysql.com/doc/refman/5.7/en/privilege-changes.html)

So to be clear, this error does not cause any issues. But obviously a pop up like it can be concerning to someone unfamiliar with SQL, and is something that can be easily fixed. Either the RELOAD permission should be given to all Administrator users created through the software, or the "FLUSH PRIVILEGES" command can be dropped as it is not required.

@cpenford,

Wow, thank you for reporting this issue. You are correct on all points. The odd thing is this has been like this for a while and now it fails. Weird we haven't seen this issue before. Could it be different with older versions of MySQL/MariaDB?

What if we simply modify the code to not report the FLUSH PRIVILIEGES error but still try to execute the command? Serves two purposes.

1. Doesn't give user useless error feedback in 99% of the cases.
2. Since we added the command many years ago (in response to an issue), there might actually be a version of MySQL/MariaDB out there that requires/supports it so I wouldn't want to break that.

What do you think?

Best regards,

The odd thing is this has been like this for a while and now it fails. Weird we haven't seen this issue before. Could it be different with older versions of MySQL/MariaDB?

My guess would be you were previously granting wider permissions when a super-user created another admin, and once those were tightened up and the RELOAD permission was lost, the FLUSH command would start to error out. It's possible that older versions of MySQL/MariaDB required it, although I can find similar documentation saying it was not even as far back as v4.1 for MySQL (circa 2004). Even to this day most the top pages from a "mysql grant user permissions" Google search will tell you to flush privileges after doing grant commands, so it seems to be an ingrained habit.

Simply not displaying the error for that specific command is probably the easiest fix. It doesn't cause any harm if it fails in the above scenario, and maybe it's required for some specific setups.

Thank you for the feedback.

I will go ahead and make the change in our next release (2021.2.x) (ETA Q4-2021)

Best regards,