How To Create MySQL User And Grant Privileges
access_time
hourglass_empty
person_outline

How To Create MySQL User And Grant Privileges

Do you want to understand the basics of MySQL operations? Read on, as this article will show you how to create a user in MySQL. You’ll also learn about several commands to grant privileges, revoke privileges, and delete existing users.

What is MySQL Database?

In order to understand MySQL, you’ll need to know what a database is. It’s a virtual storage where you can save necessary data for building websites and web applications.

Databases can store user details, such as username, password, email addresses, and any type of information that you want to keep for later use.

However, the stored data should be in some kind of order. That’s why we have database management systems. These tools are used to communicate with the database and allow developers to structure, store, dump, and modify the data.

MySQL is one of the most popular brands when it comes to database management systems. This is due to its ease of use and community support. Big-league players such as Facebook and Twitter even use MySQL to some extent.

Before we continue to the next section of the tutorial, make sure that you have installed MySQL. If you haven’t, we have great tutorials on how to install MySQL on Ubuntu and CentOS.

Also, note that we will use the command line to access our Linux VPS as root. You can either use PuTTy (Windows) or terminal (macOS, Linux) and log in with root details from your hosting provider.

Step 1 – Creating a MySQL User and Granting All Privileges

Just as you start using MySQL, you’ll be given a username and a password. These initial credentials will grant you ‘root’ access or full control of all your databases and tables.

But, there are times when you’ll need to give the database access to someone else without granting them full control.

For instance, you hire developers to maintain your databases, but you don’t want to provide them with the ability to delete or modify any sensitive information.

Due to that, you should give them the credentials of a non-root user. This way, you can keep track of what the developers can and cannot do with your data.

In this part, we will explain how to create users in MySQL and grant them all the privileges of your database. In a practical sense, it’s not wise to give full reign to a non-root user. However, it’s still a good entry-point to learn about user privileges.

To create a new MySQL user, follow these steps:

  1. Access command line and enter MySQL server:
    mysql

    The script will return with this result, which verifies that you are accessing MySQL server.

    mysql>
  2. Then, execute the following command:
    CREATE USER 'non-root'@'localhost' IDENTIFIED BY '123';

    non-root’ is the name we’ve given to our new user. And ‘123’ is the password for this user. You can replace these values with your own, inside the quotation marks.

  3. To grant the newly created user all privileges of the database, execute the command:
    GRANT ALL PRIVILEGES ON * . * TO 'non-root'@'localhost';
  4. For changes to take effect immediately flush the privileges by typing in the command:
    FLUSH PRIVILEGES;

And that’s all! Your new user has the same access to the database as the root user.

Step 2 – Granting Specific Privileges for a MySQL User

As stated before, it’s not smart to grant root-level access to a non-root user. Most of the time, you’ll want to give different levels of access to other users. MySQL makes it a breeze granting these privileges. You just need to type this simple command:

GRANT PERMISSION_TYPE ON database_name.table_name TO ‘non-root’@'localhost’;

Remember to specify the database and table names to give the root user fine-grain control over certain data. Plus, replace the ‘permission type’ value with the kind of access you want to grant to the new user.

Here are the most used ones in MySQL:

  • CREATE – enable users to create databases/tables
  • SELECT – permit users to retrieve data
  • INSERT – let users add new entries in tables
  • UPDATE – allow users to modify existing entries in tables
  • DELETE – enable users to erase table entries
  • DROP – let users delete entire databases/tables

Note: The ALL PRIVILEGES permission type in step 1 will allow all of the permissions above.

To use any of the above options, simply replace permission type with the appropriate keyword. To apply multiple privileges, separate them with a comma. For example, we can assign CREATE and SELECT to our non-root MySQL user with this command:

GRANT CREATE, SELECT ON * . * TO 'non-root'@'localhost';

Sometimes, you might face a situation where you need to revoke given privileges from a user. You can do so by entering:

REVOKE PERMISSION_TYPE ON database_name.table_name FROM ‘non-root’@‘localhost’;

For example, to withdraw all privileges for our non-root user we should use:

REVOKE ALL PRIVILEGES ON * . * FROM 'non-root'@'localhost';

Finally, you can entirely delete an existing user by using the following line

DROP USER ‘non-root’@‘localhost’;

Remember, you need to have root access to run any of these commands. Also, be sure to execute FLUSH PRIVILEGES; after making changes.

Conclusion

A database is an essential part of every website and web application because it stores all users’ data. To manage and communicate with it, you will need a database management system. The most popular and recommended one is MySQL, due to its reliability and ease of use.

In this tutorial, you have learned about some basic commands to perform several tasks in MySQL, including:

  • How to create users in MySQL and grant them all privileges
  • How to give specific privileges to users, revoke them, and remove MySQL users

Good luck, and feel free to leave a comment below if you have any questions!

The Author

Author

Domantas G. / @domantas

Domantas leads the content and SEO teams forward with fresh ideas and out of the box approaches. Armed with extensive SEO and marketing knowledge, he aims to spread the word of Hostinger to every corner of the world. During his free time, Domantas likes to hone his web development skills and travel to exotic places.

Related tutorials

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Become a part of Hostinger now!

More in MySQL
Everything You Need to Know About wp-config.php

Close