How to Properly Delete a MySQL User

When you install MySQL, it already has a root account with almost unlimited privileges. This account is necessary for creating, modifying, and deleting other database users.

Let's figure out how to delete a MySQL user. This tutorial is also applicable to MariaDB.

Authorizing in MySQL with root

Let's assume you have a cloud server on Hostman with MySQL installed. Log in as the root superuser:

mysql -u root -p password

After the -u argument, enter the username, in this case root. After -p, specify the MySQL root password (this is not the operating system root user's password).

Querying Data

First, check which users exist in the database server to avoid mistakes. In MySQL, you can view users with the command:

SELECT User, Host FROM mysql.user;

The list will display the username and host.

Checking Permissions

Suppose you need to delete a MySQL user 'hostman@localhost'. First, check what permissions this user has:

SHOW GRANTS FOR 'hostman'@'localhost';

The response will be a table with the permissions. In our example, this user has full privileges for the hostman_db database.

Revoking Permissions

You have obtained the list of users and their permissions through MySQL. Next, you need to decide what to do with the user. You don’t always have to delete it; you can start by revoking privileges. The account will still exist, but the permissions will be limited.

To delete user’s permissions in MySQL, execute the command:

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'hostman'@'localhost';

Replace 'hostman'@'localhost' with the actual username in your case.

The response should be:

Query OK, 0 rows affected (0.00 sec)

You can verify everything is correct by requesting the list of privileges again:

SHOW GRANTS FOR 'hostman'@'localhost';

Deleting the MySQL User

If the user is no longer needed, delete it:

DROP USER 'hostman'@'localhost';

The response should be:

Query OK, 0 rows affected (0.00 sec)

You can verify everything is correct by requesting the list of users:

SELECT User, Host FROM mysql.user;

The DROP USER command deletes one or more MySQL users and their privileges. Therefore, revoking privileges is an optional step if you ultimately decide to delete the account completely.

DROP USER does not automatically close open sessions. If you delete a user with an open session, the changes will only take effect once the session is closed. After the session is closed, the user will be deleted, and the next login attempt will fail.