答案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;