mysql如何徹底刪除用戶

小編給大家分享一下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如何徹底刪除用戶”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注億速云行業資訊頻道！