Mysql8 创建用户、授权

#创建用户 
    #用户名：admin 
    #从61.186.30.127可以连接 %代表所有主机
    #身份认证 mysql_native_password
    #密码 @ab168168
create user if not exists 'admin'@'61.186.30.127' identified with mysql_native_password by '@ab168168';
#刷新指令
FLUSH PRIVILEGES;

#授权权限
  #授权数据库mysql下的所有表查权限给mysql

  #其他权限可以用逗号隔开 insert,delete,update
grant select on mysql.* to 'admin'@'61.186.30.127';

#撤销权限
revoke select on mysql.* from 'admin'@'61.186.30.127';

#授权指定表权限
grant select on mysql.db to 'admin'@'61.186.30.127';
#授权指定表字段权限
grant select(Host,Db,User) on mysql.db to 'admin'@'61.186.30.127';

#拥有grant权限才能授权其他用户权限
grant grant option on *.* to 'admin'@'61.186.30.127'; 

#设置过期用户
alter user  'admin'@'61.186.30.127' password expire;
#设置用户一天更改一次密码
alter user  'admin'@'61.186.30.127' password expire INTERVAL 1 day;
#锁定用户
alter user 'admin'@'61.186.30.127' account lock;
#解锁用户
alter user 'admin'@'61.186.30.127' account unlock;
#更改密码
alter user 'admin'@'61.186.30.127' identified with mysql_native_password by '@ab168168';
#创建角色
create role 'jr_read_only','jr_writes','jr_developer';
#给角色分配权限
grant select on mysql.user to 'jr_read_only';
#给用户分配角色
grant 'jr_read_only' to 'admin'@'61.186.30.127';