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

[7 Apr 2004 8:15] Frederic Briere
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)
[7 Apr 2004 12:38] Alexander Keremidarski
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)
[7 Apr 2004 12:40] Alexander Keremidarski
# 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.
[7 Apr 2004 14:59] Frederic Briere
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>
[29 Apr 2004 12:58] Alexander Keremidarski
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.
[4 May 2004 16:24] Paul DuBois
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
[24 May 2005 8:49] Eugene Radchenko
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.