How to Create a New User and Grant Permissions in MySQL

MySQL is one of the popular open source database management system software that will enable its users to store, organize and retrieve data easily. It has the ability to offer specific permissions for specific users within its tables and databases. In this article, you will be learn how to create a new user and grant permissions in MySQL.

 

Creation of a New User

To create a new user named “tester”within MYSQL shell:

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

You can replace password with your password.

mysql user create 1

 

 

 

 

After creating a user we have to grant them the custom permissions for their proper operations.

The command below will provide the user with necessary access to the databases and tables.

GRANT ALL PRIVILEGES ON * . * TO 'tester'@'localhost';
mysql user create 2



NOTE: the asterisk sign refers to the database and table. The above command will enable the newly created user to read, edit and execute all kinds of tasks within all databases and tables.

Make sure  to reload all the privileges using the command below once user has been granted permission:

FLUSH PRIVILEGES;
flush pr


 

Granting Different User Permissions to User

According to the requirement in different  situations you can restrict and grant the custom permissions.

The option for the common possible permissions that a user need to have are given below:

  1. Create – allows  user to create new tables or databases.
  2. Select – allows users to read through the databases.
  3. Insert – allows users to insert rows into tables.
  4. Update – allows users to update rows of tables.
  5. Delete –  allows  users to delete rows from tables.
  6. Drop – allows users to delete entire tables or databases.
  7. Grant Option – allows users to grant or remove other user’s privileges.
  8. All Privileges – This is an admin/root level privilege that allow users to execute all operations in a database.

 

These options can be used in the following format:

GRANT [type of permission] ON [database name].[table name] TO ‘[tester]’@'localhost’;

You can replace the type of permission and names of database and tables to suit your requirements.

NOTE: Do not forget to  reload the privileges after each update or change in permissions to bring in your changes into effect.

 

Sometimes you need to revoke the already given permission to a user by simply executing the command given below:

REVOKE [type of permission] ON [database name].[table name] FROM ‘[tester]’@‘localhost’;

Similarly, you need to replace the fields with actual values.

likewise, we can remove user from a database, you will be able to delete a user in the same way.

DROP USER ‘tester’@‘localhost’;

The above command will remove the user. You can verify by logging out of the shell and trying to log in again:

quit

mysql -u [username]-p

These were only the simple commands for creating and granting permissions to a new user in MySQL.

KB Admin has written 46 articles