大网数据广告活动
服务器运维问答

MariaDB Can't Create New User

· 52CE 我爱测

Fresh install of MariaDB 10.4 (fedora 31). I login into mysql as root: MariaDB [mysql]> select User, Host,Password from user; +-------+-----------+-------------------------------------------+ | User | Host | Password | +-------+-…

问题原文

原帖链接 · gdm · CC BY-SA 4.0

Fresh install of MariaDB 10.4 (fedora 31). I login into mysql as root:

MariaDB [mysql]> select User, Host,Password from user;
+-------+-----------+-------------------------------------------+
| User  | Host      | Password                                  |
+-------+-----------+-------------------------------------------+
| root  | localhost | *A4B6157319038724E3560894F7F932C8886EBFCF |
| mysql | localhost | invalid                                   |
+-------+-----------+-------------------------------------------+
2 rows in set (0.001 sec)

MariaDB [mysql]

If I try to create a new user:

MariaDB [mysql]> create user 'developer'@'localhost' identified by 'pippo';
ERROR 1396 (HY000): Operation CREATE USER failed for 'developer'@'localhost'

As stated from the first table, the user developer doesn't exist. The permissions of root seem fine:

MariaDB [(none)]> SHOW GRANTs;
+-------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                               |
+-------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED VIA mysql_native_password OR unix_socket WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION                                                           |
+-------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

已采纳解答

原帖链接 · gdm · CC BY-SA 4.0

Found the problem. Before creating the user, I created a db from a dump. I checked the mysql.db tables and I found that the user 'developer' is in that table, BUT not in mysql.user.

MariaDB [mysql]> select * from db;
+------------+----------+-----------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| Host       | Db       | User      | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Create_tmp_table_priv | Lock_tables_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Execute_priv | Event_priv | Trigger_priv |
+------------+----------+-----------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| %          | mydb   | developer | Y           | Y           | Y           | Y           | Y           | Y         | N          | Y               | Y          | Y          | Y                     | Y                | Y                | Y              | Y                   | Y                  | Y            | Y          | Y            |
| 10.8.148.% | postal   | developer | Y           | Y           | Y           | Y           | Y           | Y         | N          | Y               | Y          | Y          | Y                     | Y                | Y                | Y              | Y                   | Y                  | Y            | Y          | Y            |
| 10.8.148.% | mydb   | developer | Y           | Y           | Y           | Y           | Y           | Y         | N          | Y               | Y          | Y          | Y                     | Y                | Y                | Y              | Y                   | Y                  | Y            | Y          | Y            |
| miyazaki   | mydb   | developer | Y           | Y           | Y           | Y           | Y           | Y         | N          | Y               | Y          | Y          | Y                     | Y                | Y                | Y              | Y                   | Y                  | Y            | Y          | Y            |
| %          | helpdesk | develop   | Y           | Y           | Y           | Y           | Y           | Y         | N          | Y               | Y          | Y          | Y                     | Y                | Y                | Y              | Y                   | Y                  | Y            | Y          | Y            |
| %          | helpdesk | developer | Y           | Y           | Y           | Y           | Y           | Y         | N          | Y               | Y          | Y          | Y                     | Y                | Y                | Y              | Y                   | Y                  | Y            | Y          | Y            |
+------------+----------+-----------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+

So that's the problem. I destroyed the disturbing db

MariaDB [mysql]> create user 'developer'@'localhost' identified by 'pippo';
Query OK, 0 rows affected (0.002 sec)

So, the solution is:

1 - Check for the incriminated user in these tables;

columns_priv, db, procs_priv, tables_priv

2 - When dumping, do not export unrelated tables.

摘自公开归档,保留原文语言,版式经整理,外部媒体与邮箱地址已移除。

资料来源与适用范围

资料来源
Server Fault
适用范围
以原帖系统、软件版本与发布日期为准(2020-02-14)
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-15
授权说明
CC BY-SA 4.0

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。