mysql进阶-用户密码的设置和管理

一、修改密码

1.1 修改自己的密码

方式一：
推荐使用

alter user user() identified by '新密码';

方式二：

set password ='新密码';

演示

[root@VM-4-6-centos /]# mysql -uzhang3 -p@ZhangSan123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 351
Server version: 5.7.43 MySQL Community Server (GPL)Copyright (c) 2000, 2023, 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> alter user user() identified by '@ZhangSan123';
Query OK, 0 rows affected (0.00 sec)mysql> quit;
Bye
[root@VM-4-6-centos /]# mysql -uzhang3 -p@ZhangSan123456
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'zhang3'@'localhost' (using password: YES)
[root@VM-4-6-centos /]# mysql -uzhang3 -p@ZhangSan123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 353
Server version: 5.7.43 MySQL Community Server (GPL)Copyright (c) 2000, 2023, 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> set password ='@ZhangSan123456';
Query OK, 0 rows affected (0.00 sec)mysql> quit;
Bye
[root@VM-4-6-centos /]# mysql -uzhang3 -p@ZhangSan123
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'zhang3'@'localhost' (using password: YES)
[root@VM-4-6-centos /]# mysql -uzhang3 -p@ZhangSan123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 355
Server version: 5.7.43 MySQL Community Server (GPL)Copyright (c) 2000, 2023, 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> 

2.2 修改其他用户的密码

需要root权限，一般只有root账号可以修改其他用户的密码

方式一：

alter user 'user_name'@'host_name' identified by 'new_password';

方式二：

set password for 'user_name'@'host_name'='new_password';

示例：

mysql> alter user 'zhang3'@'localhost' identified by '@ZhangSan123';
Query OK, 0 rows affected (0.00 sec)mysql> set password for 'zhang3'@'localhost'='@ZhangSan123456';
Query OK, 0 rows affected (0.00 sec)mysql> 

二、管理密码

2.1 系统默认设置

默认是所有账号永不过期

 set persist default_password_lifetime = 0;

2.2 设置所有账号指定时间后过期

 set persist default_password_lifetime = 365;//365天后所有密码过期 

2.3 设置指定账号永不过期

alter user 'zhang3'@'localhost' PASSWORD EXPIRE NEVER;

2.4 设置指定账号密码立即过期

设置完之后，立即过期，账号zhang3能登入但是无法操作数据，必须重新设置密码

ALTER USER ‘zhang3’@'localhost' PASSWORD EXPIRE;

2.5 设置指定账号密码固定周期时间过期

每隔90天过期

ALTER USER ‘zhang3’@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;