Drop user in mysql

In order to drop user in mysql a user issuing drop command must have one of the following two privileges.
-You have the global CREATE USER privilege.
-The DELETE privilege for the mysql database.

1)Before issuing dropping command be sure the user invokes drop command has the privileges by,
mysql> show grants;
+-----------------------------------------------------------------------------------------------------------------------
-----------------+
| Grants for root@localhost
|
+-----------------------------------------------------------------------------------------------------------------------
-----------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29' WITH GRANT OPTION |
+-----------------------------------------------------------------------------------------------------------------------
-----------------+
1 row in set (0.00 sec)

2)
Note that, you might be concerned about REVOKE privilege of the user that you are going to drop specially if your mysql database is before 5.0.2. Before mysql 5.0.2, the granted privileges are not automatically revoked and you must revoke them yourself.

Suppose we want to DROP a user named arju2 in mysql before 5.0.2. Then before dropping arju2 user check the privilege by,
mysql> show grants for arju2@localhost;
+-----------------------------------------------------------------------------------------------------------------------
+
| Grants for arju2@localhost
|
+-----------------------------------------------------------------------------------------------------------------------
+
| GRANT ALL PRIVILEGES ON *.* TO 'arju2'@'localhost' IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29'
|
+-----------------------------------------------------------------------------------------------------------------------
+
1 row in set (0.00 sec)

Revoke privileges by,
mysql> REVOKE ALL PRIVILEGES ON *.* FROM arju2@localhost;
Query OK, 0 rows affected (0.00 sec)

Check again,
mysql> show grants for arju2@localhost;
+--------------------------------------------------------------------------------------------------------------+
| Grants for arju2@localhost |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'arju2'@'localhost' IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29' |
+--------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


However if your mysql database version is 5.0.2 onwards then the granted privileges are automatically revoked.

3) Now drop the user.
Note that dropping user by simply drop user username statement will be failed if user's host is assigned to localhost. If you specify only the user name part of the account name, a host name part of '%' is used.

So following statement will be failed as it interpreted as 'arju2'@'%'.
mysql> drop user arju2;
ERROR 1396 (HY000): Operation DROP USER failed for 'arju2'@'%'

Check the host of the user querying from,

mysql> select user, host from user;
+---------+-----------+
| user    | host      |
+---------+-----------+
| magento | %         |
| root    | 127.0.0.1 |
|         | localhost |
| arju2   | localhost |
| magento | localhost |
| momin   | localhost |
| pma     | localhost |
| richard | localhost |
| rober4t | localhost |
| robert  | localhost |
| root    | localhost |
+---------+-----------+
11 rows in set (0.00 sec)

Then drop it by,
mysql> drop user 'arju2'@'localhost';
Query OK, 0 rows affected (0.00 sec)

Note that if user host is defined as '%' then no need to add host name while dropping user. Below is an example.
mysql> create user test;
Query OK, 0 rows affected (0.00 sec)

mysql> select user, host from user where user='test';
+------+------+
| user | host |
+------+------+
| test | %    |
+------+------+
1 row in set (0.00 sec)

mysql> drop user test;
Query OK, 0 rows affected (0.00 sec)

mysql> select user, host from user where user='test';
Empty set (0.00 sec)

Related Documents
http://arjudba.blogspot.com/2009/05/creating-and-using-database-in-mysql.html
http://arjudba.blogspot.com/2009/05/issuing-mysql-commands.html
http://arjudba.blogspot.com/2009/05/connecting-to-and-disconnecting-from.html

http://arjudba.blogspot.com/2009/05/create-user-in-mysql.html