Create New MySQL User With Password And Grant Permissions

MySQL databases handle all the data for websites and apps. Security is of extreme importance when it comes to handling data. So far we have been using the root user for everything.

But in real websites, the root user is never used as it has access to all databases. This means that if the root user is compromised, it could result in damage to all databases.

So for the security purposes, we should create a new user with password in MySQL.

We create a different databases and grant the database permissions to the new user.

Firstly, we will connect to our MySQL database with root.

mysql -u root -p

Now, let’s try to see how many users are there in mysql by default. The following command lists all the MySQL users –

select user from mysql.user

Create new MySQL user with password

You can see that we have 4 users as of now. We have only interacted with the one named root. The rest of the users are used by the system. Now we will create a new user with the password. We will use the following command for the same –

create user rishabh@localhost identified by 'pass123';

This will create a user named rishabh on our MySQL’s localhost and set the password to pass123. You can make changes to the command as per your name and choice of password.

In the image above, we can see that a new user named rishabh has been created. We can now give any database access to this user.

Let us first try to log in with rishabh’s username and password.

Login was successful and we can see that currently, this user has access to just one database that was created by mysql itself. So practically this user has access to no database. We will now login as our root user and grant this user access to the database that we earlier created. To do this we can use the following command.

grant all privileges on theitstuff.* to rishabh@localhost;

flush privileges;

We have used two commands here. The first one grants all privileges on all tables of the database called theitstuff to the user named rishabh. The next one flushes all privileges options and sets them active. Now we need to actually test if the operation we intended has worked or not.

Check MySQL users permissions

We will now login as rishabh and try to see databases it has access to.

Well Done! You can see that the user we created has access to the database ‘theitstuff‘. Now we can safely use this user in a web application or site. Now even in some worst-case scenario, this user would get hacked, it would only affect one database. It would not affect any other database since this user does not have access to them.

We have usually given all privileges in the example above. But you could give the user specific permissions like select, insert, update delete, etc

Revoke Permissions

Revoking permission is as easy as granting permission. You can use the revoke keyword for the same.

The following command will revoke all privileges of rishabh on the database called theitstuff.

revoke all privileges on theitstuff.* from rishabh@localhost;

Again, you can revoke specific privileges by typing each privilege separated by a comma.

Conclusion

So that’s how we can create a new user with password in MySQL. Security is very important in MySQL databases. It is of extreme importance that only required permissions are granted. This ensures that if a user were to be compromised, it would only affect limited data from the database.

I hope you understand the concept of permissions in MySQL. If you stuck somewhere do let me know in the comment section and I’ll be there for you.