Bug #38347 ALTER ROUTINE privilege allows SHOW CREATE TABLE

Description:
The problem was discovered with a test doing upgrade/downgrade from/to 5.1.26 and 6.0.6, and is a change in behavior in 6.0.6 compared to 6.0.5. In 6.0.6, a user that has ALTER ROUTINE privilege on *.*, and no other privileges, is allowed to do SHOW CREATE TABLE on any table, as seen below:

mysql> show grants;
+-------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for alter_rout_all@localhost                                                                                                             |
+-------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALTER ROUTINE ON *.* TO 'alter_rout_all'@'localhost' IDENTIFIED BY PASSWORD '*20369235ECC57D8EA35440293DAE5086044A86FE' WITH GRANT OPTION |
+-------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE upgrade_drop_me_db.global_proc;
+-------------+-------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                    |
+-------------+-------------------------------------------------------------------------------------------------+
| global_proc | CREATE TABLE `global_proc` (
  `s1` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------------+-------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

This is not allowed in 6.0.5.

It seems likely that the change in behavior was introduced by the work on this bugreport:  http://bugs.mysql.com/bug.php?id=27145

I belive the intention with this change was to allow SHOW CREATE TABLE if the user has any privilege for the TABLE in question. I do not believe that ALTER ROUTINE is a privilege with a TABLE context, but with a FUNCTION/PROCEDURE context. So, ALTER ROUTINE should not allow you to do SHOW CREATE TABLE.

The problem is unrelated to the database having been upgraded, I was able to reproduce it on a fresh 6.0.6 server.

How to repeat:
Create a user with only ALTER ROUTINE privilege on *.*, and you will be able to do SHOW CREATE TABLE on any table with this user.

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/70465

2750 Alexander Nozdrin	2009-03-26
      Patch for Bug#38347: ALTER ROUTINE privilege allows SHOW CREATE TABLE.
      
      If a user has any of the following privileges for a table (or the database
      if the table), he should be able to issue SHOW CREATE TABLE for the table:
        - CREATE
        - DROP
        - ALTER
        - DELETE
        - INDEX
        - INSERT
        - SELECT
        - UPDATE
        - TRIGGER
        - REFERENCES
        - GRANT OPTION
        - CREATE VIEW
        - SHOW VIEW
      
      Any other privilege (even SUPER) should not allow SHOW CREATE TABLE.

Pushed into 6.0-runtime.

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/70538

2753 Alexander Nozdrin	2009-03-26
      Additional patch for Bug#38347 (ALTER ROUTINE privilege
      allows SHOW CREATE TABLE).

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/70740

2753 Alexander Nozdrin	2009-03-27
      Additional patch for Bug#38347 (ALTER ROUTINE privilege
      allows SHOW CREATE TABLE).
      
      The problem was that information_schema.test,
      information_schema_parameters.test and information_schema_routines.test
      failed with the first patch. That happened due to limitation in check_access():
      it allows only SELECT_ACL privilege for INFORMATION_SCHEMA tables.
      
      The patch is to request only SELECT_ACL privilege for INFORMATION_SCHEMA tables.

Pushed into 6.0.11-alpha (revid:davi.arnaut@sun.com-20090331130058-yonsevjt1833wt5o) (version source revid:alik@sun.com-20090327183634-l1qrx9tu6cqf0o8a) (merge vers: 6.0.11-alpha) (pib:6)

Noted in 6.0.11 changelog.

The ALTER ROUTINE privilege incorrectly allowed SHOW CREATE TABLE.

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/87770

2891 Alexander Nozdrin	2009-10-22
      Backporting patches for Bug#38347 (ALTER ROUTINE privilege
      allows SHOW CREATE TABLE) from 6.0. Original revisions:
      ------------------------------------------------------------
      revno: 2617.31.8
      committer: Alexander Nozdrin <alik@sun.com>
      branch nick: 6.0-rt-bug38347
      timestamp: Thu 2009-03-26 09:08:24 +0300
      message:
        Patch for Bug#38347: ALTER ROUTINE privilege allows SHOW CREATE TABLE.
        
        If a user has any of the following privileges for a table (or the database
        if the table), he should be able to issue SHOW CREATE TABLE for the table:
          - CREATE
          - DROP
          - ALTER
          - DELETE
          - INDEX
          - INSERT
          - SELECT
          - UPDATE
          - TRIGGER
          - REFERENCES
          - GRANT OPTION
          - CREATE VIEW
          - SHOW VIEW
        
        Any other privilege (even SUPER) should not allow SHOW CREATE TABLE.
      ------------------------------------------------------------
      revno: 2617.31.11
      committer: Alexander Nozdrin <alik@sun.com>
      branch nick: 6.0-rt
      timestamp: Fri 2009-03-27 21:36:34 +0300
      message:
        Additional patch for Bug#38347 (ALTER ROUTINE privilege
        allows SHOW CREATE TABLE).
        
        The problem was that information_schema.test,
        information_schema_parameters.test and information_schema_routines.test
        failed with the first patch. That happened due to limitation in check_access():
        it allows only SELECT_ACL privilege for INFORMATION_SCHEMA tables.
        
        The patch is to request only SELECT_ACL privilege for INFORMATION_SCHEMA tables.
      ------------------------------------------------------------

Pushed into 5.5.0 (mysql-next-mr-runtime).

Noted in 5.5.0 changelog.

Pushed into 6.0.14-alpha (revid:alik@sun.com-20091102151658-j9o4wgro47m5v84d) (version source revid:alik@sun.com-20091023064702-2f8jdmny61bdl94u) (merge vers: 6.0.14-alpha) (pib:13)

Already fixed in earlier 6.0.x release.

Pushed into 5.5.0-beta (revid:alik@sun.com-20091110093229-0bh5hix780cyeicl) (version source revid:mikael@mysql.com-20091103113702-p61dlwc6ml6fxg18) (merge vers: 5.5.0-beta) (pib:13)