Bug #20214 Incorrect error when user calls SHOW CREATE VIEW on non privileged view
Submitted: 1 Jun 2006 23:20 Modified: 27 Aug 2006 20:47
Reporter: Erica Moss Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:WIN -5.0.21 LIN 5.0.22 OS:Microsoft Windows (win32 - XP SP2, Fedora core 5)
Assigned to: Tatiana Azundris Nuernberg CPU Architecture:Any

[1 Jun 2006 23:20] Erica Moss
Description:
If a user calls SHOW CREATE VIEW on a view for which he has not been granted SHOW VIEW, the returned error states that SELECT is required.  This is demonstrated below with a short mysql-test-run script.

How to repeat:
# CODE
connect (root, localhost, root,,);
DROP DATABASE IF EXISTS privDB;
CREATE DATABASE privDB;
use privDB;

CREATE TABLE t1 (c1 INT);
CREATE VIEW v1 AS SELECT * FROM t1;
CREATE VIEW v2 AS SELECT * FROM t1 WHERE c1=55;

GRANT SHOW VIEW ON privDB.v1 TO 'show_view'@'localhost'
        IDENTIFIED BY 'show_view';

connect (show_view, localhost, show_view, show_view,);

SHOW CREATE VIEW privDB.v2;

#clean-up
connection root;
DROP VIEW privDB.v1, privDB,v2;
DROP TABLE privDB.t1;
DROP DATABASE privDB;
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'show_view'@'localhost';
DROP USER 'show_view'@'localhost';
 
# OUTPUT
mysqltest: At line 15: query 'SHOW CREATE VIEW privDB.v2' failed: 1142: SELECT command denied to user 'show_view'@'localhost' for table 'v2'
[1 Jun 2006 23:22] Erica Moss
forgot version info
[1 Jun 2006 23:27] Erica Moss
NOTE: This is very similar and certainly related to 20136
[3 Aug 2006 12:58] 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/commits/10006

ChangeSet@1.2230, 2006-08-03 14:58:13+02:00, tnurnberg@mysql.com +2 -0
  Bug#20214: Incorrect error when user calls SHOW CREATE VIEW on non privileged view
  
  "A SELECT privilege on a view is required for SHOW CREATE VIEW and it will stay
  that way because of compatibility reasons." (see #20136)
  
  a test case to illustrate how the ACLs work in this case (and ensure they will continue
  to do so in the future)
[7 Aug 2006 4:12] Tatiana Azundris Nuernberg
expected behaviour, see Bug#20136
documented for 5.0/5.1 in the course 20136.

test to ensure this behaviour pushed to 5.0-maint (5.0.25)

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php
[17 Aug 2006 16:23] Chad MILLER
Available in 5.0.25 and soon in 5.1.12-beta.