一次mysql的授權故障排除

標籤：mysql   百度   資料庫   如何   localhost   

描述一下情況，有一個資料庫xiyouzyadlog用sync授權 ，結果我在授權的時候授權給了xiyoumainTXZYANDROID 所有許可權，導致sync使用者登陸能看到所有的資料庫，於是刪除sync使用者結果sync登陸後依然能看到所有的資料庫包括mysql，然後百度查看mysql的授權，結果出現下面的結果，下面就示範下如何刪除沒有用的授權資訊mysql> show grants for  [email protected]‘localhost‘;+-------------------------------------------------------------------------------------------------------------+| Grants for [email protected]                                                                                   |+-------------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO ‘sync‘@‘localhost‘ IDENTIFIED BY PASSWORD ‘*B805E12E6933FF815F344D9F4B0D7236B223DC86‘ || GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON `xiyoumainTXZYANDROID`.* TO ‘sync‘@‘localhost‘        || GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON `xiyouzyadlog`.* TO ‘sync‘@‘localhost‘                || GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON `mysql`.`xiyouzyadlog` TO ‘sync‘@‘localhost‘          |+-------------------------------------------------------------------------------------------------------------+刪除無用的授權資訊revoke SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON xiyoumainTXZYANDROID.* from [email protected];mysql> show grants for  [email protected]‘localhost‘;+-------------------------------------------------------------------------------------------------------------+| Grants for [email protected]                                                                                   |+-------------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO ‘sync‘@‘localhost‘ IDENTIFIED BY PASSWORD ‘*B805E12E6933FF815F344D9F4B0D7236B223DC86‘ || GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON `xiyouzyadlog`.* TO ‘sync‘@‘localhost‘                || GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON `mysql`.`xiyouzyadlog` TO ‘sync‘@‘localhost‘          |+-------------------------------------------------------------------------------------------------------------+3 rows in set (0.00 sec)mysql> 刪除無用的授權資訊revoke SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON mysql.xiyouzyadlog from [email protected];mysql> show grants for  [email protected]‘localhost‘;+-------------------------------------------------------------------------------------------------------------+| Grants for [email protected]                                                                                   |+-------------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO ‘sync‘@‘localhost‘ IDENTIFIED BY PASSWORD ‘*B805E12E6933FF815F344D9F4B0D7236B223DC86‘ || GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON `xiyouzyadlog`.* TO ‘sync‘@‘localhost‘                |+-------------------------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)mysql> mysql> show grants for  [email protected]‘192.168.15.%‘;+----------------------------------------------------------------------------------------------------------------+| Grants for [email protected]%                                                                                   |+----------------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO ‘sync‘@‘192.168.15.%‘ IDENTIFIED BY PASSWORD ‘*B805E12E6933FF815F344D9F4B0D7236B223DC86‘ || GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON `xiyoumainTXZYANDROID`.* TO ‘sync‘@‘192.168.15.%‘        || GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON `xiyouzyadlog`.* TO ‘sync‘@‘192.168.15.%‘                || GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON `mysql`.`xiyouzyadlog` TO ‘sync‘@‘192.168.15.%‘          |+----------------------------------------------------------------------------------------------------------------+4 rows in set (0.00 sec)mysql> 刪除無用的授權資訊revoke SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON `xiyoumainTXZYANDROID`.* from ‘sync‘@‘192.168.15.%‘revoke SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON `mysql`.`xiyouzyadlog` from  ‘sync‘@‘192.168.15.%‘最後別忘記flush privileges；好了，大功告成

本文出自 “yaoshenshen” 部落格，謝絕轉載！

一次mysql的授權故障排除