If you’ve been associated with web development or app development in general, you might have heard of databases. Databases are virtual data stores, necessary for building some web applications. They are used to store user details, such as username, password, email addresses etc. Basically, any type of information that you want to keep for later use can be stored in a database.
Of course, the data that you store has to have some semblance of order. This is where database management systems come into play. These systems are used to communicate with the database and allow developers to structure, store, dump and modify data.
MySQL is one of such database management systems. It is one of the more popular ones, due to its ease of usage and community support. In fact, its pervasiveness has even attracted big-league players such as Facebook and Twitter, who use it to some extent.
In this tutorial we will overview MySQL basics and learn how to create a user in MySQL. We will also learn how to grant privileges for MySQL users. We will use command line for this tutorial.
Before you begin this guide, you’ll need the following:
- MySQL Installed on your machine. Tutorial how to install it on CentOS VPS can be found here.
Just as you start using MySQL, you will be given a username and a password. These initial credentials grant you ‘root access’. The root user has full access to all the databases and tables within those databases.
But often times, you need to give the access of the database to someone else without granting them full control. For instance, companies who hire developers to maintain databases, but do not want to give them the ability to delete or modify any sensitive information, will often give them credentials of a non-root user. This way, the company can keep control of what their developers can and cannot do with the data.
Creating a new user is fairly simple in MySQL. We will show you how to create a new MySQL user, and how to grant them all privileges of your database. It is not wise, in a practical sense, to give full reign to a non-root user, but it is a good entry-point to learn about user privileges. To create a new user, follow these steps:
- Access command line and enter MySQL server:
If you want to practice these commands on your VPS server, you will need to establish SSH connection first.
- Execute the following command:
CREATE USER 'non-root'@'localhost' IDENTIFIED BY '123';
In this command, ‘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.
- Simply creating this new user isn’t enough. You have to grant them privileges. To grant the newly created user all privileges of the database, execute the following command:
GRANT ALL PRIVILEGES ON * . * TO 'non-root'@'localhost';
- For changes to take effect immediately flush the privileges by typing in the following command:
And that’s it! Your new user has the same access to the database as the root user.
As stated before, it is not smart to grant root level access to a non-root user. Most of the time, you will want to give different levels of access to different kinds of users. MySQL makes it a breeze granting these privileges, by the following simple command:
GRANT [permission type] ON [database name].[table name] TO ‘non-root’@'localhost’;
You simply have to replace the ‘permission type’ value with the kind of permission you want to grant the new user. You also have to specify database and table names, which gives the root user fine-grain control over granting privileges. Similar to the example above, ‘non-root’ is the username, so you’re free to replace it with your own choice.
MySQL has quite a few permission types, some of which are described below:
- CREATE – Enables users to create databases/tables
- SELECT – Enables users to retrieve data
- INSERT – Enables users to add new entries in tables
- UPDATE – Enables users to modify existing entries in tables
- DELETE – Enables users to delete table entries
- DROP – Enables users to delete entire databases/tables
To use any of these options, simply replace [permission type] with the appropriate keyword. To apply multiple privileges, separate them with a command like this. For example, we can assign CREATE and SELECT privileges for our non-root MySQL user with this command:
GRANT CREATE, SELECT ON * . * TO 'non-root'@'localhost'; code>
Of course, you might face a situation where you need to revoke given privileges from a user. You can do this by using the following command:
REVOKE [permission type] ON [database name].[table name] FROM ‘non-root’@‘localhost’;
For example, to revoke all privilegies 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 command:
DROP USER ‘non-root’@‘localhost’;
Remember, to execute any of these commands, you need to have root access. Also, be sure to execute FLUSH PRIVILEGES after any changes made to the privileges.
In this tutorial, we learned some of the basics about MySQL privileges and how to create a new MySQL user. Specifically, we learned about:
- New user creation and granting them all privileges
- Granting specific privileges to users, as well as revoking those privileges and deleting users
Granting privileges may seem like a chore when developing web applications, but it is a vital security measure for database management and maintenance. Almost every modern application relies on databases, making it essential for developers to learn granting/revoking MySQL database privileges.