How to Create a New user and Grant Permissions in MySQL

MySQL is an open-source relational database management software package to store, organize or retrieve data. It has an extensible varieties of management options like user management with specific permissions to databases and tables.

In this guide you are going to learn how to create a new user and grant permissions in MySQL.

Prerequisites

• Install MySQL in your server.
• Root access to your MySQL.

Create a New User in MySQL

To create a new user you need to login to your MySQL shell using the following command.

sudo mysql -u root -p

You will be prompted to enter your root password. Once you have logged in you can execute the following command yo create a new user.

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

Now a new user will get created, but that user wont have any permissions to perform any operations in MySQL.

Grant Permissions to User in MySQL

Here are a list of some common permissions that can be provided to the users in MySQL.

Types of Permissions

• ALL PRIVILEGES — this would allow a MySQL user full access to a database or if no database is selected, global access across the system
• CREATE — allows user to create new tables or databases
• DROP — allows user to delete tables or databases
• DELETE — allows user to delete rows from tables
• INSERT — allows user to insert rows into tables
• SELECT — allows user to use the SELECT command to read through databases
• UPDATE — allow user to update table rows
• GRANT OPTION — allows user to grant or remove other users’ privileges

You can use one of the above privileges to assign to the user. The common permission provided to a user is:

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

This command will provide all permissions for the user over the specific database.

Now you need to reload the privileges for the changes to take effect.

FLUSH PRIVILEGES;

Grant specific permissions

If you wish to provide specific permissions you can use the following syntax.

Assign permissions

GRANT type_of_permission ON database_name.table_name TO 'username'@'localhost';

Once you update or change permissions you need to flush privileges for the changes to take effect.

Remove permissions

REVOKE type_of_permission ON database_name.table_name FROM 'username'@'localhost';

This command will remove the specific permission type of user from the specific database or table.

Review User Permissions

You can view the permissions assigned to the user using the following command.

SHOW GRANTS FOR 'username'@'localhost';

Conclusion

Now you have learned how to create a user in MySQL and grant permissions to update, delete, insert and more.

Thanks for your time. If you face any problem or any feedback, please leave a comment below.