Docker容器中MariaDB可建库建用户但授权失败的排查求助

问题场景 #

运行Docker官方镜像的MariaDB 11.3.2版本时，可正常创建数据库和用户，但执行授权操作时失败。具体操作及报错如下：

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'

原因分析 #

1. 权限缺失：从SHOW GRANTS结果可明确看到，root@localhost缺少SHOW ROUTINE权限。执行GRANT ALL PRIVILEGES时会尝试授予包含该权限的全量权限，因当前用户自身无此权限，触发权限不足报错。
2. 版本初始化bug：MariaDB 11.3.x版本存在初始化阶段的权限配置问题，默认创建的root@localhost用户未被授予完整的必要权限。

排查方向 #

• 执行SHOW GRANTS FOR 'root'@'localhost';再次确认权限列表，排查是否存在其他缺失的权限项。
• 执行SHOW DATABASES;验证mydb数据库是否存在，且名称拼写无误。
• 检查容器启动时是否挂载了自定义权限配置脚本，或设置了限制root权限的环境变量。

解决方法 #

临时修复（无需重启容器） #

先补充缺失的权限给root用户，再执行目标授权：

-- 授予SHOW ROUTINE权限
GRANT SHOW ROUTINE ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

-- 执行目标授权操作
GRANT ALL PRIVILEGES ON mydb.* TO 'mydb'@'%';
FLUSH PRIVILEGES;

彻底解决（需重启容器） #

1. 先备份数据库数据，然后停止并删除当前容器：

docker stop your-mariadb-container
docker rm your-mariadb-container

2. 使用官方标准参数重新启动容器，确保root权限初始化完整：

docker run -d \
  --name your-mariadb-container \
  -e MARIADB_ROOT_PASSWORD=your-secure-password \
  -v mariadb-data:/var/lib/mysql \
  mariadb:11.3.2

3. 容器启动后重新登录，确认root权限完整后再执行授权操作。

内容的提问来源于stack exchange，提问作者queeg