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: Sergey Glukhov CPU Architecture:Any

[9 Apr 2005 15:26] Ondra Zizka
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).
[13 Jun 2005 23:31] Ondra Zizka
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.
[22 Jun 2005 22:12] Miguel Solorzano
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>
[1 Dec 2005 9:00] Bugs System
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
[1 Dec 2005 9:02] Sergey Glukhov
Fixed in 5.1.4-alpha
[2 Dec 2005 20:01] Paul Dubois
Noted in 5.1.4 changelog.