小编给大家分享一下mysql如何彻底删除用户，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！
之前碰到 一个奇怪问题,删除了mysql.user里面的某个用户后,flush privileges后,在建同样的用户后,发现权限不对...后来才发现.是mysql.db在作怪;
大致讲下,在删除某个用户对某个库的的insert权限后,删除该用户,flush privileges后,在建同样名字和host的用户,给予相同库或其他库的update权限,结果发现,之前删除的insert权限又出现了.
原因是在mysql.db里面有存储对数据库的操作权限,虽然在mysql.user里面删除了用户,但并没有把权限删除.所以mysql在加载用户权限的时候,会去加载之前未删除的权限.如果是给的*.* 权限...不会记录到db表,没搞懂为什么,继续研究...
大致测了一下.如下:
如果授权是*.*,并不会记录到mysql.db表中,如下例:
mysql> grant select on *.* to tes222@'%' identified by '1111111';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from db where user ='tes222';
Empty set (0.00 sec)
mysql> grant delete on *.* to tes222@'%';
Query OK, 0 rows affected (0.05 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from db where user ='tes222';
Empty set (0.00 sec)
如果授权是固定的库名,就会记录:
mysql> grant delete on test.* to tes222@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from db where user ='tes222';
+------+------+--------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| 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 |
+------+------+--------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| % | test | tes222 | N | N | N | Y | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N |
+------+------+--------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
1 row in set (0.00 sec)
mysql> delete from mysql.user where user ='tes222';
Query OK, 1 row affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from db where user ='tes222';
+------+------+--------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| 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 |
+------+------+--------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| % | test | tes222 | N | N | N | Y | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N |
+------+------+--------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
1 row in set (0.00 sec)
mysql> grant insert on test.* to tes222@'%' identified by '111333';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
mysql>
mysql>
mysql> select * from db where user ='tes222';
+------+------+--------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| 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 |
+------+------+--------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| % | test | tes222 | N | Y | N | Y | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N |
+------+------+--------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
1 row in set (0.00 sec)
mysql> show grants for tes222@'%';
+-------------------------------------------------------------------------------------------------------+
| Grants for tes222@% |
+-------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'tes222'@'%' IDENTIFIED BY PASSWORD '*5EB462FE941D41EF8FAB7467C66B5CEC646731A2' |
| GRANT INSERT, DELETE ON `test`.* TO 'tes222'@'%' |
+-------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
通过revoke后,在mysql.db里面不存在数据了.
mysql> show grants for tes222@'%';
+-------------------------------------------------------------------------------------------------------+
| Grants for tes222@% |
+-------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'tes222'@'%' IDENTIFIED BY PASSWORD '*5EB462FE941D41EF8FAB7467C66B5CEC646731A2' |
| GRANT INSERT, DELETE ON `test`.* TO 'tes222'@'%' |
+-------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql>
mysql>
mysql>
mysql> revoke delete on test.* from tes222@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from mysql.db where user ='tes222';
+------+------+--------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| 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 |
+------+------+--------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| % | test | tes222 | N | Y | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N |
+------+------+--------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
1 row in set (0.00 sec)
mysql> revoke all privileges on test.* from tes222@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from mysql.db where user ='tes222';
Empty set (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for tes222@'%';
+-------------------------------------------------------------------------------------------------------+
| Grants for tes222@% |
+-------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'tes222'@'%' IDENTIFIED BY PASSWORD '*5EB462FE941D41EF8FAB7467C66B5CEC646731A2' |
+-------------------------------------------------------------------------------------------------------+
所以,个人建议是,如果要彻底删除用户权限,还是先revoke,然后在delete用户表mysql.user
以上是“mysql如何彻底删除用户”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注亿速云行业资讯频道！
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
