创建用户
create user '用户'@'地址(%表示不限ip)' identified by '密码';
例;create user 'zzz'@'%' identified by '123';
报错:1819 - Your password does not satisfy the current policy requirements
说我的秘密不符合当前的策略要求
查看当前密码策略:SHOW VARIABLES LIKE 'validate_password%';
mysql> create user 'zzz'@' %' identified by '123';
1819 - Your password does not satisfy the current policy requirements
mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+--------+
| Variable_name | Value |
+--------------------------------------+--------+
| validate_password_check_user_name | OFF |
| validate_password_dictionary_file | |
| validate_password_length | 8 |
| validate_password_mixed_case_count | 1 |
| validate_password_number_count | 1 |
| validate_password_policy | MEDIUM |
| validate_password_special_char_count | 1 |
+--------------------------------------+--------+
7 rows in set
mysql 密码策略相关参数;
1)validate_password_length 固定密码的总长度;
2)validate_password_dictionary_file 指定密码验证的文件路径;
3)validate_password_mixed_case_count 整个密码中至少要包含大/小写字母的总个数;
4)validate_password_number_count 整个密码中至少要包含阿拉伯数字的个数;
5)validate_password_policy 指定密码的强度验证等级,默认为 MEDIUM;
关于 validate_password_policy 的取值:
0/LOW:只验证长度;
1/MEDIUM:验证长度、数字、大小写、特殊字符;
2/STRONG:验证长度、数字、大小写、特殊字符、字典文件;
6)validate_password_special_char_count 整个密码中至少要包含特殊字符的个数;
两个选择:修改策略或修改密码来符合策略。
赋权
grant 权限1,权限2...权限n on 数据库名称.表名称 to 用户名@用户地址 identified by ‘连接密码’ with grant option;
with grant option允许用户将自己的权限授予其他用户
例子:
grant all privileges on *.* to 'zzz'@'%' identified by 'Asdf@123' with grant option;
//*.* 前面一个‘*’表示库,后面一个‘*’表示表名,此处表示所有库表
当我授权时报错 1045 - Access denied for user 'root'@'%' (using password: YES)
这个错误一般出现在连接数据库时密码错误。此处报错的原因:因为我使用的Navicat远程连接的数据库,默认只有root用户localhost连接才有授权能力,其他IP赋权默认不允许的,可能为了安全性考虑。
select user,host,grant_priv from user;
+---------------+-----------+------------+
| user | host | grant_priv |
+---------------+-----------+------------+
| root | localhost | Y |
| mysql.session | localhost | N |
| mysql.sys | localhost | N |
| root | % | N |//我使用的root远程登录没有授权权限
| zzz | % | N |
|
+---------------+-----------+------------+
6 rows in set
修改root用户%连接的grant_priv为Y
UPDATE user set Grant_priv='N' where user='root' and `Host`='%'
修改后重新连接,授权成功后刷新 flush privileges;