Bug #5831 REVOKE ALL PRIVILEGES, GRANT OPTION does not revoke everything
Submitted: 30 Sep 2004 18:24 Modified: 22 Oct 2004 19:41
Reporter: Dean Ellis Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.6 OS:
Assigned to: Dean Ellis CPU Architecture:Any

[30 Sep 2004 18:24] Dean Ellis
Description:
Per manual:

"To make it easy to revoke all privileges, MySQL 4.1.2 has added the following syntax, which drops all database-, table-, and column-level privileges for the named users:

REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ..."

This does not work when there are many privileges to revoke; repeated executions of the REVOKE statement revoke a few of the privileges until they are all gone.

How to repeat:
USE test;
DROP TABLE IF EXISTS t1, t2, t3, t4, t5, t6, t7, t8, t9, t10;
CREATE TABLE t1 ( a int );
CREATE TABLE t2 ( a int );
CREATE TABLE t3 ( a int );
CREATE TABLE t4 ( a int );
CREATE TABLE t5 ( a int );
CREATE TABLE t6 ( a int );
CREATE TABLE t7 ( a int );
CREATE TABLE t8 ( a int );
CREATE TABLE t9 ( a int );
CREATE TABLE t10 ( a int );
GRANT INSERT, UPDATE, DELETE ON test.t1 TO testuser;
GRANT INSERT, UPDATE, DELETE ON test.t2 TO testuser;
GRANT INSERT, UPDATE, DELETE ON test.t3 TO testuser;
GRANT INSERT, UPDATE, DELETE ON test.t4 TO testuser;
GRANT INSERT, UPDATE, DELETE ON test.t5 TO testuser;
GRANT INSERT, UPDATE, DELETE ON test.t6 TO testuser;
GRANT INSERT, UPDATE, DELETE ON test.t7 TO testuser;
GRANT INSERT, UPDATE, DELETE ON test.t8 TO testuser;
GRANT INSERT, UPDATE, DELETE ON test.t9 TO testuser;
GRANT INSERT, UPDATE, DELETE ON test.t10 TO testuser;
SHOW GRANTS FOR testuser;
REVOKE ALL PRIVILEGES, GRANT OPTION FROM testuser;
SHOW GRANTS FOR testuser;
REVOKE ALL PRIVILEGES, GRANT OPTION FROM testuser;
SHOW GRANTS FOR testuser;
REVOKE ALL PRIVILEGES, GRANT OPTION FROM testuser;
SHOW GRANTS FOR testuser;
DROP TABLE IF EXISTS t1, t2, t3, t4, t5, t6, t7, t8, t9, t10;
DROP USER testuser;

Suggested fix:
n/a
[1 Oct 2004 20:15] Dean Ellis
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Fix will be in 4.1.6. Pushed in changeset 1.2034.
[18 Oct 2004 17:08] Dean Ellis
Fix is incomplete; re-opening.
[22 Oct 2004 19:41] Dean Ellis
Fixed in 4.1.7 (cset 1.2137).