Static privileges are built into the server and stored as Y/N columns of the grant tables: SELECT, INSERT, UPDATE, DELETE, the DDL rights, and global ones such as FILE (server files), PROCESS (everyone's SQL) and CREATE USER. Dynamic privileges are registered by the server and its components, are global only, and are rows in mysql.global_grants. They split the deprecated SUPER into narrow rights: CONNECTION_ADMIN to kill others' sessions, SYSTEM_VARIABLES_ADMIN for SET GLOBAL, SYSTEM_USER to shield an account from ordinary admins.
GRANT PROCESS, CONNECTION_ADMIN ON *.* TO 'auditor'@'localhost';
GRANT CONNECTION_ADMIN ON shop.* TO 'auditor'@'localhost';
SELECT user, priv FROM mysql.global_grants WHERE user = 'auditor';
REVOKE PROCESS, CONNECTION_ADMIN ON *.* FROM 'auditor'@'localhost';Output
ERROR 3619 (HY000) at line 2: Illegal privilege level specified for CONNECTION_ADMIN +---------+------------------+ | user | priv | +---------+------------------+ | auditor | CONNECTION_ADMIN | +---------+------------------+
GRANT SUPER still works but raises warning 1287, The SUPER privilege identifier is deprecated. Root on this server holds 42 dynamic privileges, and SHOW PRIVILEGES lists every privilege with its scope.