mysql 用户权限管理

root用户登录
>mysql uroot -pxxxx;


切换到mysql
>use mysql;


创建数据库:
>create database db_xxx;


创建用户
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
host默认值为'%'。


修改密码：
>update user set password=password('xxxx') where user='xxx';


查看用户:
>select user,host from user;


赋予权限：
GRANT [privileges] ON db_name.tablename TO 'username'@'host';
privileges权限常见可选项：
ALL(所有),CREATE,ALTER,DROP,SELECT,DELETE,INSERT,UPDATE,EXECUTE,TRIGGER等


on *.* 所有权限
on db_name.* 某个数据库的所有权限


收回权限:
REVOKE [privileges] ON db_name.tablename FROM 'username'@'host';
例：
REVOKE INSERT ON *.* FROM 'jeffrey'@'localhost';
REVOKE ALL PRIVILEGES FROM 'jeffrey'@'localhost';


查看用户权限:
SHOW GRANTS FOR 'username'@'host';


更改后操作需执行:flush privileges 使修改生效。


参考：
https://dev.mysql.com/doc/refman/5.7/en/create-user.html
https://dev.mysql.com/doc/refman/5.7/en/grant.html