Bug #5831 REVOKE ALL PRIVILEGES, GRANT OPTION does not revoke everything
Submitted: 30 Sep 2004 20:24 Modified: 22 Oct 2004 21:41
Reporter: Dean Ellis
Status: Closed
Category:Server Severity:S2 (Serious)
Version:4.1.6 OS:
Assigned to: Dean Ellis Target Version:

[30 Sep 2004 20: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 22: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 19:08] Dean Ellis 
Fix is incomplete; re-opening.
[22 Oct 2004 21:41] Dean Ellis 
Fixed in 4.1.7 (cset 1.2137).