背景需求:实验室MySQL数据库登陆之前是通过root账号登陆,随着数据库表格不断扩大,考虑到数据安全,需要针对不同用户开放不同的数据库并赋予权限。
1.创建用户:
create user 'test'@'IP' identified by '123456';
but,出现报错信息:
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
原因是:为了加强安全性,MySQL5.7为root用户随机生成了一个密码。我的解决方法是先用这个随机生成的密码创建用户,之后再将密码修改为简单的格式。
找到原始生成的随机密码:
grep "password" /var/log/mysqld.log| head
# 随机创建的临时密码位于文件首行:
# 2017-06-19T02:18:57.195429Z 1 [Note] A temporary password is generated for root@localhost: 临时密码XXX
插一句:MySQL对于密码设置有基础规定,查看密码规范的命令
# MySQL下输入
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 |
+--------------------------------------+--------+
密码基本要求是8位字符,包含大小写和特殊字符,之后的密码应该按照这个格式修改。
创建用户的正确格式:
create user 'test'@'数据库所在IP' identified by '找到的随机密码';
2.创建数据库并将数据库操作权限赋予新用户
create database testdb DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
# 用户与数据库绑定
grant all privileges on `testdb`.* to 'test'@'IP';
# 刷新权限
flush privileges;
3.用户账户登陆数据库
mysql -u test -h IP -p
# 输入初始设置的密码
4.用户修改登陆密码
# 新设置的密码应该遵循密码设置的基本要求
set password for test@IP=password('sTu-12345');
5.用新密码登陆成功
mysql -u test -h IP -p 新密码
# 登陆成功
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 31178
Server version: 5.7.18 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.