MySQL Commands You Need To Know To Create A New User

Working with our root user on all our databases is not a smart move and we all know it. We are going to learn how to use the command line to create a new MySQL user and grant him all the permissions and privileges needed to work independently.

I am assuming you already have MySQL installed globally and the service is running. If that's the case you will have a root user to login with. Let's use him to create our new user!

MySQL Server Connection

Let's start by opening our terminal and connecting to our MySQL server!

We can connect by simply running:

mysql

To connect with the default user

OR

To specify a certain user, we can run:

mysql -u root -p 

Where -u stands for user and its value is root and -p stands for password so we will be prompted to enter the password for the specified user.

Create MySQL User

Our next step now, is what all this is about, creating the user. To do that we will gracefully run:

CREATE USER 'rana'@'localhost' identified by 'Bad@Password1';

This will give us a new user, who can connect to the server using the user rana and the password Bad@Password1

But if we tried to connect using our new user, we will find out that our user doesn't have access on anything and pretty much as good as dead. To fix that we need privileges!

Grant MySQL User Privileges

It's time to grant our user privileges to let him play around like the other kids!

If we want our user to have all the privileges to manipulate everything we can use:

GRANT ALL ON *.* TO 'rana'@'localhost';

The first asterisk in *.* stands for all the databases we have and the second one stands for all the tables we have within the database. We can specify which database and which table exactly we want to grant the privileges on. Also, all here gathers all the privileges available in the official MySQL docs

What if we want to create a user who is dedicated to a certain database for a certain project?

Let's assume we have a database called amazingdb! If we want our user to be able to play around with all the grants on this database, we will run:

GRANT ALL ON amazingdb.* to 'rana'@'localhost';

If we want the user to only have some grants like the basic CRUD operations grants, we can go for:

GRANT INSERT, SELECT, UPDATE, DELETE
ON amazingdb.* 
TO 'rana'@'localhost';

Finally, if we want to specify only one grant we will use:

GRANT INSERT ON amazingdb.* TO 'rana'@'localhost';

That's it! Now we can create all the users we need and give them all the privileges they need!