Remove a MySQL user

To remove a user and its privileges from a MySQL database you use the DROP USER statement. With MySQL you can create multiple users and assign different permissions. But it's a good idea to not keep unused accounts around.

You can delete a user in MySQL with the following statement.

mysql> DROP USER 'morty'@'localhost';

This would have dropped the user morty from the host localhost.

Keep in mind that this change takes effect after the sessions of the deleted user is closed. If the dropped user has an open session, the user isn't dropped until its session is closed.

Ofcourse you can't just go dropping users left and right. In order to do so you at least need either the global CREATE USER or DELETE privilege in mysql system schema.

You can list your user privileges in MySQL as well. For this you need the SHOW GRANTS statement.

This shows the grants of the currently logged in user.

mysql> SHOW GRANTS;
+---------------------------------------------------+
| Grants for koen@localhost                         |
+---------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'koen'@'localhost' |
+---------------------------------------------------+
1 row in set (0,00 sec)

If the user doesn't have the sufficient rights to drop a user you should be able to drop it as the root user.

One thing that might comes in handy is a query to show the users which are currently present. In order to do so you can query the mysql.user table.

mysql> SELECT User, Host FROM mysql.user;
+---------------+-----------+
| User          | Host      |
+---------------+-----------+
| koen          | localhost |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+