MySQL | DROP USER

In MySQL, managing user accounts is essential for database security and proper access control. The DROP USER statement is a powerful tool used by database administrators to delete user accounts that are no longer required.

This command ensures that all privileges assigned to the user are revoked, preventing them from accessing the MySQL server. In this article, We will learn about MySQL DROP USER in detail by understanding various examples and so on.

MySQL DROP USER

• In MySQL, the DROP USER statement is used to delete an existing user account from the MySQL server.
  
• When a user is dropped, all the privileges assigned to that user are automatically revoked and the user can no longer log into the MySQL server.

Syntax:

DROP USER 'username'@'host';

Parameters:

• username': The name of the user you want to delete.
  
• 'host': The hostname or IP address from which the user is allowed to connect. Use '%' to indicate any host.

Example of MySQL DROP USER

Suppose there are 4 users in the MySQL database server as listed below:

We can drop a single user account as well as multiple user accounts using a single DROP USER statement as shown below:

Example 1: Dropping a single user using the DROP USER statement

Dropping a Single User:

To delete a single user account, such as gfguser1, the following query is executed:

Query:

DROP USER 'gfguser1'@'localhost';

Output:

Explanation: This will remove the user gfguser1 from the MySQL server, and the user will no longer be able to log in or access the database.

Example 2: Dropping multiple users using the DROP USER statement

Dropping Multiple Users:

Multiple user accounts can be deleted simultaneously using a single DROP USER statement. For example, to remove both gfguser1 and gfguser2:

Query:

DROP USER 'gfguser1'@'localhost' , DROP USER 'gfguser2'@'localhost';

Output:

Explanation: This query drops both users in one step, revoking their access to the MySQL serv

Note:

In case the DROP USER statement is executed for an user account when the session for this account is active then it will not effect that account until it's session gets closed. The user account will be dropped after its session gets closed and will not be able to login any more.

Conclusion

The DROP USER statement in MySQL is an essential command for effectively managing and controlling user access within the database. Whether you are removing an individual user or cleaning up multiple accounts, it simplifies the process of user management by revoking privileges and preventing access after the account is dropped. Database administrators can use this command to ensure a more secure and organized database environment.