MySQL :: MySQL 8.4 Reference Manual :: 8.2.20 Account Locking (original) (raw)

8.2.20 Account Locking

MySQL supports locking and unlocking user accounts using theACCOUNT LOCK and ACCOUNT UNLOCK clauses for the CREATE USER and ALTER USER statements:

• When used with CREATE USER, these clauses specify the initial locking state for a new account. In the absence of either clause, the account is created in an unlocked state.
  If the validate_password component is enabled, creating an account without a password is not permitted, even if the account is locked. SeeSection 8.4.3, “The Password Validation Component”.
• When used with ALTER USER, these clauses specify the new locking state for an existing account. In the absence of either clause, the account locking state remains unchanged.
  ALTER USER ... UNLOCK unlocks any account named by the statement that is temporarily locked due to too many failed logins. SeeSection 8.2.15, “Password Management”.

Account locking state is recorded in theaccount_locked column of themysql.user system table. The output fromSHOW CREATE USER indicates whether an account is locked or unlocked.

If a client attempts to connect to a locked account, the attempt fails. The server increments theLocked_connects status variable that indicates the number of attempts to connect to a locked account, returns anER_ACCOUNT_HAS_BEEN_LOCKED error, and writes a message to the error log:

Access denied for user 'user_name'@'host_name'.
Account is locked.

Locking an account does not affect being able to connect using a proxy user that assumes the identity of the locked account. It also does not affect the ability to execute stored programs or views that have a DEFINER attribute naming the locked account. That is, the ability to use a proxied account or stored programs or views is not affected by locking the account.

The account-locking capability depends on the presence of theaccount_locked column in themysql.user system table. For upgrades from MySQL versions older than 5.7.6, perform the MySQL upgrade procedure to ensure that this column exists. SeeChapter 3, Upgrading MySQL. For nonupgraded installations that have no account_locked column, the server treats all accounts as unlocked, and using the ACCOUNT LOCK or ACCOUNT UNLOCK clauses produces an error.