WL#13515: Add Oracle style per user FAILED_LOGIN_ATTEMPTS/PASSWORD_LOCK_TIME counters

Affects: Server-8.0   —   Status: Complete

Introduce a per-user account temporary account lock-out on failed password
attempt, similar to
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6010.htm , the

The idea is that if the user failed to provide their password
FAILED_LOGIN_ATTEMPTS consecutive times the account gets locked for login
attempts for PASSWORD_LOCK_TIME. 

The default of these options will be off for backward compatibility. 
FR1. Add 2 new password_option to CREATE USER and ALTER USER :

* FAILED_LOGIN_ATTEMPTS : temporarily lock the user at the
specified attempt (e.g. FAILED_LOGIN_ATTEMPTS 2 will block on the 2nd failed
login attempt


FR1.1: if any is specified it will override the stored value

FR1.2: The values will be checked (and the remaining days updated) at login 

FR1.3: a server restart and the FLUSH PRIVILEGES command
will unlock all temporarily locked users and reset their failed login attempts
counts back to the FAILED_LOGIN_ATTEMPTS value.

FR1.4: the failed login attempts for each user will be reset back to
failed_login_attempts after password_lock_time days even for unsuccessful login
(and login failure counting will start afresh).

FR1.5: Any other updates to the user account will not result in any update on
the state of its logins failed temp lock.

FR1.6: Setting either FAILED_LOGIN_ATTEMPTS or PASSWORD_LOCK_TIME to zero will
turn off the tracking of failed login attempts for that user account.

FR1.7: setting either FAILED_LOGIN_ATTEMPTS or PASSWORD_LOCK_TIME will reset 
runtime lock state, i.e. it will unlock the account if it was locked due to

FR1.8: If the account is in locked state (FAILED_LOGIN_ATTEMPTS and before
PASSWORD_LOCK_TIME has expired) even a successful login will not be possible.
The administrator needs to explicitly reset the lock via ALTER USER or the ways
described in FR1.3.

FR1.9: A successful login will reset the runtime count of consecutive
unsuccessful logins back to FAILED_LOGIN_ATTEMPTS. 

FR2. Add a new composite key into mysql.user.user_attributes: 
{"Password_locking": {"failed_login_attempts": ,
"password_lock_time_days": }} to store the current values for

FR2.1. If any of the JSON key(s) is missing or has wrong or missing value the
whole fragment will be ignored and the values will be considered 0. 

only if they are non-zero.

FR4. binlog versions of CREATE USER and ALTER USER will contain
FAILED_LOGIN_ATTEMPTS and PASSWORD_LOCK_TIME if they're specified in the 
original statement.

FR5. If the login is to fail due to account being locked a specific error
message telling that will be sent to the client and logged to the error log as

FR6. The results of the login are checked right after checking the
authentication method and should not be affected by any other reasons for a
login failure (ALTER USER LOCK etc).

FR7. The tracking of the login failures will be done for the "login account",
i.e. the one that is used to check the credentials. In proxy case this means 
the user account a successful login will proxy to (CURRENT_USER) but the USER()

FR8. These new features will work for all authentication types. And will count
failed logins too. In this case a "failed login" means the authentication 
returning "false".

FR9. If you do not specify FAILED_LOGIN_ATTEMPTS it will default to 0

FR10. If you do not specify PASSWORD_LOCK_TIME it will default to 0

FR11. ALTER USER ACCOUNT UNLOCK will also reset the current password locked
state introduced by this worklog (similarly to a successful login, see FR1.3).

FR12. PASSWORD_LOCK_TIME will take a special option value called UNBOUNDED
meaning the account will be in locked state until it's unlocked through one of
the accepted ways. 

FR13. PASSWORD_LOCK_TIME and FAILED_LOGIN_ATTEMPTS can take numeric values 
between 0 and 32767. 
We add a composite key to mysql.user.user_attributes. 
We add new parser rule to parse FAILED_LOGIN_ATTEMPTS and PASSWORD_LOCK_TIME.
We make FAILED_LOGIN_ATTEMPTS and PASSWORD_LOCK_TIME non-reserved mysql specific
We add a new structure to ACL_USER that we fill in with:
2. The trackers of the lock state: the day number when the account was locked (0
if not) and the remaining password attempts (0 if account locked or not to be
locked at all).
Every time the values for FAILED_LOGIN_ATTEMPTS and PASSWORD_LOCK_TIME we reset
the trackers of the lock state according to the new value (and unlock the 

The runtime state.
In addition to the values for FAILED_LOGIN_ATTEMPTS and PASSWORD_LOCK_TIME
ACL_USER holds 2 more data members:
- the remaining failed logins before the account is locked
(remaining_login_attempts). 0 means don't check. 
- the day number when the account is locked (daynr_locked). 0 here means not

How is the check at login time done:

If the account has no password lock checking returns success regardless of the
runtime state.

if the login was successful and the account is not locked the runtime state is
reset and checking returns success.

if there are remaining login attempts they're decreased and if they are still
greater then zero checking returns success

if the remaining login attempts has just got down to 0 the account is locked:
daynr_locked is set to the present day number and processing returns failure
(stop the login).

if the account is locked (daynr_locked is non-zero) and the current day is
PASSWORD_LOCK_TIME days after the day the account was locked (daynr_locked) the
runtime state is reset (daynr_locked set to 0 and remaining_login_attempts to
FAILED_LOGIN_ATTEMPTS) and success is returned. 

Notes on upgrade/downgrade:

No changes to the USER_ATTRIBUTE columns will be needed at version upgrade.
Since the JSON composite will be missing it'll be considered set to 0/0, i.e.
backward compatible with what it used to be before this feature is introduced. 

On downgrade the older server will just ignore the values set for these two
options hence everything should work with the old server except that the
auto-locking will stop even for accounts that have it configured. Note that once
the other values in the JSON column are updated by an old server the locking
JSON composite may be removed when storing the column. Thus if you downgrade
the server, update one of the JSON values and then upgrade the server again, the
password locking settings may be lost (wherever set). 

Clarification on FR7
Having 2 active passwords doesn't have an effect on the behavior: a successful 
login (either password) resets the count. A failed login (none of the passwords) 
counts as a failed login.

Example syntax: 


Clarification on FR9/FR10

Obviously the default 0 applies only to CREATE USER. For ALTER USER if you do 
not specify either of the new clauses the current value will be preserved.