Bug #49346 Privilege on routines gives access to SHOW CREATE TABLE
Submitted: 2 Dec 2009 12:57 Modified: 14 Dec 2009 18:24
Reporter: Vemund Østgaard Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Security: Privileges Severity:S3 (Non-critical)
Version:5.5.0, mysql-next-mr OS:Any
Assigned to: Kristofer Pettersson CPU Architecture:Any

[2 Dec 2009 12:57] Vemund Østgaard
Description:
If a user is given ALTER ROUTINE privilege for *.* WITH GRANT OPTION:

GRANT ALTER ROUTINE ON *.* TO 'testuser' WITH GRANT OPTION;

that user gets access to SHOW CREATE TABLE on any table.

If the user is just given ALTER ROUTINE privilege for *.* (but no GRANT OPTION):

GRANT ALTER ROUTINE ON *.* TO 'testuser';

that user does not get access to SHOW CREATE TABLE on any table.

This is a change in behavior from previous versions, where ALTER ROUTINE WITH GRANT OPTION would not give access to SHOW CREATE TABLE. This change was introduced in 5.5.0 and I believe it is also in 5.1.41. The fix that introduced the change seems to be bug#27145.

I think that *.* in the grant statements above means "any routine" and not "any table", so it seems strange that granting access to routines also grants access to tables.

How to repeat:
1. GRANT ALTER ROUTINE ON *.* TO 'testuser' WITH GRANT OPTION;

2. Connect as testuser

3. SHOW CREATE TABLE on any table testuser normally would not have access to.
[3 Dec 2009 8:10] Sveta Smirnova
Thank you for the report.

Verified as described. Version 5.1 is not affected.

Test case to repeat the problem:

create database db1;
use db1;
create table t1(f1 int);
GRANT ALTER ROUTINE ON *.* TO 'testuser'@'localhost' WITH GRANT OPTION;

connect (addconroot, localhost, testuser,,);
connection addconroot;
use db1;
show create table t1;
show tables;
[7 Dec 2009 7:46] Kristofer Pettersson
thoughts: 'WITH GRANT OPTION' should apply to all objects in this case. The GRANT privilege is part of the requirement for SHOW CREATE TABLE, and anyone with this privilege is able to execute the statement.

Maybe it isn't a bug after all?
[7 Dec 2009 20:28] Kristofer Pettersson
PeterG says GRANT OPTION should not be part of the requirements list for SHOW CREATE TABLE. Grabbing this.
[14 Dec 2009 18:45] Peter Laursen
But 5.1 and 5.5 are inconsistent in this respect I understand!? Is this difference documented (at least) ?

This closure is a bureaucratic gesture and sheer laziness simply!
[14 Dec 2009 19:12] Alexander Nozdrin
GRANT OPTION is a table privilege (because of documentation and because
it can be granted on a table). So if a user has GRANT OPTION on all objects
in a database, that privilege is applicable to tables, and therefore
SHOW CREATE TABLE is possible.