Create a New User and Grant Permissions in MySQL

Create New User and Grant Permissions to MySQL Database Server

In this article, we will explain how to create a new user in MySQL, and grant permissions to that user in MySQL. Creating a new user in MySQL database and granting permissions is an easy task if you follow this tutorial carefully.

1. Log in to MySQL

To log in to MySQL system uses the following command:

mysql -u root –p

By executing this command we tell the MySQL client to log us in with the root user and to prompt us for the user’s password.

2. Creating a new MySQL user

Now we can instruct MySQL to create a new user with the following query:

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

Always use a strong password for all your accounts.

We provide our new user with permissions so that they can read data from all databases on our MySQL server. We do that by typing in the following command:

GRANT SELECT ON *.* TO 'new_user'@'localhost';

In order for our newly set permissions to take effect we need to reload all the privileges:

FLUSH PRIVILEGES;

3. Granting users other types of permissions in MySQL

In this section, we will explore keywords that will allow us to set various types of permissions on the user.

USAGE – gives the user permission to login to the MySQL server(given by default when creating a new user);

SELECT – gives the user permission to use the select command to fetch data from tables;

INSERT – gives the user permission to add new rows into tables;

UPDATE – gives the user permission to modify the existing rows in tables;

DELETE – gives the user permission to delete existing rows from tables;

CREATE – gives the user permission to create new tables or databases;

DROP – gives the user permission to remove existing tables or databases;

ALL PRIVILEGES – gives the user permission to have unrestricted access on a database or the whole system(by using an asterisk in the database position);

GRANT OPTION – gives the user permission to grant or remove other users’ permissions.

GRANT SELECT tells MySQL that the user will have nothing other than permissions to read data from a given database or databases. Granting permissions is typically done in this format:

GRANT 'permission type' ON 'database'.'table' TO 'username'@'host';

We can also instruct MySQL to take away a certain permission from a user in the same format as above by only replacing the keyword GRANT with REVOKE and the keyword TO with FROM:

REVOKE 'permission type' ON 'database'.'table' FROM 'username'@'host';

4. Delete users in MySQL

Deleting users is done by using the DROP command:

DROP USER 'new_user'@'localhost';

Conclusion

MySQL is one of the most popular database management systems. Thanks to this system you can easily manage the database of your application or website. In this article, we have explained how to create a user in MySQL. Grant privileges to this user, as well as some other functions that you can do in MySQL with a new user.