As you probably know, MySQL is an open source database management system software that will enable its users to store, organize and retrieve data easily. It has the ability to offer nuanced permissions for specific users within its tables and databases. In this tutorial, you will learn how to create a new user and grant permissions in MySQL.
Creation of a New User
A new user can be created within the MySQL shell using the following command:
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
You can replace the username and password with actual values.
Now that we have created a user, let’s get down to granting custom permissions. Without proper permissions the new user will not be able to perform any operations.
Below is the command which will provide the user with necessary access to the databases and tables.
GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';
Here, 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.
After setting up the new user with required permissions, you have to reload all the privileges using the command below:
Granting Different User Permissions to User
There may be situations where you would require more restrictions and want to create users with custom permissions. Below is a rundown of the common possible permissions that a user need to have:
- Create – This option allows the user to create new tables or databases.
- Select – This allows the users to read through the databases.
- Insert – Insert option allows the users to insert rows into tables.
- Update – This option allows users to update rows of tables.
- Delete – This will allow the users to delete rows from tables.
- Drop – Drop option will allow users to delete entire tables or databases.
- Grant Option – This will allow the users to grant or remove other user’s privileges.
- All Privileges – This option will 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 ‘[username]’@'localhost’;
Replace the type of permission and names of database and tables to suit your requirements. Make sure you reload the privileges after each update or change in permissions to bring in your changes into effect.
Suppose if you want to revoke the given permission, you can do that by executing the command below:
REVOKE [type of permission] ON [database name].[table name] FROM ‘[username]’@‘localhost’;
Similarly, you need to replace the fields with actual values.
Now, let’s see how to remove a user from a database. As with dropping databases and tables using Drop command, you will be able to delete a user in the same way.
DROP USER ‘username’@‘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 the simple commands for creating and granting permissions to a new user in MySQL.