浅析drop user与delete from mysql.user的区别
2024-07-24 13:02:06
供稿：网友
drop user 会将该用户的信息全部删掉，而 delete 只会清除user表，其他的比如db表中的信息还是存在。
如果delete 后，再创建一个最小权限的用户，那么他会重用以前的权限。
grant all on test.* to 'test'@'%' identified by 'test';
delete from user where user =test;
差看db 表
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 |
+------+---------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| % | test || Y| Y| Y| Y| Y| Y| N| Y| Y| Y| Y| Y| Y| Y| Y| N| N| Y| Y|
| % | test/_% || Y| Y| Y| Y| Y| Y| N| Y| Y| Y| Y| Y| Y| Y| Y| N| N| Y| Y|
| % | test | test | Y| Y| Y| Y| Y| Y| N| Y| Y| Y| Y| Y| Y| Y| Y| Y| Y| Y| Y|
+------+---------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
3 rows in set (0.00 sec)
test 的权限依然存在
创建一个只有select 的 用户test
grant select on test.* to 'test'@'%' identified by 'test';
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 |
+------+---------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
| % | test || Y| Y| Y| Y| Y| Y| N| Y| Y| Y| Y| Y| Y| Y| Y| N| N| Y| Y|
| % | test/_% || Y| Y| Y| Y| Y| Y| N| Y| Y| Y| Y| Y| Y| Y| Y| N| N| Y| Y|
| % | test | test | Y| Y| Y| Y| Y| Y| N| Y| Y| Y| Y| Y| Y| Y| Y| Y| Y| Y| Y|
+------+---------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+
3 rows in set (0.00 sec)
权限和以前的一样。
