The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
Need a server to try this on? Antihost offers Linux VPS plans with NVMe storage, full root access and a 14-day free trial.
MySQL’s ERROR 1142 (42000): CREATE command denied to user ... is a privilege error, not a SQL-syntax error. Copy the exact user and host shown in the message, inspect that account with SHOW GRANTS, then grant CREATE at the narrowest scope that matches the operation. If the permission comes from a role, the role must be granted and active in the current session. CREATE ROLE is different: it requires a global CREATE ROLE or CREATE USER privilege, with an additional administrative privilege when the server is read-only.
What the error means
MySQL identifies this failure as error 1142, ER_TABLEACCESS_DENIED_ERROR. The message follows this pattern:
Recommended Free Tools
ERROR 1142 (42000): CREATE command denied to user 'user_name'@'host_name' for table 'table_name'
The server accepted your connection and parsed the statement, but the account selected for that connection does not have the required CREATE privilege at a scope that covers the object you are trying to create. A malformed statement produces a syntax error instead; changing SQL punctuation will not resolve error 1142.
#1 Best Overall
MySQL accounts are two-part identities: 'user_name'@'host_name'. A grant to 'app'@'localhost' is not automatically a grant to 'app'@'%', and neither necessarily matches a connection from a particular IP address. The host printed in the error is therefore as important as the user name.
Fastest safe fix
- Copy the complete account identity from the error, including its host.
- Connect as an administrator and run
SHOW GRANTS FOR 'user'@'host';. - Grant only the required privilege at the target database or table scope, for example
GRANT CREATE ON mydb.* TO 'app'@'host';. - If a role is intended to supply the privilege, grant that role to the account and activate it in the session.
- Retry the same statement through the same connection identity and against the same schema.
Do not start with a global grant simply because it is convenient. A database-level grant normally fixes application table creation while limiting the account to one schema.
Step 1: Identify the account MySQL is actually checking
Read both values from the error
Copy the text between the first pair of quotes and the host after the @. For example, an error naming 'app'@'localhost' refers to a different account row from one naming 'app'@'10.20.30.15' or 'app'@'%'. Preserve the spelling and punctuation when you build the SHOW GRANTS and GRANT statements.
Why host mismatches are common
Applications often connect through a Unix socket and therefore match localhost, while a command-line client using TCP may match an IP address. A container, proxy, or remote application can also present a different source host than the developer expects. MySQL chooses the account row that best matches the connection, so granting CREATE to another host variant can leave the failing session unchanged.
Confirm the session’s selected schema
The object name in your statement may be unqualified, such as CREATE TABLE orders (...). In that case MySQL uses the session’s current database. Check it with:
SELECT USER(), CURRENT_USER(), DATABASE();
USER() shows the user and host presented by the client, while CURRENT_USER() shows the authenticated account row whose privileges are being used. DATABASE() shows the schema selected for an unqualified CREATE TABLE. These values help explain why a grant appears correct but does not affect the failing connection.
Do these 3 things before closing this tab:
1Repair Windows errors before they cause bigger problems2Fix the driver behind crashes, sound loss and screen glitches3Clear out junk files and repair common Windows errorsStep 2: Inspect the account’s existing grants
Run this statement from an administrator session, replacing both parts with the identity shown by the error:
SHOW GRANTS FOR 'app'@'host';
The result can contain direct privileges, role grants, or both. Look for CREATE on the schema involved in the statement. A grant on otherdb.* does not cover mydb.*, and a role listed in the output does not prove that the role is active in the current session.
Interpret common grant scopes
| Grant form | What it covers | Typical use | Risk and limits |
|---|---|---|---|
... ON mydb.mytable |
One named table, where the operation and server version support table-level scope | A tightly controlled operation on a specific object | Least exposure, but it may not cover creation of other objects or every statement variant |
... ON mydb.* |
Objects in one database | An application that creates tables in its own schema | Broader than one table, but avoids access to other databases |
... ON *.* |
All databases on the server | Exceptional administrative or platform accounts | Highest blast radius; avoid for ordinary application users |
The CREATE privilege enables database and table creation. Grant it at the smallest scope that actually covers the statement rather than using a blanket global grant.
Step 3: Grant CREATE directly at the appropriate scope
Database-level grant for application tables
For an application that should create tables in mydb, an administrator can run:
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
GRANT CREATE ON mydb.* TO 'app'@'host';
This grants the privilege for that database only. It does not grant the account permission to create objects in every schema.
Table-level grant when it is sufficient
Where the operation and server version support a table-level grant, use:
GRANT CREATE ON mydb.mytable TO 'app'@'host';
Use this narrow form only when the statement genuinely concerns that table and the account does not need to create additional tables. A table-specific grant cannot substitute for a database-level grant if the application creates arbitrary new table names in the schema.
Granting permission to an existing account
The target account must already exist for a normal GRANT statement. If the error names a host variant that has not been created, first have an administrator create the intended account, then grant the privilege to that exact user-and-host pair. Do not silently redirect the grant to a similarly named account with a different host.
Do not use a global grant as a shortcut
This command is deliberately broad:
GRANT CREATE ON *.* TO 'app'@'host';
It permits creation across all databases and should be reserved for accounts whose documented job requires that reach. For an application confined to one schema, mydb.* is the safer boundary.
Step 4: Handle roles correctly
Grant a role that contains CREATE
Organizations often keep privileges in a role instead of attaching them to every user. An administrator can assign a role with:
GRANT 'app_developer' TO 'app'@'host';
The role itself must contain a suitable CREATE grant, such as CREATE ON mydb.*. Assigning a role that lacks the needed privilege will not change the error.
Activate the role in the failing session
A granted role does not necessarily supply privileges to the current session until it is active. In the same connection that will execute the CREATE statement, activate the role explicitly, for example:
Quick wins for a faster PC:
Repair Windows errors before they cause bigger problemsFix Now →Scan for outdated or missing drivers - takes under a minuteDriver Scan →Clear out junk files and repair common Windows errorsFree Scan →SET ROLE 'app_developer';
Then verify the effective grants with SHOW GRANTS; and retry. If your account has multiple roles, select the role or role set that includes the database-level CREATE privilege. A role activated in one connection does not automatically become active in an already-open connection used by another process.
Check default-role behavior
If an application opens new sessions and relies on automatic role activation, confirm that the intended role is configured as a default role for the account. Otherwise each new connection can begin without the role even though SHOW GRANTS FOR lists it. Explicit activation during session setup is more predictable for scripts and deployment tools.
CREATE ROLE is a separate privilege case
If the denied statement is literally CREATE ROLE ..., a database-level CREATE grant is not the required fix. MySQL’s CREATE ROLE statement requires the executing account to have the global CREATE ROLE or CREATE USER privilege.
Rank #4
When the server’s read_only setting is enabled, the account additionally needs CONNECTION_ADMIN; the older SUPER privilege is the deprecated alternative referenced for this purpose. These are global administrative capabilities, so grant them only to an account responsible for identity administration.
A newly created role is locked, has no password, and uses the default authentication plugin. Creating the role therefore does not make it an ordinary login account or give it table-creation rights. After creating a role, grant the role the privileges it is meant to carry, assign it to users, and activate it where required.
Retry using the same identity and target
- Keep the original client, connection method, and credentials if possible. Switching from a local socket to a remote TCP connection can select a different host entry.
- Run
SELECT CURRENT_USER(), DATABASE();in that session to confirm the account row and schema. - Use the exact database named in the grant, or qualify the statement explicitly, for example
CREATE TABLE mydb.orders (...);. - If a role was used, run
SET ROLEin this same connection before the statement. - Retry the
CREATEstatement and record the complete error if it still fails; a changed error may indicate that the original privilege problem is resolved and a different requirement is now being checked.
When CREATE still fails after a grant
The grant was made to the wrong host variant
Compare the host in CURRENT_USER() and the error with the host in SHOW GRANTS FOR. Correct the grant target if the account is, for example, 'app'@'localhost' but the connection is matching 'app'@'10.0.0.25'. A wildcard account may also be superseded by a more-specific host row, so inspect the exact row MySQL selected rather than assuming % is in use.
The selected database is not the granted database
Check DATABASE() and compare it with the schema in the grant. A grant on mydb.* does not authorize CREATE TABLE in testdb. Select the intended schema with USE mydb; or qualify the object name.
The role is listed but inactive
SHOW GRANTS FOR can show that a role is assigned, while the current session still lacks its privileges. Activate the role in that session, then use SHOW GRANTS; without FOR to inspect effective grants. Repeat this initialization for every new application connection unless default-role configuration guarantees activation.
PC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11Crashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minuteThe statement is a different CREATE operation
CREATE TABLE, CREATE DATABASE, and CREATE ROLE do not have identical authorization rules. Re-read the statement named in the error and apply the requirement for that operation. In particular, do not troubleshoot CREATE ROLE with only a schema-level CREATE grant.
The account has CREATE but another prerequisite is missing
Once the authorization check passes, MySQL may report a separate issue such as an existing object name, invalid column definition, missing referenced privilege, or a storage-engine constraint. Treat that later message independently; error 1142 itself means the account lacked the required authorization at the time of the original attempt.
Best Value
Choosing between direct grants and roles
| Approach | Best fit | Advantages | Operational caution |
|---|---|---|---|
Direct GRANT CREATE ON mydb.* |
A small deployment with one or a few accounts | Easy to inspect and quick to apply | Privilege changes must be repeated for each account; host variants must be handled individually |
Role containing CREATE |
Teams or services sharing a defined permission set | Centralizes policy and simplifies auditing | The role must be granted, active, and correctly initialized in every session |
Global CREATE |
Only accounts whose duties span databases | Works across schemas | Greatly increases the damage a compromised account could cause |
For most application deployments, a role or direct grant limited to the application schema provides the best balance. Reserve global privileges for database administration and document why they are necessary.
Least-privilege checklist for administrators
- Record the exact
'user'@'host'identity from the failing connection. - Inspect current privileges before changing them with
SHOW GRANTS FOR. - Grant
CREATEon one table or one database when that meets the requirement. - Avoid
ON *.*unless the account’s role explicitly requires server-wide creation. - If using roles, verify both role assignment and session activation.
- Treat
CREATE ROLEas an identity-administration operation requiring global privileges. - Retest through the same network path, credentials, schema, and connection pool used by the application.
- Keep a record of the grant and its intended scope so it can be narrowed or revoked later.
Worked diagnostic examples
Application cannot create a table in its own schema
The error says CREATE command denied to user 'orders_app'@'10.1.4.8', and the application uses schema orders. An administrator should inspect:
SHOW GRANTS FOR 'orders_app'@'10.1.4.8';
If no suitable grant exists, the narrow fix is:
GRANT CREATE ON orders.* TO 'orders_app'@'10.1.4.8';
The application must reconnect using the same host identity and select orders before retrying.
Developer was granted a role but still receives 1142
SHOW GRANTS FOR 'developer'@'localhost'; lists 'schema_creator', but SHOW GRANTS; in the active client does not show CREATE ON project.*. The missing step is session activation:
SET ROLE 'schema_creator';
After activation, verify effective grants and run the original statement without changing the account or schema. If a new connection is opened by a pool, configure that connection’s role initialization as well.
Free tools Windows power users keep installed
One-click scans. No signup required.
An administrator tries to create a role
For CREATE ROLE 'reporting';, granting CREATE ON reports.* is irrelevant. The executing account needs global CREATE ROLE or CREATE USER. If read_only is enabled, it also needs CONNECTION_ADMIN (or the deprecated SUPER privilege). This is a separate administrative decision from allowing an application to create tables.
Final decision path
Use the error’s account and host as the starting point, not the username you expected the application to use. Confirm the selected schema, inspect effective grants, and then choose a table-level or database-level CREATE grant that covers only the required objects. If privileges are role-based, activate the role in the same session. If the statement is CREATE ROLE, switch to the global role-management requirements instead. These checks resolve the common causes of error 1142 without granting unnecessary server-wide access.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
