Create New Users And Grant Permissions In MySQL

MySQL databases handle important 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.

Hence, we create different databases and grant permissions accordingly.

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

login to mysql cli ubuntu

Now, let us try to see how many users are there in mysql by default. This can be done with a simple command.

select user from mysql.user

creae mysql user ubuntu

Creating New Users

You can see that we have 4 users as of now. We have only interacted with the one named root. The others are used by the system. Now we will try to create a new user and set a password for the same. 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 in and set the password to pass123. You can make changes to the command as per your name and choice of password.

login mysql user

In the image above, we can see that a new user named rishabh has been created. This user can now be given access to various databases.

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;

grant privileges mysql user ubuntu

We have used 2 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.

Checking if permissions have been granted

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

show databases after permissions

Well Done !, you can see that the user we had created has access to the database that was created. Now we can safely use this user in a web application or site. Now even if in some worst-case scenario, this user would get hacked, it would only affect one database. Other databases would not be affected 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

Revoking All Permissions

Revoking permission is as easy as granting permissions. 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

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 get stuck somewhere do let me know in the comment section and I’ll be there for you.

Leave A Reply

Your email address will not be published.

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. AcceptRead More