MySQL添加用户和授权

1. root登录

mysql -uroot -p;

use mysql;

2. 创建/删除用户

# 添加用户
# "%" 允许所有ip连接, localhost 本机, ip 只允许指定ip
create user 'username'@'%' identified by 'password';

# 添加用户
insert into mysql.user(Host,User,Password) values("%","username",password("password"));

# 删除用户
drop user username@localhost;
delete from user where User="username" and Host="localhost";

3. 授权

# 查看当前授权
show grants;
 
# 查看指定host授权
show grants for 'root'@'localhost';

# 授权所有库和表
grant all privileges on *.* to 'username'@'ip' identified by 'password';

# 指定授权的库和表
grant all privileges on database.table to 'username'@'ip' identified by 'password';

# 取消授权
revoke all on *.* from 'username'@'ip';

4. 操作权限

# 权限:select查询,insert插入,delete删除,update修改,drop删除,all所有权限

# 所有权限
grant all privileges on *.* to 'username'@'%' identified by 'password' with grant option;

# 查询权限
grant select on *.* to 'username'@'ip' identified by 'password' with grant option;

# 查询插入权限
grant select,insert on *.* to 'username'@'ip' identified by 'password' with grant option;

# 取消用户查询权限
revoke select on what from 'username';

# 取消授权
revoke all on *.* from 'username'@'ip';
revoke privileges(columns) on what from 'username';

 5.  记得操作后要刷新权限

flush privileges;