How to give complete database permissions in MySQL

Other Questions MySQL
1

I set up a new database called ‘testdb’ and created a user for it. Here’s what I did:

CREATE DATABASE testdb CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE USER 'dbuser'@'localhost' IDENTIFIED BY 'password123';
GRANT ALL PRIVILEGES ON testdb.* TO 'dbuser'@'localhost';
FLUSH PRIVILEGES;

The user can connect to the database just fine, but when I try to create tables I get an error. When I run this command:

CREATE TABLE users (name VARCHAR(50) CHARACTER SET utf8);

I get this error message:

ERROR 1142 (42000): CREATE command denied to user 'dbuser'@'localhost' for table 'users'

What’s the right way to give a user full access to a database so they can create tables and do everything else they need to do?

2

Check for existing users with similar names - MySQL sometimes gets confused by duplicate usernames from different hosts. Drop the user completely and recreate it: DROP USER 'dbuser'@'localhost'; then run your create/grant commands again. Fixed the same issue for me last week.

3

Had this exact issue before - it’s usually a session problem. Disconnect and reconnect as dbuser after running the GRANT commands. MySQL sometimes won’t recognize new privileges in your current session, even with FLUSH PRIVILEGES. Also, make sure you’re actually connected as ‘dbuser’@‘localhost’ when testing, not root. Run SELECT USER(); to check. The error says the user lacks CREATE permissions, which is weird since ALL PRIVILEGES should cover that. One more thing - run your initial commands from mysql command line as MySQL root, not through a GUI tool. Those can have different connection settings that mess things up.

4

Your syntax looks fine. This usually happens when you’re not in the right database context. After connecting as ‘dbuser’, run USE testdb; before creating tables. MySQL doesn’t always switch to the granted database automatically, even when the connection works. I’ve hit this before - user had the right privileges but was accidentally trying to create tables in a different schema. Also check if any global privileges are overriding your database-specific grants. ALL PRIVILEGES should definitely let you create tables in that database.

5

This might be MySQL’s privilege inheritance acting up. ALL PRIVILEGES doesn’t always grant what you’d expect, especially in newer versions. Try being specific with your grants:

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, INDEX, REFERENCES ON testdb.* TO 'dbuser'@'localhost';

I hit something similar migrating to MySQL 8.0 - ALL PRIVILEGES behavior changed a bit. Also check if sql_mode restrictions are blocking table creation. Run SELECT @@sql_mode; and try temporarily setting it to an empty string to see if that fixes your CREATE TABLE problem.

6

that’s odd! u might wanna try SHOW GRANTS FOR 'dbuser'@'localhost'; to check the actual perms. maybe there’s a typo or grants didn’t apply right. also, confirm ur username and host are spot on.