Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

MySQL ERROR 1227: you need the CREATE USER privilege

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

Short 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.

  • Applies to MySQL 8.0, MySQL 8.4
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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;
  2. 2Match what you see, then jump to its fix

    1. 1SHOW 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. 2The 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. 3ALTER USER, DROP USER or RENAME USER fail with the same 1227 text.Another account-management statement fails with the same messageFix

What does the error look like?

  • ERROR 1227 (42000): Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation
  • Access 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

Did this fix it?

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

Did this fix it?

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

Did this fix it?

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:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.