ThisCalculator

Everyday

Create MySQL User & Fix Permission Errors

Updated · Standard formula, shown below

Create MySQL User & Fix Permission Errors

This guide walks you through creating a new MySQL user and resolving common permission errors that prevent that user from accessing or modifying databases. You’ll learn the exact SQL commands, how to grant the right privileges, and how to troubleshoot issues like access denied or host mismatches. No prior MySQL administration experience is needed, but you’ll need credentials for an account with administrative rights (usually root).

What You’ll Need

  • Access to a MySQL server (local or remote) with an admin account (e.g., root).
  • The MySQL command-line client installed on your machine (or a tool like phpMyAdmin, but the command line is used here).
  • The hostname or IP address from which the new user will connect (e.g., localhost for local only, or '%' for any host).
  • A strong password for the new user.

Step-by-Step

  1. Log in to MySQL as an admin. Open a terminal and run: mysql -u root -p. Enter the root password when prompted. If you’re using a different admin user, replace root with that username.
  2. Check current users (optional but helpful). Run SELECT user, host FROM mysql.user; to see existing users and their hosts. This helps avoid duplicate usernames.
  3. Create the new user. Use the CREATE USER statement. For a user connecting only from the local machine: CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'strong_password';. For a user connecting from any host (less secure): CREATE USER 'newuser'@'%' IDENTIFIED BY 'strong_password';. Replace newuser and strong_password with your own values.
  4. Grant privileges. Decide what the user can do. To give full access to a specific database: GRANT ALL PRIVILEGES ON mydb.* TO 'newuser'@'localhost';. To give only SELECT (read) access: GRANT SELECT ON mydb.* TO 'newuser'@'localhost';. To grant global privileges (not recommended for most users), use GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost';.
  5. Apply the changes. Run FLUSH PRIVILEGES; to reload the grant tables. This ensures the new privileges take effect immediately.
  6. Verify the user can connect. Exit the admin session (EXIT;) and try logging in as the new user: mysql -u newuser -p. Enter the password you set. If you’re connecting remotely, use mysql -h hostname -u newuser -p.
  7. Test database access. Once logged in, run SHOW DATABASES; to see which databases the user can see. If you granted access to a specific database, it should appear. Try a simple query like SELECT 1; to confirm the connection works.

Common Mistakes

  • Forgetting the host part. MySQL users are defined by both username and host. 'newuser'@'localhost' is different from 'newuser'@'%'. If you create the user for localhost but connect from another machine, you’ll get an access denied error.
  • Not running FLUSH PRIVILEGES. While GRANT and CREATE USER generally take effect immediately, running FLUSH PRIVILEGES after changes is a safe habit, especially if you edited grant tables directly.
  • Using a weak password. MySQL may reject weak passwords if the validate_password plugin is enabled. Use a mix of letters, numbers, and symbols.
  • Granting too many privileges. Giving ALL PRIVILEGES ON *.* to a user who only needs one database is a security risk. Grant only what’s needed.
  • Mixing up database names. Ensure the database exists before granting privileges on it. You can check with SHOW DATABASES;.

Tips

  • Use specific hosts. Instead of %, use a specific IP or hostname (e.g., '192.168.1.100') to reduce exposure.
  • Create separate users for different applications. Each app gets its own user with only the privileges it needs.
  • Use a password manager to generate and store strong passwords.
  • Document your users. Keep a note of which user has which privileges, so you can audit later.
  • Revoke privileges when they’re no longer needed. Use REVOKE ALL PRIVILEGES ON mydb.* FROM 'newuser'@'localhost'; and then DROP USER 'newuser'@'localhost'; to remove the user entirely.

Troubleshooting

Access denied for user ‘newuser’@’localhost’ (using password: YES) – This usually means the host part doesn’t match where you’re connecting from. If you’re connecting from the same machine, the user must be created with @'localhost'. If connecting from another machine, the user must be created with that machine’s IP or @'%'. Also verify the password is correct.

ERROR 1130 (HY000): Host ‘…’ is not allowed to connect to this MySQL server – This indicates the user (or the host) isn’t permitted to connect from your current IP. Check the host part of the user. If you need remote access, create the user with @'%' or a specific IP range.

ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ – This is a login issue with the admin account. Double-check the root password. If you’ve forgotten it, you may need to reset it (which is beyond this guide).

ERROR 1064 (42000): You have an error in your SQL syntax – Check for typos in your SQL statements. Ensure you’re using single quotes around strings and semicolons at the end.

Privileges not taking effect – Run FLUSH PRIVILEGES; after any grant/revoke. Also, if you’re using a connection pool, existing connections may still have old privileges; reconnect.

Can’t create user because it already exists – If you see ERROR 1396 (HY000): Operation CREATE USER failed for 'newuser'@'localhost', the user already exists. You can either drop it first (DROP USER 'newuser'@'localhost';) or use ALTER USER to change its password.

Frequently Asked Questions

Q: What’s the difference between ‘localhost’ and ‘%’ in the host part?
A: ‘localhost’ means the user can only connect from the same machine where MySQL runs. ‘%’ means any host can connect, which is convenient but less secure. Use a specific IP if possible.

Q: How do I change a user’s password?
A: Use ALTER USER 'newuser'@'localhost' IDENTIFIED BY 'new_password'; then FLUSH PRIVILEGES;.

Q: Can I grant privileges on all databases at once?
A: Yes, use GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost';. But be cautious—this gives full control over all databases.

Q: What does FLUSH PRIVILEGES do?
A: It reloads the grant tables in memory, ensuring that any changes to user privileges are applied immediately. It’s a good practice after creating users or granting privileges.

Q: How do I see what privileges a user has?
A: Run SHOW GRANTS FOR 'newuser'@'localhost';.

Q: Why do I get an error when creating a user with a weak password?
A: If the validate_password plugin is enabled, MySQL enforces password strength requirements. Use a password with at least 8 characters, including uppercase, lowercase, numeric, and special characters.

Related

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.