Pre-General Availability Draft: 2017-06-27
MySQL supports static and dynamic privileges:
Static privileges are built in to the server. They are always available to be granted to user accounts and cannot be unregistered.
Dynamic privileges can be registered and unregistered at runtime. This affects their availability: A dynamic privilege that has not been registered cannot be granted.
The remainder of this section describes how dynamic privileges work in MySQL. The discussion uses the term “components” but applies equally to plugins.
Server administrators should be aware of which server components define dynamic privileges. For MySQL distributions, documentation of components that define dynamic privileges describes those privileges.
Third-party components may also define dynamic privileges; an administrator should understand those privileges and not install components that might conflict or compromise server operation. For example, one component conflicts with another if both define a privilege with the same name. Component developers can reduce the likelihood of this occurrence by choosing privilege names having a prefix based on the component name.
The server maintains the set of registered dynamic privileges internally in memory. Unregistration occurs at server shutdown.
Normally, a server component that defines dynamic privileges registers them when it is installed, during its initialization sequence. When uninstalled, a server component does not unregister its registered dynamic privileges. (This is current practice, not a requirement. That is, components could, but do not, unregister at any time privileges they register.)
No warning or error occurs for attempts to register an already registered dynamic privilege. Consider the following sequence of statements:
INSTALL COMPONENT 'my_component'; UNINSTALL COMPONENT 'my_component'; INSTALL COMPONENT 'my_component';
statement registers any privileges defined by server component
UNINSTALL COMPONENT does not
unregister them. For the second
COMPONENT statement, the component privileges it
registers are found to be already registered, but no warnings or
Dynamic privileges apply only at the global level. The server
stores information about current assignments of dynamic privileges
to user accounts in the
The server automatically registers privileges named in
global_grantsduring server startup (unless the
--skip-grant-tablesoption is given).
Dynamic privilege assignments listed in
global_grantsare persistent. They are not removed at server shutdown.
Example: The following statement grants to user
u1 the privileges required to control
replication (including Group Replication) on a slave server, and
to modify system variables:
GRANT REPLICATION_SLAVE_ADMIN, GROUP_REPLICATION_ADMIN, BINLOG_ADMIN ON *.* TO 'u1'@'localhost';
Granted dynamic privileges appear in the output from the
SHOW GRANTS statement and the
REVOKE at the global level, any
named privileges not recognized as static are checked against the
current set of registered dynamic privileges and granted if found.
Otherwise, an error occurs to indicate an unknown privilege
GRANT ALLat the global level grants all static global privileges and all currently registered dynamic privileges. A dynamic privilege registered subsequent to execution of the
GRANTstatement is not granted retroactively to any account.
REVOKE ALLat the global level revokes all granted static global privileges and all granted dynamic privileges.
PRIVILEGES statement reads the
global_grants table for dynamic privilege
assignments and registers any unregistered privileges found there.
For descriptions of the dynamic privileges provided by MySQL Server and server components included in MySQL distributions, see Section 6.2.1, “Privileges Provided by MySQL”.
In MySQL 8.0, many operations that previously
SUPER privilege are
also associated with a dynamic privilege of more limited scope.
(For descriptions of these privileges, see
Section 6.2.1, “Privileges Provided by MySQL”.) Each such operation can
be permitted to an account by granting the associated dynamic
privilege rather than
change improves security by enabling DBAs to avoid granting
SUPER and tailor user privileges
more closely to the operations permitted.
SUPER is now deprecated and will
be removed in a future version of MySQL.
When removal of
operations that formerly required
SUPER will fail unless accounts
SUPER are migrated to the
appropriate dynamic privileges. Use the following instructions
to accomplish that goal so that accounts are ready prior to
Execute this query to identify accounts that are granted
SELECT GRANTEE FROM INFORMATION_SCHEMA.USER_PRIVILEGES WHERE PRIVILEGE_TYPE = 'SUPER';
For example, if
SUPERfor binary log purging and system variable modification, these statements make the required changes to the account:
GRANT BINLOG_ADMIN, SYSTEM_VARIABLES_ADMIN ON *.* TO 'u1'@'localhost'; REVOKE SUPER ON *.* FROM 'u1'@'localhost';
After you have modified all applicable accounts, the
INFORMATION_SCHEMAquery in the first step should produce an empty result set.