MySQL Bugs: #20136: SHOW CREATE VIEW requiring a SELECT GRANT when not needed

Bug #20136	SHOW CREATE VIEW requiring a SELECT GRANT when not needed
Submitted:	30 May 2006 5:39	Modified:	25 Aug 2006 16:12
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:	Windows (win32 - XP SP2, Fedora core 5)
Assigned to:	Georgi Kodinov	CPU Architecture:	Any

Description:
The mysql-test script below describes a simple example where a VIEW can be created by as user having only a CREATE VIEW grant, with no SELECT  grant because no tables are being read.

However when another user having only SHOW VIEW attempts a SHOW CREATE VIEW statement on that same view, an error is thrown stating that SELECT privilege is required for this action.

How to repeat:
connect (root, localhost, root,,);

CREATE DATABASE privDB;

GRANT CREATE VIEW ON privDB.* TO 'create'@'localhost'
       IDENTIFIED BY 'create';
GRANT SHOW VIEW ON privDB.* TO 'show'@'localhost'
       IDENTIFIED BY 'show';

connect (create, localhost, create, create,);

CREATE VIEW privDB.v1 AS
        SELECT CURRENT_USER();

connect (show, localhost, show, show,);

# error because user doesn't have a SELECT GRANT
--error 1142
SHOW CREATE VIEW privDB.v1;

# cleanup
connection root;
DROP VIEW IF EXISTS privDB.v1;
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'create'@'localhost';
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'show'@'localhost';
DROP USER 'create'@'localhost';
DROP USER 'show'@'localhost';
DROP DATABASE privDB;

# NOTE: in this script two SHOW VIEW users are created
# one ON privDB.v1, and the other ON *.*
# The first more restrictive grant will not require SELECT to execute
# but the second user with the global priv will not be able to execute 
# without SELECT.  The same thing happens if the grant is ON privDB.*

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;

# USER 1
GRANT SHOW VIEW ON privDB.v1 TO 'show_view'@'localhost'
        IDENTIFIED BY 'show_view';
# USER 2
GRANT SHOW VIEW ON *.* TO 'show_view_db'@'localhost'
        IDENTIFIED BY 'show_view_db';

connect (show_view, localhost, show_view, show_view,);
SHOW CREATE VIEW privDB.v1;

connect (show_view_db, localhost, show_view_db, show_view_db,);
SHOW CREATE VIEW privDB.v1;

#clean-up
connection root;
DROP VIEW privDB.v1;
DROP TABLE privDB.t1;
DROP DATABASE privDB;
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'show_view'@'localhost';
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'show_view_db'@'localhost';
DROP USER 'show_view'@'localhost', 'show_view_db'@'localhost';

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

A SELECT privilege on a view is required for SHOW CREATE VIEW and it will stay that way because of compatibility reasons.

Updated SHOW CREATE VIEW section of 5.0 and 5.1 documentation to indicate that SHOW VIEW and SELECT are required to SHOW CREATE VIEW.