ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation

What it means

Some statements need a privilege that applies to the whole server rather than to one database: setting global variables, turning off binary logging for a session, creating objects that run as another user, reading the InnoDB monitor. Error 1227 means your account doesn’t have one, and the message lists the privileges, any one of which would be enough. Nothing ran.

SUPER is MySQL’s old catch-all administrative privilege. MySQL 8 split it into narrower dynamic privileges, so the list usually names one of those too, and MariaDB names its own:

Hosted databases such as Amazon RDS don’t give even their administrator account SUPER, so these statements fail there whatever you grant.

Common causes

1. Importing a mysqldump file into a hosted database or as a non-admin user. Dumps can contain two kinds of line that need these privileges:
   • SET @@SESSION.SQL_LOG_BIN= 0; and SET @@GLOBAL.GTID_PURGED=…, written when the source server has GTIDs on;
   • DEFINER=`root`@`localhost` on views, procedures, functions, triggers and events, naming an account that isn’t you.
2. Changing server settings with SET GLOBAL from an application account.
3. Monitoring queries (SHOW ENGINE INNODB STATUS, SHOW BINARY LOGS, some performance_schema tables) from an account without PROCESS or replication privileges.
4. Exporting with INTO OUTFILE without the FILE privilege.

How to fix it

Find the line the import stopped at

The client names it: ERROR 1227 (42000) at line 18: …. Look at it:

sed -n '18p' dump.sql

Re-dump without GTID and binary-log lines

mysqldump --set-gtid-purged=OFF --single-transaction --routines --triggers shop > shop.sql

--set-gtid-purged=OFF leaves out both SQL_LOG_BIN and GTID_PURGED. Only skip them if you aren’t restoring into a replica that needs the source’s GTID history.

Remove the DEFINER clauses

Without a DEFINER clause, an object’s definer is the account that creates it. Remove the clause from the dump, or make the object run with the caller’s privileges:

sed -e 's/DEFINER=[^ ]* SQL SECURITY DEFINER/SQL SECURITY INVOKER/' \
    -e 's/DEFINER=`[^`]*`@`[^`]*`//g' shop.sql > shop-nodefiner.sql

Check the result before importing: the second pattern removes every DEFINER= clause, including those of procedures and triggers. Alternatively, create the account the dump names ('root'@'localhost' is rarely what you want on a hosted server) or set DEFINER to the account you import with.

Grant the narrow privilege, where you can

On a server you run, give the account only the privilege the message names, not SUPER:

GRANT PROCESS ON *.* TO 'monitor'@'%';
GRANT SET_ANY_DEFINER ON *.* TO 'deploy'@'%';   -- MySQL 8.2 and later

Dynamic privileges are granted ON *.*. On a hosted database, use the provider’s parameter settings for server variables rather than SET GLOBAL.

Reproduce it

In a temporary MySQL 8.4.11 container with GTIDs on (removed afterwards), a view owned by root was dumped with mysqldump shop, then imported by seo_dev, an account with all privileges on that database only:

ERROR 1227 (42000) at line 18: Access denied; you need (at least one of) the SUPER, SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN privilege(s) for this operation

Line 18 was SET @@SESSION.SQL_LOG_BIN= 0; (line 24 held SET @@GLOBAL.GTID_PURGED=…). Dumped again with --set-gtid-purged=OFF and imported into an empty database, it stopped at the view:

ERROR 1227 (42000) at line 90: Access denied; you need (at least one of) the SUPER or SET_ANY_DEFINER privilege(s) for this operation

Line 90 was /*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */. After the sed that replaces it with SQL SECURITY INVOKER, the whole dump imported, view included. (Importing over the existing database instead failed earlier, on DROP VIEW of root’s view, asking for SYSTEM_USER.)

On the shared MySQL 8.4 server, the same account type running the other statements in the table:

ERROR 1227 (42000): Access denied; you need (at least one of) the PROCESS privilege(s) for this operation
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER, REPLICATION CLIENT privilege(s) for this operation
ERROR 1227 (42000): Access denied; you need (at least one of) the FILE privilege(s) for this operation

SET GLOBAL was tried only in the temporary containers, where it was refused with SUPER or SYSTEM_VARIABLES_ADMIN on MySQL. MariaDB 11.4.13 gave the same number and SQLSTATE with its own privilege names:

ERROR 1227 (42000): Access denied; you need (at least one of) the SET USER privilege(s) for this operation
ERROR 1227 (42000): Access denied; you need (at least one of) the BINLOG ADMIN privilege(s) for this operation
ERROR 1227 (42000): Access denied; you need (at least one of) the BINLOG MONITOR privilege(s) for this operation

In Inlet

When a statement fails, Inlet shows the server’s error and links to this page. For MySQL and MariaDB, Inlet has an Activity monitor for the server’s sessions and an accounts view for its accounts.