MariaDB 11.3: ERROR 1044 (42000): Access denied for user root@localhost to database

Recently, when trying to setup this blog, I got stuck with an unusual error. When granting permission to new user for wordpress database, it gave Access denied error.

MariaDB [(none)]> GRANT ALL PRIVILEGES ON wordpressblog.* TO wordpressuser@localhost;
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'wordpressblog'

This doesn’t make sense, since root is supposed to have all the access. And I have created users before, and given them access, with same user and on same machine.

Searching online, or asking in couple of help channels didn’t help much, so, to debug the exact permission that might be giving me error, I tried checking the GRANTs and it turned out I was missing SHOW CREATE ROUTINE GRANT.

After that it was simple to just replace GRANT ALL with the exact list of GRANTs. Though after finding the problem, it turns out it’s a new GRANT added in Mariadb and is mentioned in the Release Notes for 11.3.2. To quote:

Note that upgrading from earlier versions will leave all users, including root and other users with ALL PRIVILEGES ON *.*, and ALL PRIVILEGES ON db.*, without the SHOW CREATE ROUTINE privilege. While the users with read privileges on mysql.proc will be have the effective privileges of SHOW CREATE ROUTINE, they are unable to GRANT them.

To gain the privileges back on a global user, e.g. root@localhost:

1. Start server with skip-grant-tables as a configuration option
2. Run mariadb client
3. Execute FLUSH PRIVILEGES
4. Execute GRANT ALL ON *.* TO root@localhost WITH GRANT OPTION
5. Remove the skip-grant-tables option from the configuration file.

To re-gain a database level privileges, using a user with SHOW CREATE ROUTINE on *.* execute GRANT ALL ON db.* TO user] optionally WITH GRANT OPTION.

In hindsight, that’s a very simple issue, but it stumped me for quite a bit. Hence sharing it so it can be helpful to others.