MySQL Bugs: #3414: SHOW DATABASES privilege always ignored

Bug #3414	SHOW DATABASES privilege always ignored
Submitted:	7 Apr 2004 8:15	Modified:	4 May 2004 16:24
Reporter:	Frederic Briere	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	4.0.18	OS:	Linux (Linux/Debian)
Assigned to:	Documentation Team	CPU Architecture:	Any

Description:
The SHOW DATABASES privilege appears to always be "on", even when turned off in the mysql.user table.  Therefore, if a user has any privilege turned on for *.*, that user will see every database on the server.

How to repeat:
As root (where tmpuser is a new user):

mysql> grant LOCK TABLES on *.* to tmpuser;
Query OK, 0 rows affected (0.00 sec)

mysql> select Show_db_priv from mysql.user where User = 'tmpuser'\G
*************************** 1. row ***************************
Show_db_priv: N
1 row in set (0.00 sec)

As tmpuser:

mysql> show databases\G
[...]
6 rows in set (0.00 sec)

Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Additional info:

mysql> grant LOCK TABLES on *.* to tmpuser;
Query OK, 0 rows affected (0.11 sec)
 
mysql> select Show_db_priv from mysql.user where User = 'tmpuser'\G
*************************** 1. row ***************************
Show_db_priv: N
1 row in set (0.07 sec)

# mysql-4 -u tmpuser
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7 to server version: 4.0.19-debug-log
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
mysql> show databases;
+----------+
| Database |
+----------+
| test     |
+----------+
1 row in set (0.03 sec)

I.e. tmpuser can see only databases on whiah he has other privileges granted. By default everyone has ALL privileges on test database.

There seems to be a disagreement between what the server does and what the documentation says. 
Section 5.4.3 says: "The SHOW DATABASES privilege allows the account to see database names by issuing the SHOW DATABASE statement."  Therefore, shouldn't turning off Show_db_priv *prevent* someone from issueing a SHOW DATABASE request?  This is certainly how I interpreted it.

I'll refrain from commenting further until we agree on how the server should behave.  Then we'll try to find something else to disagree on. <g>

Ok. I see your point.

SHOW DATABASES Priv allows the account to see *all* databases regerdless of other privileges on these databases.

When this privilege is set to N account can only see databases it has some other privileges.

This is intended behaviour.

I agree it is not explained well in chapter Privileges Provided by MySQL and therefore I change the category of this bug to Documentation.

Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

Additional info:

I've attempted to clarify how this works.  Please see:

For --skip-show-database option:
http://dev.mysql.com/doc/mysql/en/Privileges_options.html

For skip_show_database variable:
http://dev.mysql.com/doc/mysql/en/Server_system_variables.html

For SHOW DATABASES privilege:
http://dev.mysql.com/doc/mysql/en/Privileges_provided.html
http://dev.mysql.com/doc/mysql/en/GRANT.html

For SHOW DATABASES statement:
http://dev.mysql.com/doc/mysql/en/Show_database_info.html

Well, maybe the problem is not with the documentation only.
I have MySQL 4.1.11 server on FreeBSD.
Most users have only CREATE TEMPORARY TABLES and LOCK TABLES global priviledges (though removing LOCK TABLES does not help) and DB priviledges for one DB only. However, SHOW DATABASES shows them all databases on server.