Bug #6280 "show databases" shows all databases
Submitted: 27 Oct 2004 12:30 Modified: 27 Nov 2004 15:02
Reporter: Holger Junge Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.7, 4.1.4, 4.1.5 OS:Linux (Linux, Windows)
Assigned to: CPU Architecture:Any

[27 Oct 2004 12:30] Holger Junge 
Description:
Upgrading Mysql form 3.23, 4,0 or 4.1.4 to MySQL-4.1.7 as the following effect: Already existing MySQL-users can see all databases with "show databases". New created users cannot. "revoke show databases on *.* from USER@'host'" has no consequences (not even after "flush privileges").

--safe-show-database is deprecated (and no longer does anything). How can I prevent users from seeing other databases they have no access rights for?

How to repeat:
Upgrade MySQL with existing  databases.
[27 Oct 2004 12:33] Holger Junge 
How can I prevent OLD users from seeing other databases they have no access rights for?
[27 Oct 2004 12:41] MySQL Verification Team 
Check that you run mysql_fix_privilege_tables script to update privilege table.
[27 Oct 2004 12:45] Holger Junge 
It doesn't make a difference if you run mysql_fix_privileges. Already existing users can still see databases they have no access rights on.
[27 Oct 2004 14:00] MySQL Verification Team 
Check that these users have no global level privileges like LOCK TABLES, CREATE TEMPORARY TABLES privileges.
[27 Oct 2004 15:02] Holger Junge 
A look in table “mysql.user” showed that new users had value “N” for “Lock_tables_priv” and “Create_tmp_table_priv”. Old users had value “Y” in those fields. I changed those privileges for an old user to “N” and flushed privileges. Result: No change. Then I restarted the mysql daemon and voila: The particular user couldn’t see the databases of other users anymore. Many thanks for your help.
[11 Jan 2005 0:02] Kaloyan Tenchov 
Users with any of the CREATE TEMPORARY TABLES or LOCK TABLES global
privileges can see all databases on the server even though they don't have
the SHOW DATABASES privliege (experienced with MySQL Server v4.1.8).

These privileges are automatically given to existing users by the
mysql_fix_privilege_tables script after upgrade from previous versions of
MySQL Server (the database specific analogs of these privileges are not
set by this script).
[14 Feb 2005 22:54] Bugs System 
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".