Create new user in MySQL and give it full access to one database

What privileges are you granting?

The keyword ALL PRIVILEGES is like a power suit for your MySQL user. It includes standard operations (SELECT, INSERT, UPDATE, DELETE, etc.) but it's no "Infinity Gauntlet", the user cannot grant permissions to others themselves.

If you want to set specific table access, use yourDb.yourTable instead of yourDb.*. Also, don't forget to put database names, usernames, or hostnames in single quotes. It's like putting your dog on a leash; you don't want it biting strangers or running off!

Password security during setup

Sure, CREATE USER can create a password, but real devs know password setting happens at the GRANT PRIVILEGES show:

GRANT ALL PRIVILEGES ON dbname.* TO 'username'@'host' IDENTIFIED BY 'new_password';


Worried about the eavesdroppers? Secure those credentials! Store them in the ~/.my.cnf file; it's like the Fort Knox for credentials.

Terminal commands and shortcuts

Elevate your command-line game with this little trick:

mysql -u root -p -e "COMMAND";


Sprinkle some magic by creating shell commands like mysql-create-user and mysql-drop-user. It's like having your own wand for database operations!

Refresh your privileges

After the whole shebang of granting privileges, FLUSH PRIVILEGES; comes to play. It's the bouncer ensuring the newly permitted user gets the proper access.

Fine-tuning the privileges

Sometimes you may not want a user to rain over your database. That’s when you handpick privileges like a chef choosing ingredients:

GRANT SELECT, INSERT, UPDATE ON dbname.* TO 'tempuser'@'host';


Wanna kick a user out? Use REVOKE and lay down the law:

REVOKE ALL ON dbname.* FROM 'username'@'host';


Look but don't touch, granting privileges rule

The GRANT OPTION is a double-edged sword. It allows a user to grant access, but doesn't mean they should possess it. Let's avoid creating "Doctor Octopus" in our databases.

Working with unknown hosts

If you're unsure about the host, the wildcard % can be used. However, exposing your database to all hosts is equivalent of leaving your house door open. It's a bad security practice.

GRANT ALL PRIVILEGES ON dbname.* TO 'username'@'%'; -- Open House!


Always go for a specific hostname or IP whenever possible.

Regular audits

Keep your finger on the pulse of access rights. Make regular check-ups of who’s got the keys to your kingdom. Your future self will thank you!