mysql5.7与8.0密码加密方式_mysql8.0密码加密方式

mysql5.7与8.0密码加密方式

1.mysql5.7默认是方式是mysql_native_password;
2.mysql8.0默认是caching_sha2_password
注意：在8.0+中使用指定使用caching_sha2_password创建用户账号密码时，有些客户端暂时不支持，从而导致连接认证失败。所以，有时需要指定mysql_native_password创建。

## 查看密码加密方式
show variables like '%password%';
+----------------------------------------------+-----------------+
| Variable_name                                | Value           |
+----------------------------------------------+-----------------+
| caching_sha2_password_auto_generate_rsa_keys | ON              |
| caching_sha2_password_private_key_path       | private_key.pem |
| caching_sha2_password_public_key_path        | public_key.pem  |
| default_password_lifetime                    | 0               |
| disconnect_on_expired_password               | ON              |
| generated_random_password_length             | 20              |
| mysql_native_password_proxy_users            | OFF             |
| password_history                             | 0               |
| password_require_current                     | OFF             |
| password_reuse_interval                      | 0               |
| report_password                              |                 |
| sha256_password_auto_generate_rsa_keys       | ON              |
| sha256_password_private_key_path             | private_key.pem |
| sha256_password_proxy_users                  | OFF             |
| sha256_password_public_key_path              | public_key.pem  |
+----------------------------------------------+-----------------+
15 rows in set (0.00 sec)


## 修改用户密码安全加密方式
alter user 'zhangsan'@'localhost' identified with mysql_native_password by '123456';

## 8.0下创建用户通过mysql_native_password加密的方式
create user 'zhangsan'@'localhost' identified with mysql_native_password by '123456';

## 查看加密的方式以及是否有设置过期策略
select host,user,password_expired,plugin from mysql.user where user in ('root','zhangsan');
+-----------+----------+------------------+-----------------------+
| host      | user     | password_expired | plugin                |
+-----------+----------+------------------+-----------------------+
| localhost | root     | N                | caching_sha2_password |
| localhost | zhangsan | N                | mysql_native_password |
+-----------+----------+------------------+-----------------------+
2 rows in set (0.00 sec)

## 刷新权限配置
flush privileges;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

## 5.7版本
mysql> select @@version;
+------------+
| @@version  |
+------------+
| 5.7.23-log |
+------------+
1 row in set (0.00 sec)

##检查密码是否正确
mysql> select password('123456');
+-------------------------------------------+
| password('123456')                        |
+-------------------------------------------+
| *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

## 8.0版本
mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.18    |
+-----------+
1 row in set (0.00 sec)

## 8.0+版本后password()函数不可用

mysql> select password('123456');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('123456')' at line 1
1
2
3
4
5
6
7
8
9
10
11
12
13

密码策略相关参数：

• validate_password_dictionary_file：密码策略文件，当策略为strong时需要
• validate_password_length：密码至少长度
• validate_password_mixed_case_count：至少需要大小写字符长度个数
• validate_password_number_count：数字至少个数
• validate_password_special_char_count：特殊字符个数要求
• validate_password_policy：密码策略，默认是MEDIUM（要求：长度=2*大小写+特殊字符+数字）

策略选项：

• 0/LOW:只验证长度
• 1/MEDIUM:验证长度、数字、大小写、特殊字符
• 2/STRONG:验证长度、数字、大小写、特殊字符、字典文件

##查看安全策略
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 (0.00 sec)

##查询结果为空，则说明还没有安装相关的插件
show plugin;

##查看插件的路径
show variables like 'plugin_dir%';

##安装插件
INSTALL PLUGIN validate_password SONAME 'validate_password.so';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23