MySQL数据库密码策略以及修改密码方式
MySQL密码策略介绍
#mysql数据库分为高(2)、中(1)、低(0)。三种密码策略
#低级密码策略,密码强度一般可以为纯数字
#中级密码策略,密码强度一般需要字母加数字
#高级密码策略,密码强度一般需要字母大小写加数字加特殊字符
#温馨提醒:在测试环境中可以使用低级密码策略来进行使用,在实际生产环境中建议使用密码策略稍微较高
MySQL安装密码生成
#MySQL
通过yum包管理方式安装MySQL数据库,临时密码会生成在/var/log/mysqld.log
通过二进制或源码编译安装,临时密码会生成在自定义安装的日志文件中
#mariadb
安装mariadb数据库默认是可以免密登录数据库
#使用临时密码登录数据库,一定对数据库密码进行修改,否则重启后在使用临时密码登录就会报错
MySQL查看密码策略
#使用 show variables like 'validate_password%'; 查看当前数据库密码策略
# validate_password_policy | STRONG | 显示当前本机密码策略为高策略
mysql> show variables like 'validate_password%';
+--------------------------------------+--------+
| Variable_name | Value |
+--------------------------------------+--------+
| validate_password_check_user_name | OFF |
| validate_password_dictionary_file | |
| validate_password_length | 6 |
| validate_password_mixed_case_count | 1 |
| validate_password_number_count | 1 |
| validate_password_policy | STRONG |
| validate_password_special_char_count | 1 |
+--------------------------------------+--------+
MySQL设置密码策略
#MySQL密码策略分为0(低),1(中),2(高)
#设置低级密码策略
set global validate_password_policy=0;
mysql> show variables like 'validate_password%';
+--------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------+-------+
| validate_password_check_user_name | OFF |
| validate_password_dictionary_file | |
| validate_password_length | 6 |
| validate_password_mixed_case_count | 1 |
| validate_password_number_count | 1 |
| validate_password_policy | LOW |
| validate_password_special_char_count | 1 |
+--------------------------------------+-------+
#设置中级密码策略
mysql> set global validate_password_policy=1;
Query OK, 0 rows affected (0.00 sec)mysql> show variables like 'validate_password%';
+--------------------------------------+--------+
| Variable_name | Value |
+--------------------------------------+--------+
| validate_password_check_user_name | OFF |
| validate_password_dictionary_file | |
| validate_password_length | 6 |
| validate_password_mixed_case_count | 1 |
| validate_password_number_count | 1 |
| validate_password_policy | MEDIUM |
| validate_password_special_char_count | 1 |
+--------------------------------------+--------+
#设置高级密码策略
mysql> set global validate_password_policy=2;
Query OK, 0 rows affected (0.00 sec)mysql> show variables like 'validate_password%';
+--------------------------------------+--------+
| Variable_name | Value |
+--------------------------------------+--------+
| validate_password_check_user_name | OFF |
| validate_password_dictionary_file | |
| validate_password_length | 6 |
| validate_password_mixed_case_count | 1 |
| validate_password_number_count | 1 |
| validate_password_policy | STRONG |
| validate_password_special_char_count | 1 |
+--------------------------------------+--------+
MySQL修改密码长度
#数字6为密码长度,该长度可以自身需求自定义
mysql> set global validate_password_length=6;
Query OK, 0 rows affected (0.00 sec)
数据库修改密码方式大全
注意!
新版mysql中用户密码可以保存在mysql.user表的authentication_string字段中
如果mysql.user表的authentication_string和password字段都保存密码,authentication_string优先生效
#方法1
#MySQL 8 版本不支持此方法
SET PASSWORD FOR 'user'@'host' = PASSWORD('1');#范例
mysql> select version();
+------------+
| version() |
+------------+
| 5.7.29-log |
+------------+
1 row in set (0.00 sec)mysql> select user,host,authentication_string from mysql.user;
+---------------+-------------+-------------------------------------------+
| user | host | authentication_string |
+---------------+-------------+-------------------------------------------+
| root | localhost | |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| demo | 192.168.3.% | *E6CC90B878B948C35E92B003C792C46C58C4AF40 |
+---------------+-------------+-------------------------------------------+
4 rows in set (0.00 sec)mysql> SET PASSWORD FOR 'demo'@'192.168.3.%' = PASSWORD('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)#登录测试
[root@Rocky8 ~]# mysql -udemo -h 192.168.3.19 -p123456#MySQL 8 版本修改密码
alter user test@'%' IDENTIFIED BY '123456';#方法2
UPDATE mysql.user SET password=PASSWORD('password') WHERE demo;#mariadb 10.3
update mysql.user set authentication_string=password('1') where user='test';#此方法为刷新数据库表的指令
#此方法需要执行下面指令才能生效:
FLUSH PRIVILEGES;