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.
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.
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:
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:
- Create – allows user to create new tables or databases.
- Select – allows users to read through the databases.
- Insert – allows users to insert rows into tables.
- Update – allows users to update rows of tables.
- Delete – allows users to delete rows from tables.
- Drop – allows users to delete entire tables or databases.
- Grant Option – allows users to grant or remove other user’s privileges.
- 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.