RDS - 密码错误后锁定账户

如果希望密码错误超过一定次数后，锁定账户，避免被暴力破解。MySQL 8 以及 Oracle 可以通过数据库自带的方式进行设置， 而 PG 则需要自己对日志中的错误进行解析。

 MySQL 8

可以使用主用户对业务用户进行设置。

### master user
MySQL [(none)]> ALTER USER user111 FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 1;
Query OK, 0 rows affected (0.00 sec)

这样当3次登陆失败之后，会锁定一天，还可以将 1 修改为 UNBOUNDED。这样就不会自动解锁。

[root@jump01 ~]# mysql -uuser111 -p'123'  -h XXXXXXXX
ERROR 1045 (28000): Access denied for user 'user111'@'XXXXXXXX' (using password: YES)
[root@jump01 ~]# mysql -uuser111 -p'123'  -h XXXXXXXX
ERROR 1045 (28000): Access denied for user 'user111'@'XXXXXXXX' (using password: YES)
[root@jump01 ~]# mysql -uuser111 -p'12345678'  -h XXXXXXXX
ERROR 3955 (HY000): Access denied for user 'user111'@'XXXXXXXX'. Account is blocked for 1 day(s) (1 day(s) remaining) due to 3 consecutive failed logins.

之后，可以进行人为解锁。

### master user
MySQL [(none)]> flush privileges;

如果使用正确密码时，可以进入

[root@jump01 ~]# mysql -uuser111 -p'12345678'  -h XXXXXXXX
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 138292
Server version: 8.0.25 Source distribution

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> ^DBye

Oracle

Oracle 中需要用到 profile

CREATE PROFILE user111 LIMIT
   FAILED_LOGIN_ATTEMPTS 5
   PASSWORD_LIFE_TIME 60
   PASSWORD_REUSE_TIME 60
   PASSWORD_REUSE_MAX 5
   PASSWORD_VERIFY_FUNCTION verify_function
   PASSWORD_LOCK_TIME 1/1440
   PASSWORD_GRACE_TIME 10;
   
ALTER PROFILE user111 LIMIT FAILED_LOGIN_ATTEMPTS 1 PASSWORD_LOCK_TIME 1;

alter user user111 profile user111;

进行验证

# 密码不对
[oracle@jump01 ~]$ sqlplus user111/123@XXXXXXXX:1521/orcl

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Feb 16 00:33:06 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied

# 再次登录锁定
[oracle@jump01 ~]$ sqlplus user111/12345678@XXXXXXXX:1521/orcl

ORA-28000: The account is locked.

之后进行人为解锁

# 解锁
SQL> alter user user111 account unlock;

User altered.

再次登录

# 尝试登录
[oracle@jump01 ~]$ sqlplus user111/12345678@XXXXXXXX:1521/orcl

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Feb 16 00:22:24 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Tue Feb 15 2022 23:36:17 +00:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.13.0.0.0

SQL>

PostgreSQL

数据库中没有相关的功能，可以通过一些分析日志的方式自行实现该功能。

首先将日志读取到 数据流中，之后从数据流中读取，过滤出这种错误

FATAL:  password authentication failed for user "dbadmin"

之后进行实时分析，当超过一定次数后，可以将用户设置为 nologin 的。

alter user user111 nologin;

这只能通过人为解锁，或自行实现。

alter user user111 login;