phpMyAdmin控制用户连接失败问题排查求助

为加固服务器，我移除了MySQL中的root账号，创建了AdminAccount用户并授予其在%和localhost的所有权限。此后登录phpMyAdmin时，每个页面底部都会出现如下错误，但未出现功能缺失，所有页面均可正常操作数据库：

mysqli::real_connect(): (HY000/1045): Access denied for user 'AdminAccount'@'localhost' (using password: YES) Connection for controluser as defined in your configuration failed.

已执行的检查操作 #

我执行了以下权限检查：

MariaDB [(none)]> show grants for 'root'@'localhost';
+-------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                   |
+-------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `root`@`localhost` IDENTIFIED BY PASSWORD '*48xxxxxxxxxxxxxx6' |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION                                               |
+-------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)
    
MariaDB [(none)]> show grants for 'root'@'%';
ERROR 1141 (42000): There is no such grant defined for user 'root' on host '%'
MariaDB [(none)]> show grants for 'AdminAccount'@'localhost';
+---------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for AdminAccount@localhost                                                                                                              |
+---------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `AdminAccount`@`localhost` IDENTIFIED BY PASSWORD '*4xxxxxxxxxxxxxxxxxxxxx6' WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `phpmyadmin`.* TO `AdminAccount`@`localhost`                                                                           |
+---------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

MariaDB [(none)]> show grants for 'AdminAccount'@'%';
+-------------------------------------------------------------------------------------------------------------------------------------+
| Grants for AdminAccount@%                                                                                                              |
+-------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `AdminAccount`@`%` IDENTIFIED BY PASSWORD '*48xxxxxxxxxxxxxxxxxxxxx6' WITH GRANT OPTION |
+-------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

已尝试的修复操作 #

我执行了以下操作，但均未解决问题：

MariaDB [(none)]> GRANT ALL ON *.* TO 'root'@'%' WITH GRANT OPTION;
MariaDB [(none)]> FLUSH PRIVILEGES;

还尝试删除空用户，但提示用户不存在：

DROP USER ''@'localhost';
DROP USER ''@'%';

环境配置信息 #

我的环境配置如下：

Database server
    Server: Localhost via UNIX socket
    Server type: MariaDB
    Server connection: SSL is not being used Documentation
    Server version: 10.5.18-MariaDB-0+deb11u1 - Debian 11
    Protocol version: 10
    User: AccountAdmin@localhost
    Server charset: UTF-8 Unicode (utf8mb4) 
Web server
    Apache/2.4.54 (Debian)
    Database client version: libmysql - mysqlnd 8.2.1
    PHP extension: mysqli curl mbstring 
    PHP version: 8.2.1

phpMyAdmin
    Version information: 5.1.4deb1~bpo11+1

补充排查发现 #

排查时发现，phpMyAdmin配置文件/etc/phpmyadmin/config.inc.php中没有常规的用户名密码配置：

$cfg['Servers'][$i]['user'] = 'your_username';
$cfg['Servers'][$i]['password'] = 'your_password';

反而存在以下配置，但$dbuser和$dbpass变量在/etc/phpmyadmin目录及子目录中均未找到（已用grep全量搜索）：

$cfg['Servers'][$i]['controluser'] = $dbuser;
$cfg['Servers'][$i]['controlpass'] = $dbpass;

问题根源 #

报错的核心是phpMyAdmin的controluser（控制用户）配置失效，而非你用来登录的AdminAccount用户权限问题。controluser是phpMyAdmin用来执行后台管理操作（比如读取设置、统计信息）的专用用户，当前配置里$dbuser和$dbpass未定义，导致phpMyAdmin尝试用错误的参数连接数据库，触发权限拒绝报错。

修复步骤 #

1. 创建有效的controluser
   在MariaDB中创建专门的phpMyAdmin控制用户，授予其phpmyadmin数据库的必要权限：

   CREATE USER 'pma'@'localhost' IDENTIFIED BY '你的安全密码';
   GRANT SELECT, INSERT, UPDATE, DELETE ON `phpmyadmin`.* TO 'pma'@'localhost';
   FLUSH PRIVILEGES;
   

2. 修正phpMyAdmin配置
   编辑/etc/phpmyadmin/config.inc.php，任选一种方式修正配置：

   • 方式一：直接替换为实际用户信息

     $cfg['Servers'][$i]['controluser'] = 'pma';
     $cfg['Servers'][$i]['controlpass'] = '你的安全密码';
     

   • 方式二：在配置文件开头定义变量

     $dbuser = 'pma';
     $dbpass = '你的安全密码';
     // 保留原有controluser配置
     $cfg['Servers'][$i]['controluser'] = $dbuser;
     $cfg['Servers'][$i]['controlpass'] = $dbpass;
     

3. 验证修复效果
   保存配置后重启Apache服务：

   systemctl restart apache2
   

   刷新phpMyAdmin页面，检查底部报错是否消失。

额外说明 #

• 之前恢复root@%权限和删除空用户的操作与当前报错无关，可根据服务器加固需求决定是否保留相关权限。
• controluser遵循最小权限原则，仅需phpmyadmin数据库的增删改查权限即可，无需全局所有权限。

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