How to delete a user in MariaDB or MySQL

Managing users in a database is an essential part of administration. Users that are no longer required must be removed to maintain security and reduce unnecessary access. Over time, certain users might become obsolete due to organizational changes or shifts in project requirements.

Deleting a user in MariaDB or MySQL involves directly removing the user account from the database. When a user is deleted, all associated privileges are revoked immediately. Ensure that the user is not critical to any ongoing processes before executing the removal.

Administrative access is required to delete users in MariaDB or MySQL. Log in with appropriate privileges to manage user accounts, and make sure you are connected to the correct database server. Proceed with caution when deleting any users.

1. Log in to the server using an administrative account.

   $ mysql -u root -p
   Enter password:
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 10
   Server version: 8.0.30 Source distribution

2. Select the database storing user information.

   mysql> USE mysql;
   DATABASE changed

   By default, MySQL and MariaDB store user information in the 'mysql' database.

3. Confirm the user exists by checking the user list.

   mysql> SELECT USER, Host FROM USER;
   +------------------+-----------+
   | USER             | Host      |
   +------------------+-----------+
   | root             | localhost |
   | testuser         | localhost |
   +------------------+-----------+

4. Revoke all privileges from the user.

   mysql> REVOKE ALL PRIVILEGES ON *.* FROM 'testuser'@'localhost';
   Query OK, 0 ROWS affected (0.01 sec)

5. Remove any granted privileges the user may have assigned to others.

   mysql> REVOKE GRANT OPTION ON *.* FROM 'testuser'@'localhost';
   Query OK, 0 ROWS affected (0.01 sec)

6. Delete the user account from the database.

   mysql> DROP USER 'testuser'@'localhost';
   Query OK, 0 ROWS affected (0.01 sec)

7. Flush privileges to apply the changes.

   mysql> FLUSH PRIVILEGES;
   Query OK, 0 ROWS affected (0.00 sec)

8. Verify the user has been removed.

   mysql> SELECT USER, Host FROM USER;
   +------------------+-----------+
   | USER             | Host      |
   +------------------+-----------+
   | root             | localhost |
   +------------------+-----------+

9. Exit the database server.

   mysql> EXIT;
   Bye