current user can alter its own password option without 'CREATE USER' privilege

[D3Hunter]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

on mysql

mysql> create user test identified by '123456';
Query OK, 0 rows affected (0.07 sec)

then login with test

mysql> alter user test password expire never;
ERROR 1227 (42000): Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation

on tidb

mysql> create user test identified by '123456';
Query OK, 0 rows affected (0.04 sec)

then login with test

mysql> alter user test password expire never;
Query OK, 0 rows affected (0.04 sec)

quote from mysql doc https://dev.mysql.com/doc/refman/8.4/en/alter-user.html

In most cases, ALTER USER requires the global CREATE USER privilege, or the UPDATE privilege for the mysql system schema. The exceptions are:

but in our impl, the current user can alway alter params related to itself

tidb/pkg/executor/simple.go

Lines 1758 to 1786 in 811be5a

	if spec.User.CurrentUser || ((user != nil) && (user.Username == spec.User.Username) && (user.AuthHostname == spec.User.Hostname)) {
	spec.User.Username = user.Username
	spec.User.Hostname = user.AuthHostname
	} else {
	// The user executing the query (user) does not match the user specified (spec.User)
	// The MySQL manual states:
	// "In most cases, ALTER USER requires the global CREATE USER privilege, or the UPDATE privilege for the mysql system schema"
	//
	// This is true unless the user being modified has the SYSTEM_USER dynamic privilege.
	// See: https://mysqlserverteam.com/the-system_user-dynamic-privilege/
	//
	// In the current implementation of DYNAMIC privileges, SUPER can be used as a substitute for any DYNAMIC privilege
	// (unless SEM is enabled; in which case RESTRICTED_* privileges will not use SUPER as a substitute). This is intentional
	// because visitInfo can not accept OR conditions for permissions and in many cases MySQL permits SUPER instead.
	// Thus, any user with SUPER can effectively ALTER/DROP a SYSTEM_USER, and
	// any user with only CREATE USER can not modify the properties of users with SUPER privilege.
	// We extend this in TiDB with SEM, where SUPER users can not modify users with RESTRICTED_USER_ADMIN.
	// For simplicity: RESTRICTED_USER_ADMIN also counts for SYSTEM_USER here.
	if !(hasCreateUserPriv || hasSystemSchemaPriv) {
	return plannererrors.ErrSpecificAccessDenied.GenWithStackByArgs("CREATE USER")
	}
	if !(hasSystemUserPriv || hasRestrictedUserPriv) && checker.RequestDynamicVerificationWithUser(ctx, "SYSTEM_USER", false, spec.User) {
	return plannererrors.ErrSpecificAccessDenied.GenWithStackByArgs("SYSTEM_USER or SUPER")
	}
	if sem.IsEnabled() && !hasRestrictedUserPriv && checker.RequestDynamicVerificationWithUser(ctx, "RESTRICTED_USER_ADMIN", false, spec.User) {
	return plannererrors.ErrSpecificAccessDenied.GenWithStackByArgs("RESTRICTED_USER_ADMIN")
	}

2. What did you expect to see? (Required)

err

3. What did you see instead (Required)

ok

4. What is your TiDB version? (Required)

master