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.
