MySQL 警告“用户存在”,但用户不在“用户”表中

MySQL 警告“用户存在”,但用户不在“用户”表中

我已经遇到这个问题好几个星期了。我不知道下一步该去哪里找。我已经清理、刷新、重新启动MySQL服务、重新启动Ubuntu服务器。是什么原因导致这个问题WARNING,导致用户不显示在user表中?我也试过了DROP user,但0 rows affected结果却不是这样。这真是令人难以置信地沮丧!用户信息还存储在架构中的哪里?我该如何清除它?

截屏

更新

当我 grep 用户名时,/var/lib/mysql/mysql我在文件中找到了用户名db.MYD。虽然我无法编辑它。所以我知道用户名存在于用户表以外的某个地方。

答案1

当创建用户并授予其权限,然后从 mysql.user 中删除而不是被删除时,会发生这种情况:

首先,创建一个用户admin_x@localhost

mysql> create user admin_x@localhost identified by 'abc123';
Query OK, 0 rows affected (0.01 sec)

检查用户是否在mysql.user

mysql> select user, host from mysql.user where user='admin_x';
+---------+-----------+
| user    | host      |
+---------+-----------+
| admin_x | localhost |
+---------+-----------+
1 row in set (0.01 sec)

好的。

现在我们授予该用户访问数据库的权限test

mysql> grant all on test.* to admin_x@localhost;
Query OK, 0 rows affected (0.00 sec)

并检查一下:

mysql> show grants for admin_x@localhost;
+-----------------------------------------------------------+
| Grants for admin_x@localhost                              |
+-----------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'admin_x'@'localhost'      |
| GRANT ALL PRIVILEGES ON `test`.* TO 'admin_x'@'localhost' |
+-----------------------------------------------------------+
2 rows in set (0.00 sec)

现在删除用户不当mysql.user

mysql> delete from mysql.user where user='admin_x';
Query OK, 1 row affected (0.00 sec)

并且用户不再处于mysql.user

mysql> select user from mysql.user where user='admin_x';
Empty set (0.00 sec)

但是当您现在尝试创建新的它时,您会收到一个错误:

mysql> create user admin_x@localhost identified by 'abc123';
ERROR 1396 (HY000): Operation CREATE USER failed for 'admin_x'@'localhost'

这是因为admin_x@localhost仍然有存储在的权限mysql.db

mysql> select User from mysql.db where user='admin_x';
+---------+
| User    |
+---------+
| admin_x |
+---------+
1 row in set (0.00 sec)

现在,当你删除用户时

mysql> drop user admin_x@localhost;
Query OK, 0 rows affected (0.00 sec)

它确实消失了,你可以再次创建它:

mysql> create user admin_x@localhost identified by 'abc123';
Query OK, 0 rows affected (0.00 sec)

答案2

首先需要授予用户使用权限,这实际上授予了 MariaDB 的登录权限:

GRANT USAGE ON *.* TO 'myuser' IDENTIFIED BY 'your_pwd';

然后就可以开始授予用户数据库的权限了:

GRANT ALL PRIVILEGES ON 'my_db'.* TO 'myuser' IDENTIFIED BY 'your_pwd';

然后不要忘记冲洗:

FLUSH PRIVILEGES;

相关内容