Removing or deleting a MySQL user account is critical for database administrators to maintain the security and efficiency of a MySQL database server.
This is such a critical requirement that MySQL presents a dedicated process of removing user accounts.
Here, it is important to understand that MySQL users are distinct from the user accounts on the underlying Linux system. As such, removing the user account from the Linux system doesn’t necessarily mean that the corresponding MySQL user account becomes dormant or non-functional.
Let’s take a close look at how you can delete MySQL user account in a Linux environment.
Table of Contents
- An Overview of the DROP USER Statement
- Scenario #1: Delete a MySQL User Account
- Scenario #2: Remove Multiple MySQL Users
- Scenario #3: Drop a Connected / Active User
- Conclusion
- FAQs
An Overview of the DROP USER Statement
The native DROP USER MySQL command is an effective way of deleting user accounts, ensuring that the DELETE and USER privileges are appropriately managed within the MySQL server’s grant tables.
When using this statement, It’s essential to employ the EXISTS clause to avoid error messages and ensure the action is executed only if the MySQL user account exists. This check helps prevent unauthorized access and maintains the integrity of the database.
DROP USER Syntax
The fundamental structure for the DROP USER statement is as follows:
mysql> DROP USER username;
where username is MySQL user account name.
Let’s now see this statement in action. We will discuss three critical scenarios where we will use the DROP USER statement to delete a user account.
An important aspect of working with MySQL databases is to list databases so that you can select the right database for further processing and action. We have created a simple tutorial on how you can list databases in MySQL.
Scenario #1: Delete a MySQL User Account
The simplest scenario is to delete a MySQL user account. The process has the following steps:
Step #1: Connect to the MySQL Server
Start by establishing a connection to the MySQL database using the MySQL root user credentials:
#mysql -u root -p
If your Linux user lacks sufficient privileges, use the sudo command to rerun the command and log into the MySQL server.
Step #2: Select the Target User
Run the following statement to list down all MySQL user account names. This is an essential step in identifying the right account:
# SELECT User, Host FROM mysql.user;
For the purpose of this demonstration, we can see that the list contains the MySQLtest user.
Step #3: DROP the User
Now, run the DROP USER statement to remove the user account. The syntax of the statement is as follows:
# DROP USER 'username'@'host';
Remember to replace username with the username of your choice.
Step #4: Confirm Account Deletion
We strongly recommend confirming the user’s removal by reviewing the list of users.
Scenario #2: Remove Multiple MySQL Users
You can remove several user accounts with a single DROP USER statement.
We recommend listing the users to identify the correct usernames to avoid accidental deletion.
Once you have the user names, run the following statement that lists multiple user names, separated by commas and spaces.
mysql> DROP USER 'user1'@'localhost', 'user2'@'localhost', 'user3'@'localhost';
Scenario #3: Drop a Connected / Active User
Did you know that even if you drop an active user from a MySQL database, their privileges will remain intact until their current session ends. The user will lose access to the database only after the session is closed.
As you can imagine, this can create a serious security scenario.
To remedy this, we recommend making sure that the DROP USER statement takes immediate effect. This means you should first terminate the user’s active session before proceeding with the account deletion.
Start by the connection ID of the user you wish to remove.
For this, run the following statement to generate a list of all active processes and their corresponding IDs.
# SHOW PROCESSLIST;
Find the user’s ID and then use the kill statement to terminate the process in MySQL.
# KILL Id_number;
When the user becomes inactive, you can eliminate their account from the MySQL database using:
# DROP USER 'username'@'localhost';
Now that you know how to delete a MySQL user, we strongly recommend you go through our comprehensive tutorial on how to create a user in MySQL and grant them appropriate privileges.
Also Read: How To Rename a Database in MySQL: 3 Easy Methods
Also Read: 3 Simple Methods to Delete Duplicate Rows in MySQL Database
Also Read: How to Import a CSV File Into a MySQL Database in 2 Simple Methods
Conclusion
Understanding the process of removing or deleting a MySQL user account is an essential skill for database administrators, especially when ensuring the security and efficiency of databases hosted on platforms like RedSwitches.
By utilizing the DROP USER statement, you not only revoke the DELETE privilege but also meticulously remove the associated privilege rows from your MySQL tables, maintaining the integrity of your database environment.
We offer the best dedicated server pricing and deliver instant dedicated servers, usually on the same day the order gets approved. Whether you need a dedicated server, a traffic-friendly 10Gbps dedicated server, or a powerful bare metal server, we are your trusted hosting partner.
FAQs
Q. How can I delete a MySQL user account on Linux?
To delete a MySQL user account on Linux, you can use the following command:
# DROP USER 'username'@'localhost';
Q. What is the procedure to remove a MySQL database on Linux via the command line?
You can remove a MySQL database on Linux via the command line using the following SQL command:
# DROP DATABASE database_name;
Q. How do I revoke privileges for a specific user account in MySQL?
To revoke privileges for a specific user account in MySQL, use the following command:
# REVOKE privilege_name ON database_name.* FROM 'username'@'localhost';
Q. Is it possible to remove multiple user accounts and grant privileges in MySQL on Linux?
You can use the DROP USER and GRANT commands for each user account to remove multiple user accounts and grant privileges in MySQL on Linux.
Q. Can I delete or remove a MySQL user account on Linux using the command line?
You can delete or remove a MySQL user account on Linux using the command line by executing the DROP USER statement in the MySQL command-line interface.
