MySQL Bugs: #1234: SHOW DATABASES shows databases even though no privilege given

Bug #1234	SHOW DATABASES shows databases even though no privilege given
Submitted:	9 Sep 2003 6:00	Modified:	9 Sep 2003 10:34
Reporter:	Lenar Lõhmus	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.0.13	OS:	Linux (Debian/Linux)
Assigned to:		CPU Architecture:	Any

Description:
Considering this: 
 
mysql> show grants for xxx@localhost; 
+------------------------------------------------------------------------------------+ 
| Grants for xxx@localhost                                                           | 
+------------------------------------------------------------------------------------+ 
| GRANT SELECT ON *.* TO 'xxx'@'localhost' IDENTIFIED BY PASSWORD '663c5dd53dae4ed0' | 
+------------------------------------------------------------------------------------+ 
 
Now I log on as 'xxx', and issue SHOW DATABASES query. 
 
Every database on system is shown in result. 
 

How to repeat:
Instructions given in description.

Even when trying skip-show-database in server my.cnf: 
 
SHOW VARIABLES; 
... 
| skip_show_database              | ON 
... 
 
SHOW DATABASES lists every database existing (logged in as xxx mentioned before).

I cannot repeat it:

show databases;
ERROR 1227: Access denied. You need the SHOW DATABASES privilege for this operation

are you sure you are REALLY logged in as xxx ?
check SELECT CURRENT_USER();

Seems to work when rights granted to only one database. I did GRANT SELECT *.* which gives some
kind of access to every database. So mysql was working the right way when giving me all database
names.

But there seems to be a real problem nevertheless.
I try to describe it.

I've always granted rights to users with this command:
GRANT ALL PRIVILEGES ON test.* TO test@localhost IDENTIFIED BY 'test';

Now since then I've upgraded to 4.0.x series and SHOW GRANTS shows this for those 'historic' users:
+-------------------------------------------------------------------------------------------------------------------+
| Grants for test@localhost |
+-------------------------------------------------------------------------------------------------------------------+
| GRANT CREATE TEMPORARY TABLES, LOCK TABLES ON *.* TO 'test'@'localhost' IDENTIFIED BY
PASSWORD '' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER ON
`test`.* TO 'test'@'localhost' |
+-------------------------------------------------------------------------------------------------------------------+

Which indicates that since users have some kind of access on *.*, then maybe they can see all
databases? There really isn't a SHOW DATABASES privilege, but they _can_ see. And they are logged in
as themselves. The upgrade conversion fix_piv_tables or whatever it was ... didn't work the way I
would've expected.

Now in present when I gant a user privileges with statement I mentioned above I get this result from
SHOW GRANTS:
+----------------------------------------------------------------------------------------+
| Grants for test@localhost |
+----------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'localhost' IDENTIFIED BY PASSWORD '' |
| GRANT ALL PRIVILEGES ON `test`.* TO 'test'@'localhost' |
+----------------------------------------------------------------------------------------+

Which seems to be exactly what I want. And SHOW DATABASES shows only 'test' if that's created.

So IMHO something is fishy anyway.

Maybe give me advice how to easily convert those 3.23.x-age users to this new representation?