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.

OperationPotential Security Risk(s)
FILEUser can read files that the MySQL server can read and transfer them to the client host.
GRANT OPTIONUser can give their privileges to other users and/or combine their privileges with other users’ privileges.
ALTERIf 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.
SHUTDOWNUser can deny access to a server by terminating it.
PROCESSUser can have access to passwords by viewing in plain text currently executing statements.
INSERT or UPDATEIf 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 ExecutablesCorresponding Dynamic PrivilegeDynamic Privilege Operation
KILL statements,GRANT statements,REVOKE statementsCONNECTION_ADMINUser can kill threads belonging to other accounts.
{START| STOP} GROUP REPLICATION,SET group_replication_consistencyGROUP_REPLICATION_ADMINUser can start and stop Group Replication.
PURGE BINARY LOGS,BINLOG statementsBINLOG_ADMINUser 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_ADMINUser can grant and revoke roles.
{START | STOP} REPLICA,CHANGE SOURCE TO..,CHANGE REPLICATION FILTERREPLICATION_SLAVE_ADMINUser can connect, start, stop source server.
Setting the authorization IDSET_USER_IDUse can specify any user as a DEFINER of a stored program or view by setting the authorization ID.
SET GLOBAL VARIABLE
SYSTEM_VARIABLE_ADMINUser can make system variable changes at runtime. Implies SESSION_VARIABLES_ADMIN
SET PERSIST,SET PERSIST ONLYPERSIST_RO_VARIABLES_ADMINUser can use SET_PERSIST and SET_PERSIST_ONLY to persist system variables.
Version Token FunctionsVERSION_TOKEN_ADMINUser 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.

Migrating from SUPER to Dynamic Privileges SUPER Privileges

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.

Migrating From SUPER to Dynamic Privileges Granting Priveleges

For a full list of dynamic privileges, visit the MySQL Reference Manual website.

Share This