1、配置密码最长有效期为90天。
查看:show variables like 'default_password_lifetime';
mysql> show variables like 'default_password_lifetime';
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| default_password_lifetime | 60 |
+---------------------------+-------+
1 row in set (0.01 sec)
mysql>
修改:set global default_password_lifetime=90; 设置90天过期
设置系统参数default_password_lifetime作用于所有的用户账户
设置MySQL用户密码过期策略
default_password_lifetime=180 设置180天过期
default_password_lifetime=0 设置密码不过期
如果为每个用户设置了密码过期策略,则会覆盖上述系统参数
ALTER USER 'mytest'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
ALTER USER 'mytest'@'localhost' PASSWORD EXPIRE NEVER; 密码不过期
ALTER USER 'mytest'@'localhost' PASSWORD EXPIRE DEFAULT; 默认过期策略
手动强制设置某个用户密码过期
ALTER USER 'mytest'@'localhost' PASSWORD EXPIRE;
2、密码安全配置
配置密码错误n次后锁定账户30分钟;建议设置登录无操作30分钟后自动退出;登陆失败
安装安全插件:
INSTALL PLUGIN CONNECTION_CONTROL SONAME 'connection_control.so';
INSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS SONAME 'connection_control.so';
查看:
show variables like "connection_control%";
+-------------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------------+------------+
| connection_control_failed_connections_threshold | 3 |
| connection_control_max_connection_delay | 2147483647 |
| connection_control_min_connection_delay | 1000 |
+-------------------------------------------------+------------+
参数含义:
connection_control_failed_connections_threshold:单个用户登录失败(由于密码错误引起)次数上限,默认3次
connection_control_max_connection_delay:失败上限之后再次尝试登录前最小等待时间,单位ms
connection_control_min_connection_delay:失败上限之后再次尝试登录前最小等待时间,默认1秒(1000ms)
上述3个参数均可以利用 set global 的方式在线修改。
mysql> set global connection_control_failed_connections_threshold=5;
mysql> set global connection_control_max_connection_delay=214748364;
mysql> set global connection_control_min_connection_delay=2000;
mysql> show variables like "connection_control%";
+-------------------------------------------------+-----------+
| Variable_name | Value |
+-------------------------------------------------+-----------+
| connection_control_failed_connections_threshold | 5 |
| connection_control_max_connection_delay | 214748364 |
| connection_control_min_connection_delay | 2000 |
+-------------------------------------------------+-----------+
3 rows in set (0.01 sec)
mysql>
密码复杂度,安装插件:install plugin validate_password soname 'validate_password.so';
查看已经安装的插件:
mysql> SELECT * from mysql.`plugin`;
+------------------------------------------+-----------------------+
| name | dl |
+------------------------------------------+-----------------------+
| CONNECTION_CONTROL | connection_