mysql添加用户及授权

1、本地环境

$ mysql -V
mysql  Ver 14.14 Distrib 5.7.19, for macos10.12 (x86_64) using  EditLine wrapper

2、用户登录Mysql

$ mysql -uroot -proot

3、添加用户

#只允许指定ip连接
create user 'userName'@'localhost' identified by 'password';
#允许所有ip连接（用通配符%表示）
create user 'userName'@'%' identified by 'password';

4、为新用户授权

#基本格式如下
grant all privileges on dbName.tableName to 'userName'@'ip' identified by 'password' ;
#允许访问所有数据库下的所有表
grant all privileges on *.* to 'userName'@'ip' identified by 'password' ;
#允许访问指定数据库下的所有表
grant all privileges on test to 'userName'@'ip' identified by 'password' ;
#指定数据库下的指定表
grant all privileges on test.test to 'userName'@'ip' identified by 'password' ;

5、设置用户操作权限

#设置用户拥有所有权限也就是管理员
grant all privileges on *.* to 'userName'@'ip' identified by 'password' WITH GRANT OPTION;
#拥有查询权限
grant select on *.* to 'userName'@'ip' identified by 'password' WITH GRANT OPTION;
#其它操作权限说明,select查询 insert插入 delete删除 update修改
#设置用户拥有查询插入的权限
grant select,insert on *.* to 'userName'@'ip' identified by 'password' WITH GRANT OPTION;
#取消用户查询的查询权限
REVOKE insert ON what FROM 'userName';

6、删除用户

DROP USER username@localhost;

7、修改后刷新权限

FLUSH PRIVILEGES;