MySQL用户权限管理

- 查看权限
select user,host from mysql.user;
select db,user,host from mysql.db;
show grants for {user}@{IP};

- 向指定用户（及IP）授权:
grant all privileges on {db}.* to {user}@{IP} identified by '{password}';
grant select         on {db}.* to {user}@{IP} identified by '{password}';
grant select，insert，update，delete on ...
flush privileges;

用户不存在时，包含创建用户(GRANT USAGE...)

- 向指定用户撤销权限：
revoke all privileges on {db}.* from {user}@{IP} identified by '{password}';
revoke select         on {db}.* from {user}@{IP};
flush privileges;

撤销DB访问权限，不能禁止IP访问, 因为并未收回USAGE权限，也未删除用户。

- 禁止IP访问（删除用户@IP）：
delete from mysql.user where Host='{IP}' and User='{user}';

flush privileges;

Q: root@localhost无法连接mysql服务

# mysql -uroot -ppassword
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

1. 使用有grant权限用户登录，并为root从本机授权
# mysql -h127.0.0.1 -uroot -ppassword
> GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' identified by 'password' WITH GRANT OPTION;

Q: root@localhost无法为其他DB用户授权
mysql> grant all privileges on db.* to user@ip identified by 'password';
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

1. 查看root@localhost：无grant权限
mysql> show grants for root@localhost;
+----------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                            |
+----------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*96184BCCD3CEA5648C9A26E4753DD258B207478F' |

2. 删除root用户并重新加载权限
mysql> delete from mysql.user where Host='localhost' and User='root';
mysql> flush privileges;

3. 使用有grant权限用户登录，重新创建root@localhost并授grant权
# mysql -h127.0.0.1 -uroot -ppassword
> GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' identified by 'password' WITH GRANT OPTION;