MySQL修改密码安全策略

MySQL 跳过密码验证，并修改密码安全策略

假如忘记登陆密码或者跳过MySQL密码策略验证时可以使用skip-grant-tables跳过数据库权限验证，然后修改密码并且不受策略限制

1.编辑/etc/my.cnf文件

• 添加 skip-grant-tables
  跳过数据库权限验证

2.重启MySQL修改密码

直接登录到MySQL

[root@xytest ~]# mysql -u root -p
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.38 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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.

mysql> update user set password=password(“新密码”) where user=”用户名”;

执行后报错

ERROR 1054(42S22) Unknown column ‘password’ in ‘field list’

错误的原因是 5.7版本下的mysql数据库下已经没有 password 这个字段了，password 字段改成了 authentication_string

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from user;

+-----------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
| Host      | User          | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin                | authentication_string                     | password_expired | password_last_changed | password_lifetime | account_locked |
+-----------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
| %         | root          | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          |            |             |              |             0 |           0 |               0 |                    0 | mysql_native_password | *A4B6157319038724E3560894F7F932C8886EBFCF | N                | 2022-06-20 13:29:42   |              NULL | N              |
| localhost | mysql.session | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | Y          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            | N                      |          |            |             |              |             0 |           0 |               0 |                    0 | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | N                | 2022-06-20 13:24:46   |              NULL | Y              |
| localhost | mysql.sys     | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            | N                      |          |            |             |              |             0 |           0 |               0 |                    0 | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | N                | 2022-06-20 13:24:46   |              NULL | Y              |
| %         | entegor       | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            | N                      |          |            |             |              |             0 |           0 |               0 |                    0 | mysql_native_password | *BE41F185DAC3CBC0D2D11115C7CC8C94C14E9E3B | N                | 2022-06-20 13:30:37   |              NULL | N              |
+-----------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
4 rows in set (0.00 sec)

mysql> update mysql.user set authentication_string=password('pasword') where user='user';

修改完成后将 /etc/my.cnf 中的 skip-grant-tables 注释或者删除，重启MySQL服务即可

注意：如果使用 skip-grant-tables 跳过密码验证，修改密码完成之后发现新的密码仍然不能登陆，可以参考https://blog.csdn.net/D1179869625/article/details/122619177

3.查看密码安全策略

MySQL密码策略默认是通过validate_password_policy属性进行设置的。我们可以通过下面语句进行查询：

mysql> select @@validate_password_policy;
+----------------------------+
| @@validate_password_policy |
+----------------------------+
| MEDIUM                     |
+----------------------------+
1 row in set (0.00 sec)

策略为0时，密码强度最低，只要长度满足就可以了。策略为1时，也就是MEDIUM，不仅需要长度满足，还有数字，大小写，特殊字符要求。因此我们可以通过设置该属性来该变策略。

mysql> set global validate_password_policy=0;

以上设置只能临时生效，重启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      |
+--------------------------------------+--------+
7 rows in set (0.06 sec)

参数	说明
validate_password_check_user_name	用户名检测，检测是否重名（不常用）
validate_password_dictionary_file	字典文件，启动后必须要满组所使用字典的规则才能满足密码的条件（不常用）
validate_password_length	密码的长度
validate_password_mixed_case_count	密码中英文字符大小写的个数，当密码策略是MEDIUM或以上时生效。
validate_password_number_count	密码中至少含有的数字个数，当密码策略是MEDIUM或以上时生效。
validate_password_policy	这个参数可以设为0、1、2，分别代表从低到高的密码强度，此参数的默认值为1，如果想将密码强度改若，则更改此参数为0。
validate_password_special_char_count	密码中非英文数字等特殊字符的个数，当密码策略是MEDIUM或以上时生效。

所以，我们不仅可以通过设置 validate_password_policy 属性来修改密码强度，还可以通过修改其它属性。只是通过 validate_password_policy 比较方便。

4. 修改/etc/my.cnf文件永久有效

在 /etc/my.cnf 配置文件中添加如下配置

validate_password_policy=0
策略为0时，密码强度最低，只要长度满足就可以了

validate_password_length=0
官网描述：validate_password_length 最小值是几个其他相关系统变量的函数 。 该值不能设置为小于此表达式的值，所以如果按本次测试将 `validate_password_length` 修改为 0 ，那么在设置密码长度时最少也要有四个字符的长度

官网参考：https://dev.mysql.com/doc/refman/5.7/en/validate-password-options-variables.html

修改完成后重启MySQL服务即可

systemctl restart mysqld