MySQL :: MySQL 8.0 Reference Manual :: 6.2.2 Static Versus Dynamic Privileges

MySQL 8.0 Reference Manual / ... / Static Versus Dynamic Privileges

Pre-General Availability Draft: 2017-07-17

6.2.2 Static Versus Dynamic Privileges

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.

For example, the SELECT and INSERT privileges are static and always available, whereas a dynamic privilege becomes available only if the server component that implements it has been enabled.

The remainder of this section describes how dynamic privileges work in MySQL. The discussion uses the term “components” but applies equally to plugins.

Note

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';

The first INSTALL COMPONENT statement registers any privileges defined by server component my_component, but UNINSTALL COMPONENT does not unregister them. For the second INSTALL COMPONENT statement, the component privileges it registers are found to be already registered, but no warnings or errors occur.

Dynamic privileges apply only at the global level. The server stores information about current assignments of dynamic privileges to user accounts in the mysql.global_grants system table:

The server automatically registers privileges named in global_grants during server startup (unless the --skip-grant-tables option is given).
The GRANT and REVOKE statements modify the contents of global_grants.
Dynamic privilege assignments listed in global_grants are 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 INFORMATION_SCHEMA USER_PRIVILEGES table.

For GRANT and 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 identifier.

For GRANT and REVOKE the meaning of ALL [PRIVILEGES] at the global level includes all static global privileges, as well as all currently registered dynamic privileges:

GRANT ALL at the global level grants all static global privileges and all currently registered dynamic privileges. A dynamic privilege registered subsequent to execution of the GRANT statement is not granted retroactively to any account.
REVOKE ALL at the global level revokes all granted static global privileges and all granted dynamic privileges.

The FLUSH 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”.

Migrating Accounts from SUPER to Dynamic Privileges

In MySQL 8.0, many operations that previously required the 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 SUPER. This 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 SUPER occurs, operations that formerly required SUPER will fail unless accounts granted SUPER are migrated to the appropriate dynamic privileges. Use the following instructions to accomplish that goal so that accounts are ready prior to SUPER removal:

Execute this query to identify accounts that are granted SUPER:

SELECT GRANTEE FROM INFORMATION_SCHEMA.USER_PRIVILEGES
WHERE PRIVILEGE_TYPE = 'SUPER';

For each account identified by the preceding query, determine the operations for which it needs SUPER. Then grant the dynamic privileges corresponding to those operations, and revoke SUPER.
For example, if 'u1'@'localhost' requires SUPER for 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_SCHEMA query in the first step should produce an empty result set.

PREV HOME UP NEXT

User Comments