Support ACCOUNT LOCK/UNLOCK for TiDB

[CbcWestwolf]

Motivation

In MySQL, you can lock or unlock a user/role by CREATE USER or ALTER USER. For compatibility, TiDB should support this feature.

Feature Acquirement

According to the description in MySQL account locking, the feature is mainly related to CREATE USER and ALTER USER statements in TiDB.

• 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.
• 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.
• Account locking state is recorded in the account_locked column of the mysql.user system table. The output from SHOW CREATE USER indicates whether an account is locked or unlocked.
• If a client attempts to connect to a locked account, the attempt fails, and returns an ErrAccountHasBeenLocked error.
• The ability to use views is not affected by locking the account.
• After a role is created, the role is locked by default and cannot log in to TiDB; after unlocking through ALTER USER, the role can also log in normally.

Note that these behaviors are different from MySQL8:

• TiDB doesn't support the validate_password component (at least not in v6.1.0)
• TiDB doesn't support the proxy user or stored programs (at least not in v6.1.0)
• TiDB would not support temporary lock when encountering too many consecutive login failures.

Background

Related Data Structures

For accelerating the reading of mysql.user, TiDB has a type MySQLPrivilege struct to cache the users' privilege:

type MySQLPrivilege struct {
	User          []UserRecord
	UserMap       map[string][]UserRecord // Accelerate User searching
	Global        map[string][]globalPrivRecord
	Dynamic       map[string][]dynamicPrivRecord
	DB            []dbRecord
	DBMap         map[string][]dbRecord // Accelerate DB searching
	TablesPriv    []tablesPrivRecord
	TablesPrivMap map[string][]tablesPrivRecord // Accelerate TablesPriv searching
	ColumnsPriv   []columnsPrivRecord
	DefaultRoles  []defaultRoleRecord
	RoleGraph     map[string]roleGraphEdgesTable
}

The state of the account lock can be accessed by the User field, since UserRecord contains a field AccountLocked bool:

type UserRecord struct {
	baseRecord

	AuthenticationString string
	Privileges           mysql.PrivilegeType
	AccountLocked        bool // A role record when this field is true
	AuthPlugin           string
}

Update of Privilege

Each time to run a command related to change privilege, such as CREATE USER, ALTER USER and FLUSH, the function NotifyUpdatePrivilege would be called to:

1. update the local cache from mysql.user, and
2. notify the other instances to update by etcd

Design

Since #9377 has

• imported account_locked column to the mysql.user table, which appeared first in an early version v3.0.5, we don't have to modify the definition of mysql.user table.
• imported AccountLocked field into type UserRecord struct, which is included in MySQLPrivilege, we don't have to modify thie definition of priviledge.

And the check of lock is placed in func (p *UserPrivileges) ConnectionVerification. The check of lock happens after the check of password.

Testing

• The unit test is in the func (cli *testServerClient) runTestAccountLock
• The related mysql-test is also ported

[CbcWestwolf]

/assign