Understanding MySQL Dynamic Permissions and Replacing the SUPER Privilege

Background: Before MySQL 8.0, permissions were coarse, with a powerful SUPER privilege that combined many abilities, making it difficult to grant only specific capabilities such as setting system variables.

MySQL 8.0 introduces a finer‑grained permission model that separates static privileges from dynamic privileges. Figure 1 shows static privileges, Figure 2 shows dynamic privileges, where the SUPER privilege is being decomposed.

Example: user 'ytt2'@'localhost' originally has SUPER. The SHOW GRANTS FOR ytt2@'localhost'; output displays the full SUPER‑related grants.

To replace SUPER with only the abilities to set system and session variables, the following commands are issued:

GRANT SESSION_VARIABLES_ADMIN, SYSTEM_VARIABLES_ADMIN ON *.* TO 'ytt2'@'localhost';
REVOKE SUPER ON *.* FROM 'ytt2'@'localhost';

The queries return “Query OK” and a warning indicating that the SUPER privilege identifier is deprecated.

After revoking, SHOW GRANTS FOR ytt2@'localhost'; shows the remaining INSERT, UPDATE, DELETE, CREATE, ALTER privileges together with the newly granted SESSION_VARIABLES_ADMIN and SYSTEM_VARIABLES_ADMIN privileges.

Other dynamic privileges exist but are not covered here.