MySQL Bugs: #9785: SELECT privilege for the whole database is needed to do SHOW CREATE DATABASE

Bug #9785	SELECT privilege for the whole database is needed to do SHOW CREATE DATABASE
Submitted:	9 Apr 2005 15:26	Modified:	2 Dec 2005 20:01
Reporter:	Ondra Zizka	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.10	OS:	Any (All)
Assigned to:	Sergei Glukhov	CPU Architecture:	Any

Description:
SELECT privilege for the whole database is needed  to do SHOW CREATE DATABASE (btw, his prevents phpMyAdmin to list database's tables in 2.6.0). This is not described in the doc, I figured it out after some tests.

There should be some way to allow SHOW CREATE DATABASE without allowing SELECT over all tables in the database.

How to repeat:
Create some database and some tables. Create some user and allow him a SELECT privilege on some tables and other privileges so that he can really do SELECTs. Now do the SHOW CREATE DATABASE. It results in an error: 

SHOW CREATE DATABASE `mydatabase` ;
#1044 - Access denied for user 'selectonlyuser'@'%' to database 'mydatabase'

A note from documentation (but this is not the case):
Note: if a user doesn't have any privileges for a table, the table will not show up in the output from SHOW TABLES or mysqlshow db_name.

Suggested fix:
Allow SHOW CREATE DATABASE when the user can use the database, although he has not the SELECT privilege on it (I hope it is possible to do with the current privilege system).

As it blocks some applications from proper function in one case or leads to potetial lack of security in other case, I change the severity to S3.

Maybe more adequate it is a feature request.

miguel@hegel:~/dbs/4.1$ bin/mysql -uroot    
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 4.1.13-debug-log

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

mysql> create database db4;
Query OK, 1 row affected (0.02 sec)

mysql> use db4;
Database changed
mysql> create table t0 (id int);
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1 (id int);
Query OK, 0 rows affected (0.00 sec)

mysql> grant select on db4.t0 to 'userbug'@'localhost' identified by 'userpass';
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
miguel@hegel:~/dbs/4.1$ bin/mysql -uuserbug -p db4
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 4.1.13-debug-log

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

mysql> select * from t1;
ERROR 1142 (42000): SELECT command denied to user 'userbug'@'localhost' for table 't1'
mysql> select * from t0;
Empty set (0.00 sec)

mysql> show create database db4;
ERROR 1044 (42000): Access denied for user 'userbug'@'localhost' to database 'db4'
mysql>

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/32893

Fixed in 5.1.4-alpha

Noted in 5.1.4 changelog.