mysql如何彻底删掉用户
发布时间：2022-01-11 21:16:42 所属栏目：MySql教程 来源：互联网
导读：小编给大家分享一下mysql如何彻底删除用户，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！ 之前碰到 一个奇怪问题,删除了mysql.user里面的某个用户后,flush privileges后
|
小编给大家分享一下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'@'%' | +-------------------------------------------------------------------------------------------------------+ （编辑：PHP编程网 - 黄冈站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
浙公网安备 33038102330482号