MySQL Bugs: #1964: The SHOW DATABASES privilege doesn't seem to have an effect

Bug #1964	The SHOW DATABASES privilege doesn't seem to have an effect
Submitted:	27 Nov 2003 2:51	Modified:	11 Dec 2003 7:35
Reporter:	Alain Fontaine	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.0.16	OS:	Linux (Linux Redhat 7.3)
Assigned to:	Dean Ellis	CPU Architecture:	Any

Description:
Regardless of whether a user HAS or HAS NOT the SHOW DATABASES privilege, he can execute a SHOW DATABASES query and display the list of existing databases.

How to repeat:
Execute a SHOW DATABASES query with a user that doesn't have the SHOW DATABASES privilege.

This is generally caused by upgrading to 4.0 from 3.23, or some other such process, with the result that all users are given a global privilege across all databases.  Please check your grants and ensure that the user does not in fact have some global privilege that causes this.

Thank you.

Query: 
select user,host from user where SHOW_DB_PRIV='Y' OR SUPER_PRIV='Y'

Result:
user,host
--------------
root,localhost
root,valain
root,192.168.0.%
thierry,%
pdf,localhost
pdf,%
vno,%
vno,localhost

<-- snip -->

Regardless of that, a user "cms" has the right to issue "show databases", although he shouldn't :

<-- snip -->
Query:
select user, SHOW_DB_PRIV, SUPER_PRIV from user where user='cms'

Result:

user,SHOW_DB_PRIV,SUPER_PRIV
----------------------------
cms,N,N
cms,N,N

Hello, I have the same problem - database names are visible (but not accessible) for common users. These users have 'N' everywhere in mysql.user and have allowed only selected databases in mysql.db. 

OS: Linux 2.4.19 (debian stable)
MySQL server: 4.0.13-log (from standard debian package) 

Data were migrated from mysql 3.23.x -> 4.0.x

Petr Grolmus,  
University od West Bohemia

Check SHOW GRANTS FOR user@host; and be quite sure that the user has no privileges on the other databases, please.

Hello,
yeah, probably here it is:
mysql> show grants for voice@localhost;
+------------------------------------------------------------------------------------------------------------------------------------+
| Grants for voice@localhost                                                                                                         |
+------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES ON *.* TO 'voice'@'localhost' IDENTIFIED BY PASSWORD '222cf41b687b67a4' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER ON `web_voice_db`.* TO 'voice'@'localhost'            |
+------------------------------------------------------------------------------------------------------------------------------------+

Petr Grolmus

Please note that in my case, although the mysql user privilege tables clearly show 'N' for the "show databases" privilege, the "SHOW GRANTS" command still shows that the user actually HAS the privilege... which is wrong.

Hello, I have another problem with %subj% - I cannot set show_db_priv to "N", becase my mysql.user table has no attribute show_db_priv :( (MySQL version 4.0.13).

mysql> desc user;
+-----------------+-----------------+------+-----+---------+-------+
| Field           | Type            | Null | Key | Default | Extra |
+-----------------+-----------------+------+-----+---------+-------+
| Host            | char(60) binary |      | PRI |         |       |
| User            | char(16) binary |      | PRI |         |       |
| Password        | char(16) binary |      |     |         |       |
| Select_priv     | enum('N','Y')   |      |     | N       |       |
| Insert_priv     | enum('N','Y')   |      |     | N       |       |
| Update_priv     | enum('N','Y')   |      |     | N       |       |
| Delete_priv     | enum('N','Y')   |      |     | N       |       |
| Create_priv     | enum('N','Y')   |      |     | N       |       |
| Drop_priv       | enum('N','Y')   |      |     | N       |       |
| Reload_priv     | enum('N','Y')   |      |     | N       |       |
| Shutdown_priv   | enum('N','Y')   |      |     | N       |       |
| Process_priv    | enum('N','Y')   |      |     | N       |       |
| File_priv       | enum('N','Y')   |      |     | N       |       |
| Grant_priv      | enum('N','Y')   |      |     | N       |       |
| References_priv | enum('N','Y')   |      |     | N       |       |
| Index_priv      | enum('N','Y')   |      |     | N       |       |
| Alter_priv      | enum('N','Y')   |      |     | N       |       |
+-----------------+-----------------+------+-----+---------+-------+

To the last commenter: you should RTFM. You must run the script that fixes/updates the privilege tables after an upgrade from a 3.xx version of MySQL to a 4.xx version.

indy: SHOW GRANTS plainly shows global privileges for the user (the first grant).

Alain: Still awaiting SHOW GRANTS FOR user@host; for your login as well.

Here we go Dean!

<-- snip -->
mysql> show grants for cms@localhost;
+------------------------------------------------------------------------------------------------------------------+
| Grants for cms@localhost                                                                                         |
+------------------------------------------------------------------------------------------------------------------+
| GRANT CREATE TEMPORARY TABLES, LOCK TABLES ON *.* TO 'cms'@'localhost' IDENTIFIED BY PASSWORD '7b49c1512263c951' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER ON `cms`.* TO 'cms'@'localhost'     |
+------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
<-- snip -->

When I connect as user CMS to localhost and issue a SHOW DATABASES, I get the list of databases ...

<-- snip -->
[alain@valain alain]$ mysql -u cms -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1292 to server version: 4.0.16-standard-log

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

mysql> show databases;
+-----------------------+
| Database              |
+-----------------------+
| ardennes_lu           |
| bug_tracker           |
| clw                   |

...
...
...
<-- snip -->

Notice the first GRANT provides global privileges.  REVOKE those and this will no longer be an issue.  The user is seeing the databases to which he does in fact have access (in this case, all of them).

Indeed, Dean, this was the problem. I'm sorry to mistakenly have reported what seemed a bug, but as other people seem to have been mislead too, this would maybe be a nice "watch out!" in the documentation.