Bug #3644 GRANT/REVOKE for SHOW DATABASES causes connect failure
Submitted: 4 May 2004 1:51 Modified: 4 May 2004 22:15
Reporter: Paul Dubois Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.2 OS:
Assigned to: CPU Architecture:Any

[4 May 2004 1:51] Paul Dubois
Description:
I don't know if this problem is actually specific to SHOW DATABASES,
but that's the privilege for which I observe this problem.

Summary: Revoking SHOW DATABASES from user causes user
not to be able to connect until FLUSH PRIVILEGES. re-granting
the SHOW DATABASE privilege then causes same problem until
FLUSH privilege.

Note: "m40102" is my alias for connecting to my 4.1.2 server.

How to repeat:
1) (Window 1) I connect as myself and list my privileges:

% m40102
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.2-alpha-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show grants;
+--------------------------------------------------------------------------------------
--------------------------------+
| Grants for paul@localhost                                                                                            |
+--------------------------------------------------------------------------------------
--------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'paul'@'localhost' IDENTIFIED BY PASSWORD 
'*16BBF8FDE9FB9CBDD2141FA3F6239C267C4FA466' |
+--------------------------------------------------------------------------------------
--------------------------------+
1 row in set (0.00 sec)

mysql> quit
Bye

Okay, so I have all privileges.

2) (Window 2) In another window, I connect as root and revoke my SHOW DATABASE
privilege:

% m40102 -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.2-alpha-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> revoke show databases on *.* from paul@localhost;
Query OK, 0 rows affected (0.00 sec)

3) (Window 1) I try to connect as myself again:

% m40102
ERROR 1045 (28000): Access denied for user: 'paul'@'localhost' (Using password: YES)

Hm, it fails.

4) (Window 2) Still connected as root, flush the privileges:

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

5) (Window 1) I try to connect as myself again:

% m40102
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 4.1.2-alpha-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> quit

Hm, now it works.

At this point, I can cause the same problem as follows:

window 2) re-grant SHOW DATABASES
window 1) try to connect; the attempt fails
window 2) FLUSH PRIVILEGES
window 1) try to connect; the attempt succeeds.

I've repeated this problem on a couple of machines running Gentoo Linux and Panther.
[4 May 2004 22:15] Michael Widenius
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:

This is same as bug #3404.

If you did a grant for a user, the password was reset until FLUSH PRIVILEGES.
The fix will be in 4.1.2