MySQL error 1227: Access denied; you need a privilege
The operation needs a global or dynamic privilege the account doesn't have, such as SUPER, SYSTEM_VARIABLES_ADMIN or PROCESS.
- Error number
1227- Name
ER_SPECIFIC_ACCESS_DENIED_ERROR- SQLSTATE
42000- Category
- Access and privileges
What it means
Some statements need administrative privileges rather than table privileges: setting global variables, killing other users' threads, creating objects with a different DEFINER, and so on. The message lists the privileges that would allow it. It's common when importing a dump from another server into a managed database where you aren't a superuser.
Common causes
- Importing a dump containing
DEFINER=clauses for another user, orSET @@GLOBAL...statements. SET GLOBALorSET PERSISTwithoutSYSTEM_VARIABLES_ADMIN(orSUPER).- Killing another user's query without
CONNECTION_ADMIN(orSUPER). - Managed services that never grant
SUPER.
How to fix it
-
Read the privilege names in the message and grant one of them if you administer the server.
GRANT SYSTEM_VARIABLES_ADMIN ON *.* TO 'admin'@'%';
-
For dumps, strip or rewrite
DEFINERclauses and globalSETstatements, or create the dump with options that leave them out, for examplemysqldump --set-gtid-purged=OFFfor GTID statements.
Related errors
1142Command denied to user for table1419SUPER privilege needed with binary logging1449Definer user does not exist
More access and privileges errors
1045Access denied for user1044Access denied for user to database1130Host is not allowed to connect1698Access denied for user (no password)1143Command denied to user for column1396Operation CREATE USER failed
The code and message text are from the MySQL 8.4 Error Message Reference. Browse the full MySQL error list or search every database error.
Work on MySQL in SQLly
A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for MySQL and the other databases you use.