添加新用户及赋予权限

创建用户

#全网可登录
create user 'cmstest'@'%' identified by 'yTaFp=4vH@gKAp86kC';
#本地登录
create user 'cmstest'@'localhost' identified by 'yTaFp=4vH@gKAp86kC';
#指定IP登录
create user 'cmstest'@'192.168.0.33_' identified by 'yTaFp=4vH@gKAp86kC';

# 指定IP网段
create user 'cmstest'@'120.%.%.%' identified by 'yTaFp=4vH@gKAp86kC';

更改密码

本机: set password for root@localhost = password('123');
全局: set password for root@'%' = password('123');

更改密码报错

更新密码报错
mysql> set password for prometheus=password("PZobh7fK7VAn5lBa");
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

修改配置报错
mysql>  SET GLOBAL validate_password.policy=LOW;
ERROR 1193 (HY000): Unknown system variable 'policy'

解决问题
1. 在配置文件里面加: /etc/my.cnf
plugin-load-add=validate_password.so
validate-password=FORCE_PLUS_PERMANENT

2. 重启MySQL

3. 再root登录进MySQL执行:
SET GLOBAL validate_password_policy=LOW;

删除用户

drop user 用户名@'%';

用户授权

1.指定所有库，所有表，所有权限

GRANT ALL privileges ON *.* TO 'cmstest'@'%';

2.只赋予查看权限

GRANT SELECT ON *.* TO 'cmstest'@'%';
权限：
SELECT
CREATE
UPDATE
......

3.如果你想允许用户user从ip为192.168.1.4的主机连接到mysql服务器，并使用mypwd作为密码

## 在安装mysql的机器上运行：
GRANT ALL PRIVILEGES ON *.* TO 'user'@'192.168.1.3' IDENTIFIED BY 'mypwd' WITH GRANT OPTION;
    WITH GRANT OPTION: 表示该用户可以将自己拥有的权限授权给别人

更改完成之后刷新进程

FLUSH PRIVILEGES;

查看用户权限

SHOW GRANTS FOR 'shmuker'@'%';

查看MYSQL数据库中所有用户

SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
移除权限
    REVOKE SELECT ON *.* FROM 'cmstest'@'%';