网友反映一个问题:
删除用户之后,再建立一个一样的用户,原来用户的权限还在。为什么?
让他重现过程,根据他的过程我在本机也可以重现:
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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25099483/viewspace-1655282/