How to delete a user in MySQL or MariaDB

MySQL and MariaDB are two of the most widely used open-source relational database management systems. They are used by developers and administrators to store, manipulate, and manage data in a structured and organized manner. Both systems support the SQL (Structured Query Language) standard, which is used to communicate with the database and perform various operations such as creating, deleting, updating, and retrieving data.

User management in MySQL and MariaDB is an essential aspect of maintaining a secure and well-functioning database environment. Administrators must have the ability to create, modify, and delete user accounts to ensure that only authorized users have access to the system and that they possess the necessary privileges for their tasks.

Deleting a user in MySQL or MariaDB is a straightforward process that involves executing a simple SQL command. However, it is crucial to exercise caution and verify the user's permissions and associated resources before removing them to prevent any unintended consequences. Here is a step-by-step guide on how to delete a user in MySQL or MariaDB:

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;