Background
On a legacy server with an older version of MariaDB, restoring to a newer Virtualmin server that run MySQL 8 might be problematic. The errors manifest themselves like so:
Restore failed : SQL grant all on `6009\_rpa\_data`.* to '6009'@'localhost' with grant option failed : You are not allowed to create a user with GRANT
You might also encounter this error at other places in the system:
The server requested authentication method unknown to the client [caching_sha2_password]
There is not a magic bullet for solving this, and the reason is that permissions on MySQL 8 is more strict so you probably have to fiddle.
Solutions
Here are solutions that might work:
Update 20 September
After a particularly nasty system failure we noted that deleting the user from localhost, then adding then to 127.0.0.1, worked.
Do not backup database with complete backup
Sometimes the solution is to not backup the database and do it separately. So backup the files, then restore them. Then backup the database, and try to restore it.
Update 15 August 2021
As the this Stack article, MySQL has issues with grant and the sequence is different.
They summarize it as so in that article:
Starting with MySQL 8 you no longer can (implicitly) create a user using the GRANT command. Use CREATE USER instead, followed by the GRANT statement:
Notice the multi step process. Please note, this article pertains to root. If you’re working on a shared server, root will not be applicable.
mysql> CREATE USER 'root'@'%' IDENTIFIED BY 'PASSWORD'; mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION; mysql> FLUSH PRIVILEGES;
One way to troubleshooted is by opening two SSH windows:
SSH Window #1:
mysql -uroot
Test grant statement.
It fails
mysql> grant all privileges on database.* to broken-user@localhost with grant option; ERROR 1410 (42000): You are not allowed to create a user with GRANT
SSH Window #2:
mysql -uroot
Create user, which also fails!
mysql> create user broken-user@localhost identified by 'secret'; ERROR 1396 (HY000): Operation CREATE USER failed for 'broken-user'@'localhost' mysql> drop user broken-user@localhost; Query OK, 0 rows affected (0.00 sec)
However, after dropping the user, and then doing grant again, it works!
mysql> create user broken-user@localhost identified by 'secret'; Query OK, 0 rows affected (0.00 sec)
Now grant all is working!
mysql> grant all privileges on broken-user.* to database@localhost with grant option; Query OK, 0 rows affected (0.01 sec)
Here are the original solutions:
Grant all privileges
First login as root to MySQL. Then:
grant all privileges on database.* to database_user@'%' with grant option;
Be extremely careful of typos, as the resultant error completely throws one off.
Fiddle with Database Import
You might also want to try Edit Database / Import Database. It seems when backups are restored and MySQL Grant issues occur, these databases become orphaned and one way to re-establish the site link is to re-import them to the correct site.
Create User Again and Grant Again
In MySQL 8, user creation and grant was split. So what you could try is to create the user again, without a grant statement, and then try adding grant permissions.