MySQL ERROR 1227: you need the CREATE USER privilege
ERROR 1227 (42000): Access denied; you need (at least one of) the CREATE USER privilege(s) for this operationShort answer
Error 1227 means the account running CREATE USER lacks the privilege for it. In MySQL 8 that is the global CREATE USER privilege (or INSERT on the mysql schema). If the server is read-only, it also needs CONNECTION_ADMIN. Check SHOW GRANTS and @@read_only, then grant from an administrator account.
Fix it fast
1Find out which cause you have
See who you are connected as, what that account may do, and whether the server is read-only.
SELECT CURRENT_USER(); SHOW GRANTS; SELECT @@read_only, @@super_read_only;2Match what you see, then jump to its fix
- 1
SHOW GRANTS has no CREATE USER and no INSERT on mysql.* (often a per-database account or a hosting panel user).The account has no CREATE USER privilegeFix - 2
The account does have CREATE USER, but @@read_only is 1 (a replica, or a primary put in read-only mode for maintenance).The server is read-only and the account lacks CONNECTION_ADMINFix - 3
ALTER USER, DROP USER or RENAME USER fail with the same 1227 text.Another account-management statement fails with the same messageFix
- 1
What does the error look like?
ERROR 1227 (42000): Access denied; you need (at least one of) the CREATE USER privilege(s) for this operationAccess denied; you need (at least one of) the %s privilege(s) for this operation
Cause 1 of 3
The account has no CREATE USER privilege
1Check
CREATE USER needs the global CREATE USER privilege, or INSERT on the mysql system schema. A grant on one application database does not give it.
SHOW GRANTS FOR CURRENT_USER();2Fix
Connect as an administrator (for example root) and grant the privilege to the account that must manage users. Prefer this over giving it ALL PRIVILEGES.
GRANT CREATE USER ON *.* TO 'admin'@'localhost';3Verify
Reconnect as that account and run the statement again.
CREATE USER 'report'@'localhost' IDENTIFIED BY 'change-me';Confirmed by dev.mysql.com
Cause 2 of 3
The server is read-only and the account lacks CONNECTION_ADMIN
1Check
With read_only on, CREATE USER also needs CONNECTION_ADMIN (or the deprecated SUPER).
SELECT @@read_only;2Fix
On a replica, run user changes on the primary instead. If this really is the right server, grant CONNECTION_ADMIN to the admin account.
GRANT CONNECTION_ADMIN ON *.* TO 'admin'@'localhost';3Verify
Run CREATE USER again from the admin account.
SHOW GRANTS FOR 'admin'@'localhost';Confirmed by dev.mysql.com
Cause 3 of 3
Another account-management statement fails with the same message
1Check
The same CREATE USER privilege also covers ALTER USER, CREATE ROLE, DROP ROLE, DROP USER, RENAME USER and REVOKE ALL PRIVILEGES, so one grant fixes all of them.
SHOW GRANTS FOR 'admin'@'localhost';2Fix
Grant CREATE USER once; do not add one-off grants for each statement.
GRANT CREATE USER ON *.* TO 'admin'@'localhost';3Verify
Run the failing statement again.
DROP USER IF EXISTS 'report'@'localhost';Confirmed by dev.mysql.com
How do I stop it coming back?
- Give one named admin account CREATE USER and keep application accounts to the database they use.
- Run account changes on the primary only.
- Keep user-creation statements in a reviewed script, not typed in a shared panel.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Projects of mine built on the same technologies:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fixDatabases
DBeaver fails to resolve Maven dependencies when downloading a database driver
A driver library does not download automatically
DBeaver downloads driver jars from a Maven repository.
3 causes, each with a fixDatabases
DBeaver cannot verify the MySQL server certificate
unable to find valid certification path to requested target
DBeaver's MySQL driver is checking the server certificate and Java does not trust the CA that signed it.
2 causes, each with a fix