MySQL Bugs: #25071: SHOW TABLES ignores privileges

Bug #25071	SHOW TABLES ignores privileges
Submitted:	14 Dec 2006 13:01	Modified:	4 Feb 2008 19:37
Reporter:	Yahoo Serious (Silver Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S3 (Non-critical)
Version:	5.0.27, 5.1 BK	OS:	Windows (Windows 2000 SP4)
Assigned to:	Georgi Kodinov	CPU Architecture:	Any
Tags:	qc

Description:
Documentation states:
  http://dev.mysql.com/doc/refman/5.1/en/grant.html
"If a user has no privileges for a table, the table name is not displayed when the user requests a list of tables (for example, with a SHOW TABLES statement)."

However, it seems that MySQL 5.1 does allow for it.  (It does not allow for a table commands like "SELECT *").

How to repeat:
- set up a db which is forbidden (or has forbidden tabels) for a certain user
- open a connection to the database with that user
- issue "SHOW TABLES" and look at result set
- issue "SELECT" on one of the forbidden tables and look at the error

Suggested fix:
- fix "SHOW TABLES"

(corrected server version from 5.1.27 to 5.0.27)

I tried this and it worked just fine for me on 5.0.27. The user originally had no grants to the db or any tables (meaning the user couldn't do a use statement on that db, which means show tables wouldnt work anyway). I then granted select on one of the tables in the forbidden db and was able to then execute a use statement for that db. Once I did a SHOW TABLES, it brought up only one table which had any privlidges. 

You might want to post your grant statement.

The user does (only) have a privilege for SHOW DATABASES and SHOW VIEW
'GRANT SHOW DATABASES, SHOW VIEW ON *.* TO 'user1'@'%''

If I leave out the "SHOW VIEW" part, I do not see the forbidden tables anymore.  However, my table VIEWS is completely empty!  So I guess the interpretation of SHOW VIEW is different then I expected.  

So it seems that "SHOW VIEW" implies "SHOW TABLES".
If this is correct, I think this should be documented explicitly.
If this is incorrect, it should be fixed (duh).

test case

Attachment: bug25071.test (application/octet-stream, text), 498 bytes.

Thank you for the report.

Verified as described using attached test case.

The manual says "Database privileges apply to all objects in a given database" ("where applicable" is implied, but SHOW VIEW Is obviously applicable to views). 
And a view is an object in the given database so there is a privilege on view v1.
And views are tables.
Therefore the words "if you have no privileges for a table" in SHOW TABLES do not apply.

However, the MySQL Reference Manual in "11.5.4.25 SHOW TABLES Syntax" make a distinction between "table" and "view". So the interpretation was natural. We will update the manual.

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 products.

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 products.