以前对mysql用户授权和回收都是使用grant 和revoke没出现过任何问题,昨天领导更改了办公网ip(蛋疼),所有办公网连接数据库都报错(恩,不要抱怨)。然后默默的开始写脚本批量更新上百台服务器的权限,更新脚本如下:
mysql -umysqldba -pxxx -e "update mysql.user set host='218.247.217.66' where host like '124.205.%';flush privileges;
跑完后,自觉得意,改完后确实能连接上但是无法访问库,查询发现所有对库的权限都没有了!人世间最痛苦的事莫过如此。
具体测试分析如下:
原始账号信息:
MariaDB [mysql]> show grants for sem3_prd@'218.247.217.66';
+-------------------------------------------------------------------------------------------------------------------------------+
| Grants for sem3_prd@218.247.217.66 |
+-------------------------------------------------------------------------------------------------------------------------------+
| GRANT SHOW DATABASES ON *.* TO 'sem3_prd'@'218.247.217.66' IDENTIFIED BY PASSWORD '*50127D5E02833F70F089E12126AD6C841843B992' |
| GRANT ALL PRIVILEGES ON `sem3_prd`.* TO 'sem3_prd'@'218.247.217.66' |
+-------------------------------------------------------------------------------------------------------------------------------+
执行更新:
update mysql.user set host='192.123.211.110' where host like '%218.247.217.66%';
flush privileges;
查看结果:
show grants for sem3_prd@'192.123.211.110';
+--------------------------------------------------------------------------------------------------------------------------------+
| Grants for sem3_prd@192.123.211.110 |
+--------------------------------------------------------------------------------------------------------------------------------+
| GRANT SHOW DATABASES ON *.* TO 'sem3_prd'@'192.123.211.110' IDENTIFIED BY PASSWORD '*50127D5E02833F70F089E12126AD6C841843B992' |
+--------------------------------------------------------------------------------------------------------------------------------+
发现丢失了对 sem3_prd库的访问权限!!!
这里首先想到的当然是db表的原因,因为db存放的是账号对库的权限信息,好现在查询如下:
查看当前host 192.123.211.110对应的库权限
select * from db where host like '%192.123.211.110%' \G
Empty set (0.00 sec)
查看原始host 218.247.217.66 对应的库权限
select * from db where host like '%218.247.217.66%' \G
*************************** 1. row ***************************
Host: 218.247.217.66
Db: sem3_prd
User: sem3_prd
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
1 row in set (0.00 sec)
一目了然了,是因为db里的host信息没有修改导致对库的权限丢失,好现在执行更新
update mysql.db set host='192.123.211.110' where host like '%218.247.217.66%';
flush privileges; -- 这里需要注意如果不flush 重新加载权限表,下面的查询将无法显示修改的效果
在查看一下权限信息:
show grants for sem3_prd@'192.123.211.110';
+--------------------------------------------------------------------------------------------------------------------------------+
| Grants for sem3_prd@192.123.211.110 |
+--------------------------------------------------------------------------------------------------------------------------------+
| GRANT SHOW DATABASES ON *.* TO 'sem3_prd'@'192.123.211.110' IDENTIFIED BY PASSWORD '*50127D5E02833F70F089E12126AD6C841843B992' |
| GRANT ALL PRIVILEGES ON `sem3_prd`.* TO 'sem3_prd'@'192.123.211.110' |
+--------------------------------------------------------------------------------------------------------------------------------+
看到红色字体,突然有一种内牛满面的赶脚。
ps:反思,这其实不是一个技术问题,而是粗心大意。做为一个dba很多时候需要细心,细心再细心!
不要迷恋权威,但也不要太相信自己,做事之前一定要三思而后行,多测试,测试还是测试。还好这次
操作影响不大,否则后悔都来不及,前车之鉴,诸君亦请留意!