MySQL 8 - Access denied when dropping procedures

On our MySQL 8.0.23 server, when a user tries to drop a procedure created by another user they get the following error:

Error Code: 1227
Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation

The user has the DROP, CREATE ROUTINE, and ALTER ROUTINE privileges. We did not have this issue with MySQL 5.7, but after migrating the data to a new MySQL 8 server, we are getting this error.

The error message indicates the user trying to drop the procedure does not have the SYSTEM_USER privilege, which is required to drop procedures created by other users in MySQL 8.0.23. The user can be granted this privilege by an administrator with the GRANT OPTION.

In addition, if the definer of the routine has the SYSTEM_USER
privilege, the user dropping it must also have this privilege. This is
enforced in MySQL 8.0.16 and later.

Source

You can do this by running the following command as an administrator:

GRANT SYSTEM_USER ON . TO 'user'@'host';

You can also check the current privileges of the user by running the following command:

SHOW GRANTS FOR 'user'@'host';

Another solution to consider, if the user that created the procedure is no longer in-use and you would like to delete all the procedures created by that user, you can use the following command as an administrator:

DROP USER 'user'@'host';

And another workaround solution to consider is to ensure the database object is created by account without the SYSTEM_USER privilege, then this is not an issue at all to deal with for other accounts without SYSTEM_USER privileges.

Supporting Resources

-
13.7.1.6 GRANT Statement

-
The SYSTEM_USER Dynamic Privilege

To modify users, you must have the CREATE USER privilege or the
UPDATE privilege on the mysql schema. We have added the
SYSTEM_USER dynamic privilege to make it possible to protect users
against the first case: modification by other users who have the
CREATE USER privilege. An account that has the SYSTEM_USER
privilege cannot be modified by an account that does not have
SYSTEM_USER, even if that account has CREATE USER.

How does the SYSTEM_USER privilege work?

The SYSTEM_USER privilege enforces the convention that if you are
modifying a user who is granted the SYSTEM_USER privilege, then in
addition to the privileges required to modify users, you also need to
have the SYSTEM_USER privilege. In other words, a user who has the
SYSTEM_USER and CREATE USER privileges can modify users who have
the SYSTEM_USER privilege.

-
13.7.1.5 DROP USER Statement

StackExchange Database Administrators Q#322707, answer score: 2