October 25, 2019
October 25, 2019
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.
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.
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.
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:
The script will return with this result, which verifies that you are accessing MySQL server.
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.
GRANT ALL PRIVILEGES ON * . * TO 'non-root'@'localhost';
And that’s all! Your new user has the same access to the database as the root 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:
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.
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:
Good luck, and feel free to leave a comment below if you have any questions!