Database Reference
In-Depth Information
Deleting a User Account
The
DROP USER
statementdeletes a user account. Let's look at an example of how this is
done. Suppose Michael Stone tells us that he won't return from his leave of absence be-
cause he has found a new job. We would execute the following to delete his user account:
DROP
USER
'michael_stone'
@
'localhost'
;
TIP
If you use an older version of MySQL (i.e., before 5.0.2), you must first revoke all privileges before you
drop the user account. This requires executing
REVOKE ALL ON *.* FROM '
user
'@'
host
'
and
then
DROP USER '
user
'@'
host
'
.
Some users, like Lena, may have more than one personal user account. So we should check
to see whether there are any other accounts associated with Michael Stone. Unfortunately,
there isn't a
SHOW USERS
statement. Instead, we'll have to check the
user
table in the
mysql
database like this:
SELECT User, Host
FROM mysql.user
WHERE User LIKE '%michael%'
OR User LIKE '%stone%';
+---------------------+-------------+
| User | Host |
+---------------------+-------------+
| mstone | mstone_home |
| michael_zabbalaoui | localhost |
+---------------------+-------------+
It seems that Michael Stone has another user account related to his home IP address. After
confirming that it's his user account, we'll drop it like so:
DROP
USER
'mstone'
@
'mstone_home'
;
When you drop a user account, if the user account is logged in and has active sessions run-
ning, it won't stop the sessions. The active sessions will continue for the user account until
the user exits or they've been idle so long that they end. However, you can shut down a
user's activities sooner. First, you will need to get the process identifier for the session. You
can do this be executing the following:
SHOW PROCESSLIST;
...
