Search code examples
dockermariadb

MariaDB Docker container allows create db and create user but fails on grant?

I am wondering why a mariadb installation allows me to create a database and create the user but fails on granting privileges. Here is what I perceive:

MariaDB [mydb]> show grants;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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` IDENTIFIED BY PASSWORD '*32F06106642D20440E25FCD31ED89DE9431B9D43' WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.001 sec)

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

MariaDB has Server version: 11.3.2-MariaDB-1:11.3.2+maria~ubu2204 mariadb.org binary distribution available in the MariaDB docker container https://hub.docker.com/_/mariadb

What could be the cause? What else might I investigate on? And how can it be fixed?

Edit: The root cause given in How to unstuck MariaDB 11.3.2 when GRANT ALL stops working for root@localhost is applicable, root@localhost definitely is missing 'show routine'. I am not sure if more privileges are missing but at least that one is.

The suggested fix is difficult to apply since the parameters would have to be added to container startup somehow. Restarting mariadbd would implicitly kill the container.

Solution

  • As a container:

    First - start MariaDB with --skip-grant-tables to enable full root access for any connection:

    docker run --name mlatest --rm -v mariadbvol:/var/lib/mysql mariadb --skip-grant-tables

    To be able to grant, flush privileges is required, however the grant also needs to be executed in the same session so the full privileges it had on connection still apply:

    docker exec mlatest mariadb \
    -e 'flush privileges; grant all on *.* to root@localhost with grant option'

    This will restore whatever grants where missing from the all status of root@localhost.