mariadb

How to unstuck MariaDB 11.3.2 when GRANT ALL stops working for root@localhost

I have an application that runs the following SQL command (as the root user) on startup:

GRANT ALL PRIVILEGES ON `pcom`.* TO 'pcom'@'localhost';

This has worked just fine for 10+ years.

After upgrading to MariaDB 11.3.2 (MacOS Homebrew) it no longer works, instead returning this error:

ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'pcom'

I think that I've fallen victim to MariaDB bug MDEV-33554 or something like it.

However, I have no idea how to fix the problem.

Does anyone know the magic SQL command to unstuck MariaDB out of this situation??

Here are the grants that MariaDB reports for 'root'@'localhost':

MariaDB [(none)]> SHOW GRANTS for 'root'@'localhost' \G
*************************** 1. row ***************************
Grants for root@localhost: GRANT SELECT, INSERT, UPDATE, DELETE,
CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX,
ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES,
EXECUTE, REPLICATION SLAVE, BINLOG MONITOR, CREATE VIEW, SHOW VIEW,
CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER,
CREATE TABLESPACE, DELETE HISTORY, SET USER, FEDERATED ADMIN,
CONNECTION ADMIN, READ_ONLY ADMIN, REPLICATION SLAVE ADMIN,
REPLICATION MASTER ADMIN, BINLOG ADMIN, BINLOG REPLAY,
SLAVE MONITOR ON *.* TO `root`@`localhost` WITH GRANT OPTION
*************************** 2. row ***************************
Grants for root@localhost: GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION
2 rows in set (0.000 sec)
Solution

  • The privilege missing was SHOW CREATE ROUTINE.

    To gain it back:

    1. Start the server after adding skip-grant-tables as a config option.

    2. On a mariadb prompt:

    MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> GRANT ALL ON *.* TO root@localhost WITH GRANT OPTION;
Query OK, 0 rows affected (0.001 sec)
    1. Remove skip-grant-tables from configuration option.

    Apologies for the usability error. Per bug report this won't affect users that skip the 11.3 version.