6.2.11 Account Categories

A user with the SYSTEM_USER privilege is a system user.

A user without the SYSTEM_USER privilege is a regular user.

A system user can modify both system and regular accounts. That is, a user who has the appropriate privileges to perform a given operation on regular accounts is enabled by possession of SYSTEM_USER to also perform the operation on system accounts. A system account can be modified only by system users with appropriate privileges, not by regular users.

A regular user with appropriate privileges can modify regular accounts, but not system accounts. A regular account can be modified by both system and regular users with appropriate privileges.

Account manipulation.

Account manipulation includes creating and dropping accounts, granting and revoking privileges, changing account authentication characteristics such as credentials or authentication plugin, and changing other account characteristics such as password expiration policy.

The SYSTEM_USER privilege is required to manipulate system accounts using account-management statements such as CREATE USER and GRANT. To prevent an account from modifying system accounts this way, make it a regular account by not granting it the SYSTEM_USER privilege. (However, to fully protect system accounts against regular accounts, you must also withhold modification privileges for the mysql system schema from regular accounts. See Protecting System Accounts Against Manipulation by Regular Accounts.)

Killing current sessions and statements executing within them.

To kill a session or statement that is executing with the SYSTEM_USER privilege, your own session must have the SYSTEM_USER privilege, in addition to any other required privilege (CONNECTION_ADMIN or SUPER).

Prior to MySQL 8.0.16, CONNECTION_ADMIN or SUPER are sufficient to kill any session or statement.

Setting the DEFINER for stored objects.

To set the DEFINER attribute for a stored object to an account that has the SYSTEM_USER privilege, you must have the SYSTEM_USER privilege, in addition to any other required privilege (SET_USER_ID or SUPER).

Prior to MySQL 8.0.16, SET_USER_ID or SUPER are sufficient to specify any DEFINER value for stored objects.

Specifying mandatory roles.

A role that has the SYSTEM_USER privilege cannot be listed in the value of the mandatory_roles system variable.

Prior to MySQL 8.0.16, any role can be listed in mandatory_roles.

A session that possesses the SYSTEM_USER privilege is a system session.

A session that does not possess the SYSTEM_USER privilege is a regular session.

If a regular session activates a role with the SYSTEM_USER privilege, the session becomes a system session.

If a system session deactivates a role with the SYSTEM_USER privilege, the session becomes a regular session, unless some other role with the SYSTEM_USER privilege remains active.

If the SYSTEM_USER privilege is granted to or revoked from an account, existing sessions for the account do not change between regular and system sessions. The grant or revoke operation affects only sessions for subsequent connections by the account.

Statements executed by a stored object invoked within a session execute with the system or regular status of the parent session, even if the object DEFINER attribute names a system account.

By using account-management statements such as CREATE USER and GRANT. This is the preferred method.

By direct grant-table modification using statements such as INSERT and UPDATE. This method is discouraged but possible for users with the appropriate privileges on the mysql system schema that contains the grant tables.

The SYSTEM_USER privilege is required to manipulate system accounts using account-management statements. To prevent an account from modifying system accounts this way, make it a regular account by not granting SYSTEM_USER to it. This includes not granting SYSTEM_USER to any roles granted to the account.

Privileges for the mysql schema enable manipulation of system accounts through direct modification of the grant tables, even if the modifying account is a regular account. To restrict unauthorized direct modification of system accounts by a regular account, do not grant modification privileges for the mysql schema to the account (or any roles granted to the account). If a regular account must have global privileges that apply to all schemas, mysql schema modifications can be prevented using privilege restrictions imposed using partial revokes. See Section 6.2.12, “Privilege Restriction Using Partial Revokes”.