mysql中delete fro mysql.user where XX和drop user的不同
網友反映一個問題：
刪除使用者之後，再建立一個一樣的使用者，原來使用者的許可權還在。為什麼？
讓他重現過程，根據他的過程我在本機也可以重現：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| bin |
| mysql |
| performance_schema |
| test |
| ttt |
+--------------------+
6 rows in set (0.00 sec)
mysql> grant all on test.* to 'tt'@'%' identified by 'tt'; --建立使用者tt給test庫的許可權
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host from mysql.user;
+------+-----------------------+
| user | host |
+------+-----------------------+
| % | % |
| sla | % |
| song | % |
| tt | % | --使用者tt 在這裡
| aaaa | 10.28% |
| root | 127.0.0.1 |
| root | ::1 |
| root | localhost |
| root | localhost.localdomain |
+------+-----------------------+
9 rows in set (0.00 sec)
mysql> show grants for 'tt'@'%';
+---------------------------------------------------------------------------------------------------+
| Grants for tt@% |
+---------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'tt'@'%' IDENTIFIED BY PASSWORD '*8A4C0190D23732FF96AA783D5D7B1AD95A0FA6DE' |
| GRANT ALL PRIVILEGES ON `test`.* TO 'tt'@'%' --有test庫的許可權 |
+---------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> grant all on mysql.* to 'tt'@'%'; --再多給tt一個mysql庫的許可權
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'tt'@'%';
+---------------------------------------------------------------------------------------------------+
| Grants for tt@% |
+---------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'tt'@'%' IDENTIFIED BY PASSWORD '*8A4C0190D23732FF96AA783D5D7B1AD95A0FA6DE' |
| GRANT ALL PRIVILEGES ON `test`.* TO 'tt'@'%' |
| GRANT ALL PRIVILEGES ON `mysql`.* TO 'tt'@'%' |
+---------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from mysql.user where user='tt'; --刪除使用者
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host from mysql.user; --tt不在了
+------+-----------------------+
| user | host |
+------+-----------------------+
| % | % |
| sla | % |
| song | % |
| aaaa | 10.28% |
| root | 127.0.0.1 |
| root | ::1 |
| root | localhost |
| root | localhost.localdomain |
+------+-----------------------+
8 rows in set (0.00 sec)
mysql> grant all on ttt.* to 'tt'@'%' identified by 'tt'; --重新建立一個一樣的使用者給ttt庫許可權
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'tt'@'%'; --這時檢視許可權，卻有很多。
+---------------------------------------------------------------------------------------------------+
| Grants for tt@% |
+---------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'tt'@'%' IDENTIFIED BY PASSWORD '*8A4C0190D23732FF96AA783D5D7B1AD95A0FA6DE' |
| GRANT ALL PRIVILEGES ON `mysql`.* TO 'tt'@'%' |
| GRANT ALL PRIVILEGES ON `test`.* TO 'tt'@'%' |
| GRANT ALL PRIVILEGES ON `ttt`.* TO 'tt'@'%' |
+---------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)
問題重現完成。
想想他這個問題： 把delete from mysql.user改為 drop user語句，再次測試。
mysql> drop user tt@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all on ttt.* to 'tt'@'%' identified by 'tt';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'tt'@'%'; --這樣是正常的了。
+---------------------------------------------------------------------------------------------------+
| Grants for tt@% |
+---------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'tt'@'%' IDENTIFIED BY PASSWORD '*8A4C0190D23732FF96AA783D5D7B1AD95A0FA6DE' |
| GRANT ALL PRIVILEGES ON `ttt`.* TO 'tt'@'%' |
+---------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
應該是drop user的時候級連刪除了許可權表中的東西，試試能不能從binlog中分析出來點什麼：
mysql> show master status;
+------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------+----------+--------------+------------------+-------------------+
| bin.000035 | 5277 | | | |
+------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> drop user tt@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> show binlog events from 5277;
Empty set (0.00 sec)
mysql> show binlog events in 'bin.000035' from 5277;
+------------+------+------------+-----------+-------------+-------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------+------+------------+-----------+-------------+-------------------------------+
| bin.000035 | 5277 | Query | 2 | 5369 | use `mysql`; drop user tt@'%' | --從binlog中看不出來什麼 東西。
+------------+------+------------+-----------+-------------+-------------------------------+
1 row in set (0.00 sec)
自己檢視相關的許可權表吧：
mysql> select * from db\G
*************************** 1. row ***************************
Host: %
Db: test
User:
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Grant_priv: N
References_priv: Y
Index_priv: Y
Alter_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: N
Execute_priv: N
Event_priv: Y
Trigger_priv: Y
*************************** 2. row ***************************
Host: %
Db: test\_%
User:
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Grant_priv: N
References_priv: Y
Index_priv: Y
Alter_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: N
Execute_priv: N
Event_priv: Y
Trigger_priv: Y
2 rows in set (0.00 sec)
mysql> grant all on ttt.* to 'tt'@'%' identified by 'tt';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from db\G
*************************** 1. row ***************************
Host: %
Db: test
User:
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Grant_priv: N
References_priv: Y
Index_priv: Y
Alter_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: N
Execute_priv: N
Event_priv: Y
Trigger_priv: Y
*************************** 2. row ***************************
Host: %
Db: test\_%
User:
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Grant_priv: N
References_priv: Y
Index_priv: Y
Alter_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: N
Execute_priv: N
Event_priv: Y
Trigger_priv: Y
*************************** 3. row ***************************
Host: %
Db: ttt
User: tt
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Grant_priv: N
References_priv: Y
Index_priv: Y
Alter_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Execute_priv: Y
Event_priv: Y
Trigger_priv: Y
3 rows in set (0.00 sec)
mysql> delete from mysql.user where user='tt';
Query OK, 1 row affected (0.00 sec)
mysql> select * from db\G
*************************** 1. row ***************************
Host: %
Db: test
User:
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Grant_priv: N
References_priv: Y
Index_priv: Y
Alter_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: N
Execute_priv: N
Event_priv: Y
Trigger_priv: Y
*************************** 2. row ***************************
Host: %
Db: test\_%
User:
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Grant_priv: N
References_priv: Y
Index_priv: Y
Alter_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: N
Execute_priv: N
Event_priv: Y
Trigger_priv: Y
*************************** 3. row ***************************
Host: %
Db: ttt
User: tt
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Grant_priv: N
References_priv: Y
Index_priv: Y
Alter_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Execute_priv: Y
Event_priv: Y
Trigger_priv: Y
3 rows in set (0.00 sec) --使用delete from mysql.user刪除使用者，許可權表裡的記錄依然還在
mysql> drop user tt@'%'; --雖然mysql.user表中沒有這個記錄了，但是命令依然能完成，這裡也說明不是最先校驗user表中的記錄
Query OK, 0 rows affected (0.00 sec)
mysql> select * from db\G
*************************** 1. row ***************************
Host: %
Db: test
User:
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Grant_priv: N
References_priv: Y
Index_priv: Y
Alter_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: N
Execute_priv: N
Event_priv: Y
Trigger_priv: Y
*************************** 2. row ***************************
Host: %
Db: test\_%
User:
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Grant_priv: N
References_priv: Y
Index_priv: Y
Alter_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: N
Execute_priv: N
Event_priv: Y
Trigger_priv: Y
2 rows in set (0.00 sec) --使用drop user後變為了兩行，許可權表裡的內容隨著drop user刪除了
總結：
使用delete from mysql.user where這種方法刪除使用者時，只能刪除user表中的記錄，相關的許可權表中的記錄還是存在的，如果再建立一樣的使用者，會有上次使用者的許可權。
刪除使用者時使用drop user 會把使用者表和許可權表中的相關記錄都刪掉（應該還有一些別的字典資訊一起被刪除）。
資料字典表還是不要直接操作的好.
轉載請註明源出處
QQ 273002188 歡迎一起學習
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25099483/viewspace-1655282/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- truncate和不帶where子句的delete, 以及drop區別delete
- Oracle中drop user和drop user cascade的區別Oracle
- 詳解SQL中drop、delete和truncate的異同SQLdelete
- mysql資料庫中刪除資料的三種形式 drop和 truncate 和 deleteMySql資料庫delete
- 簡述truncate、delete和dropdelete
- HWM和delete，drop，truncate的關係delete
- ERROR 1054 (42S22): Unknown column 'plugin' in 'mysql.user'ErrorPluginMySql
- MERGE新特性(UPDATE WHERE,DELETE WHERE,INSERT WHERE)delete
- drop、delete 與truncatedelete
- [轉][資料庫SQL語句]truncate/delete/drop同與不同資料庫SQLdelete
- mysql 的delete from where 子查詢的一些限制MySqldelete
- Truncate,Delete,Drop的比較.delete
- MySQL和Oracle中的delete,truncate對比MySqlOracledelete
- truncate delete drop 區別delete
- truncate,delete,drop的異同點delete
- 資料庫關鍵詞 drop、truncate和delete的用法資料庫delete
- truncate,delete,drop的異同點（原）delete
- 關於delete,drop,truncate的問題delete
- MySQL 5.7 建立使用者報錯 ERROR 1805 (HY000): Column count of mysql.user is wrongMySqlError
- MySQL 5.5原始碼安裝時啟動資料庫報錯"Table 'mysql.user' doesn't exist??"MySql原始碼資料庫
- 詳解MySQL中WHERE子句的用法MySql
- MyBatis中的＜where＞標籤和where子句的區別MyBatis
- 深入解析delete和truncate不同之處:delete
- Permission denied:user=xxx,access=WRITE,inode=xxx
- Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist解決辦法ErrorMySql
- 資料庫：drop、truncate、delete的區別資料庫delete
- SQL truncate 、delete與drop區別SQLdelete
- sql：delete if exists還是drop if exists?SQLdelete
- Oracle 反向索引 where index_column like '%xxx'Oracle索引Index
- SQL中where和on的區別SQL
- MySQL之Where和Having的區別MySql
- How To Delete An Oracle Applications UserdeleteOracleAPP
- maridb Error 'Operation DROP USER failed forErrorAI
- RMAN 中delete exipired 和 delete obsolete 的區別delete
- rman 中的 delete all input 和 delete input 的區別delete
- PostgreSQL DBA(151) - pgAdmin（log_XXX parameter：where）SQL
- oracle恢復表delete/truncate/drop的方法總結Oracledelete
- Truncate table 詳解及與delete,drop 的區別delete