When performing health checks on a database, I look at how many users are granted SUPER privileges. If you’re reading this, you know why. SUPER privileges pose a potential security risk and should be limited to a few users that need this far-reaching access to operations and executable statements. It is good practice to limit privileges to the corresponding operations the user news to perform specific tasks. The table below lists potential security risks associated with specific operations that SUPER privileges enables a user to perform.
| Operation | Potential Security Risk(s) |
| FILE | User can read files that the MySQL server can read and transfer them to the client host. |
| GRANT OPTION | User can give their privileges to other users and/or combine their privileges with other users’ privileges. |
| ALTER | If user does not have access to a specific table, can change the name of the table which will enable access. It requires the user to have DROP privileges. |
| SHUTDOWN | User can deny access to a server by terminating it. |
| PROCESS | User can have access to passwords by viewing in plain text currently executing statements. |
| INSERT or UPDATE | If a user has access to the mysql system database, can add and/or modify existing privileges. |
Another reason to evaluate which users need SUPER privileges is to prepare for the removal of them in future versions. In MySQL 5.7, there are two options for giving a user comparable SUPER privileges: 1) grant the user specific static privileges; 2) assign the account a role with specific privileges. In MySQL 8.0, a third option is recommended, migrating an account from SUPER to dynamic privileges.
Static Versus Dynamic Privileges:
Status privileges are built in the server. You do not need to enable them to access them. Examples are ALTER, SELECT, and CREATE. Dynamic privileges are a new feature in MySQL 8.0. They function similarly to roles with specific privileges. Instead of you creating a role to assign to users, MySQL 8.0 has a set of components called dynamic privileges, which can be enabled and assigned. The table below lists commonly used SUPER privileges and the corresponding dynamic privilege.
| SUPER Privilege Executables | Corresponding Dynamic Privilege | Dynamic Privilege Operation |
| KILL statements,GRANT statements,REVOKE statements | CONNECTION_ADMIN | User can kill threads belonging to other accounts. |
| {START| STOP} GROUP REPLICATION,SET group_replication_consistency | GROUP_REPLICATION_ADMIN | User can start and stop Group Replication. |
| PURGE BINARY LOGS,BINLOG statements | BINLOG_ADMIN | User can delete all binary log files listed in the log index file and generate printable representations of the binlog file contents. |
| GRANT statement,ROLES_GRAPHML() | ROLE_ADMIN | User can grant and revoke roles. |
| {START | STOP} REPLICA,CHANGE SOURCE TO..,CHANGE REPLICATION FILTER | REPLICATION_SLAVE_ADMIN | User can connect, start, stop source server. |
| Setting the authorization ID | SET_USER_ID | Use can specify any user as a DEFINER of a stored program or view by setting the authorization ID. |
| SET GLOBAL VARIABLE | SYSTEM_VARIABLE_ADMIN | User can make system variable changes at runtime. Implies SESSION_VARIABLES_ADMIN |
| SET PERSIST,SET PERSIST ONLY | PERSIST_RO_VARIABLES_ADMIN | User can use SET_PERSIST and SET_PERSIST_ONLY to persist system variables. |
| Version Token Functions | VERSION_TOKEN_ADMIN | User is able to execute version token functions |
A user can be granted one or a few dynamic privileges instead of granting multiple static privileges. Thankfully, migrating SUPER privileges to dynamic privileges can be done in a few easy steps.
Steps:
First, identify which accounts have SUPER privileges using the query below.
Next, decide which operation the user needs to perform and grant the corresponding dynamic privileges. Then grant the user the privileges needed to perform operations and revoke SUPER privileges using these queries as an example.
For a full list of dynamic privileges, visit the MySQL Reference Manual website.