How to delete a user in MySQL or MariaDB

Managing users is a crucial aspect of database administration. Over time, there may be users in the database that no longer require access or are perhaps obsolete due to staff changes or project shifts.

MySQL and MariaDB provide a simple syntax for adding, updating, and deleting users. If you need to revoke access to a database, one of the steps may involve deleting a user. It's crucial to remember that removing a user will also revoke all the privileges associated with that user. So, make sure the user is not essential for other applications or processes.

Before executing any commands, ensure that you have administrative privileges in your MySQL or MariaDB installation. You also need to be logged in to the server hosting the database to execute the following steps.

1. Log in to your MySQL or MariaDB server with an administrative account using the mysql client.

   $ 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
   
   Copyright (c) 2000, 2022, Oracle and/or its affiliates.
   
   Oracle is a registered trademark of Oracle Corporation and/or its
   affiliates. Other names may be trademarks of their respective
   owners.
   
   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
   
   mysql>

2. Select the appropriate database.

   USE mysql;

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

3. Confirm the user exists.

   SELECT User, Host FROM user;

4. Look for the user you want to delete in the resulting output.
5. Revoke all privileges to prevent any potential issues.

   REVOKE ALL PRIVILEGES ON *.* FROM 'username'@'hostname';

6. Revoke any additional grants if the user has the ability to grant privileges to other users.

   REVOKE GRANT OPTION ON *.* FROM 'username'@'hostname';

7. Delete the user.

   DROP USER 'username'@'hostname';

8. Flush privileges to ensure the changes take effect.

   FLUSH PRIVILEGES;

9. Verify the user has been deleted.

   SELECT User, Host FROM user;

10. Exit the MySQL or MariaDB prompt.

    EXIT;