In this tutorial, we will see how to manage MySQL database users. I will show you how to create a user, assign it to the database, remove it from the database, and delete a created user. Then, we will see how to change the database user password.
Table of Contents
Create a MySQL database user
First, login to cPanel, the control panel we use for all of our hosting services.
The tool we are interested in is MySQL Databases which allows us to create a database and associate users to it. You can find it on the main page of cPanel in the Databases section.
After creating a database, the first step is to create a user. To do this, cPanel provides us with a single function, MySQL Databases, which in addition to showing us the list of databases present, also allows us to create and manage MySQL database users.
To create a new user, locate the MySQL Users - Add New User section. To proceed, you will need to fill in the necessary fields:
Username: enter your username and note that it will consist of a prefix and the name you enter (prefix_username).
Password: Enter a password or use the Password Generator button to create a random password. You can use the advanced options of this function built into cPanel to choose the length and characteristics of the password. Make sure you remember it, check the box I have copied this password in a safe place, and proceed by clicking on Use password.
After re-entering the password in the "Password (again)" field, you can click Create User to finalize the operation.
Assign the user to a database
After creating the user, you need to associate it with the database and give it the appropriate permissions.
If you have just created the user, you are ready to proceed, otherwise, log in again to MySQL Databases from cPanel.
The section that interests us is Add user to database. You can find it right after the section that allowed us to create the new database user.
In this section, you can see two drop-down menus, the first, User, allows us to select the user, and the second Database, lets us choose the database to associate it with. After selecting user and database, click on Add.
On the next screen, you will need to assign user privileges. Check the box next to "ALL PRIVILEGES" and click on the Make changes button.
A notification like this will notify you that the operation was completed successfully.
Remove a user from the database
If after assigning a user to the database, you may decide to remove it, you can follow these steps.
From MySQL Databases, scroll to the Current Databases section and locate the database you want to edit.
You will see a column indicating the privileged users assigned to each database. To remove a user from the database, click on the trash can icon next to the user, you do not want to be associated with it anymore, as you can see in this screenshot:
On the next screen, you will need to confirm that you want to revoke the user's permissions. Click on Revoke user privileges from database to confirm.
Delete a database user
We just saw how to revoke a user's access to the database. Now let's see how to permanently delete a user. As always, go to the MySQL Databases section and scroll down to the Current Users section.
Here you will see a list of all MySQL database users created. To delete a user just click on the Delete button next to the username, as I show you on this screen:
You will then have to confirm that you want to delete the user by clicking on the Delete user button.
Change database username
The Current Users section of the cPanel MySQL Databases function also allows us to change the name of a user associated with the database.
To do this, we must click on Rename next to the name of the user we intend to modify from the list of all MySQL database users created.
In the front window that pops up, you will need to enter the new username (in this example user2) and then confirm by clicking on Proceed.
Change database user password
After creating a user, you can change the password you assigned it during the creation phase. To do this, simply click on Change password next to the corresponding username from the list of all MySQL database users.
In the new window, you will need to enter the new password you have chosen for the database user and repeat it in the "Password (again)" field. Make sure you copy the password in a safe place before confirming the operation by clicking on the Change password button below.
Create a password with the generator
cPanel allows us to use a password generator to create a random password of letters and numbers. You can manage the length of the password and its characteristics (use of numbers or special characters) by clicking on Advanced Options from the Password Generator, as you can see here: