Create a new User and Granting Privileges in MySQL

[Linux Hint]

MySQL is a quick, reliable, and easy to use the database, which is backed by some of the famous organizations such as GitHub, NASA, Netflix, US NAVY, Facebook, Twitter, YouTube, and many more. We usually play with the database under the root user, which can modify anything. But if you are a database administrator in some big organization, you often need to manage users and care about a user’s privileges. In this article, we are going to create a new user in MySQL. We are going to learn about the creation of a user in MySQL and also see how we can grant or revoke privileges to some users of the databases or tables. So, let’s get started.

First of all, let’s have a look at the user’s creation and granting privileges to the users in MySQL.

For creating a new User in MYSQL. You can run the CREATE USER command in the MySQL shell.

CREATE USER 'new_user_name'@'localhost' IDENTIFIED BY 'password';

In this syntax, make sure to replace the new username and password with your desired username and password.

Right after successfully creating the new user, we can grant privileges to this new user. For example, we want to grant the privilege of some databases to this user. We can grant the privileges of the database using the following command.

GRANT ALL PRIVILEGES ON database_name.* TO 'new_user_name'@'localhost';

In the above-given command, we are granting all privileges to all the tables of some database, and an asterisk means that we are granting privileges to all the tables of that database. Make sure to give a specific user name according to your will.

If you want to give the privileges of all the databases and tables to a user. You can run the following command using the “*.*”

GRANT ALL PRIVILEGES ON *.* TO 'new_user_name'@'localhost';

We can grant multiple privileges as well instead of granting all privileges using the following command. For example, to grant only SELECT, INSERT, and DELETE.

GRANT SELECT, INSERT, DELETE ON database_name.* TO 'new_user_name'@'localhost';

Now, if you want to have a look at the privileges of a user, you can see that using the following command.

SHOW GRANTS FOR "user_name"@'localhost';

And in case you want to get the access back from that user. You can revoke or cancel the privileges using the following command

REVOKE ALL PRIVILEGES ON database_name.* FROM 'user_name'@'localhost';

Or instead of revoking the privileges. You may want to remove that user as well. So, you can remove a database user using the following command

DROP USER 'user'@'localhost';

Well, this was all about the creation and deletion of a user in MySQL and different ways of granting the different types of privileges and revoking them.

Conclusion

In this article, we have learned to create a new user, grant some different types of privileges to that user, and we have also learned how we can revoke some privileges from a user. This article contains all the operational tasks related to the user, like creation, deletion, granting, and revoking of privileges.

View the full article