MySQL Bugs: #19239: DROP USER doesn't remove privileges immediately

Bug #19239	DROP USER doesn't remove privileges immediately
Submitted:	21 Apr 2006 1:31	Modified:	17 May 2006 13:21
Reporter:	Kai Voigt	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S2 (Serious)
Version:	5.1.10-BK, 5.1.7-beta-log	OS:	Linux (Linux, MacOSX)
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
After a "DROP USER" statement, a connected user can still execute queries. REVOKE statements however take immediate effect.

The manual claims that "DROP USER" doesn't disconnect users, but it should immediately remove all privileges.

Having the user still connected is one thing, but keeping privileges is both confusing and IMHO a security issue.

How to repeat:
In window 1, connect as root, create a new user and grant some privileges:

mysql> CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'testpw';
mysql> GRANT SELECT ON world.* TO 'testuser'@'localhost';
mysql> GRANT DELETE ON world.* TO 'testuser'@'localhost';

In window 2, connect as the new user and verify that the privileges work.

mysql> SELECT COUNT(*) FROM world.City;
(works)
mysql> DELETE FROM world.City where Id=123;
(works)

In window 1, revoke DELETE privilege:

mysql> REVOKE DELETE ON world.* FROM 'testuser'@'localhost';

In window 2, verify that the revoked PRIVILEGE takes immidiate effect.

mysql> DELETE FROM world.City where Id=1234;
ERROR 1142 (42000): DELETE command denied to user 'testuser'@'localhost' for table 'city'

In window 1, drop the user.

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

In window 2, verify that SELECT still works.

mysql> SELECT COUNT(*) FROM world.City;
(works)

Verified just as described with 5.0.10-BK on Linux. 

I am not sure what claims in the manual do you mean (I had not found exact claims of that kind at http://dev.mysql.com/doc/refman/5.1/en/drop-user.html), but the following is definitely a bug (after deliting user, in his session):

mysql> show grants;
ERROR 1141 (42000): There is no such grant defined for user 'testuser' on host 'localhost'
mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

This is by design, global level privileges aren't removed from an active session.
Perhaps it's not documented properly ?

The effect of dropping privileges, or rather the effect of
privilege changes in general, is covered here:

http://dev.mysql.com/doc/refman/5.0/en/privilege-changes.html