List:Commits« Previous MessageNext Message »
From:Alexander Nozdrin Date:October 22 2009 12:51pm
Subject:bzr commit into mysql-5.5.0-next-mr-runtime branch (alik:2891)
Bug#38347
View as plain text  
#At file:///mnt/raid/alik/MySQL/bzr/backporting/mysql-next-mr-runtime/ based on revid:kristofer.pettersson@stripped

 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@stripped>
      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@stripped>
      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.
      ------------------------------------------------------------

    added:
      mysql-test/include/bug38347.inc
    modified:
      mysql-test/r/grant.result
      mysql-test/t/grant.test
      sql/sql_acl.h
      sql/sql_parse.cc
=== added file 'mysql-test/include/bug38347.inc'
--- a/mysql-test/include/bug38347.inc	1970-01-01 00:00:00 +0000
+++ b/mysql-test/include/bug38347.inc	2009-10-22 12:51:51 +0000
@@ -0,0 +1,21 @@
+
+--echo
+SHOW GRANTS FOR mysqltest_u1@localhost;
+
+--echo
+--echo # connection: con1 (mysqltest_u1@mysqltest_db1)
+--connect (con1,localhost,mysqltest_u1,,mysqltest_db1)
+--connection con1
+
+--echo
+SHOW CREATE TABLE t1;
+
+--echo
+--echo # connection: default
+--connection default
+
+--disconnect con1
+
+--echo
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;
+SHOW GRANTS FOR mysqltest_u1@localhost;

=== modified file 'mysql-test/r/grant.result'
--- a/mysql-test/r/grant.result	2009-10-19 13:00:38 +0000
+++ b/mysql-test/r/grant.result	2009-10-22 12:51:51 +0000
@@ -1437,3 +1437,690 @@ SHOW GRANTS FOR mysqltest_1;
 Grants for mysqltest_1@%
 GRANT ALL PRIVILEGES ON *.* TO 'mysqltest_1'@'%'
 DROP USER mysqltest_1;
+#########################################################################
+#
+# Bug#38347: ALTER ROUTINE privilege allows SHOW CREATE TABLE.
+#
+#########################################################################
+
+# --
+# -- Prepare the environment.
+# --
+DELETE FROM mysql.user WHERE User LIKE 'mysqltest_%';
+DELETE FROM mysql.db WHERE User LIKE 'mysqltest_%';
+DELETE FROM mysql.tables_priv WHERE User LIKE 'mysqltest_%';
+DELETE FROM mysql.columns_priv WHERE User LIKE 'mysqltest_%';
+FLUSH PRIVILEGES;
+DROP DATABASE IF EXISTS mysqltest_db1;
+CREATE DATABASE mysqltest_db1;
+CREATE TABLE mysqltest_db1.t1(a INT);
+
+# --
+# -- Check that global privileges don't allow SHOW CREATE TABLE.
+# --
+GRANT EVENT                   ON mysqltest_db1.* TO mysqltest_u1@localhost;
+GRANT CREATE TEMPORARY TABLES ON mysqltest_db1.* TO mysqltest_u1@localhost;
+GRANT LOCK TABLES             ON mysqltest_db1.* TO mysqltest_u1@localhost;
+GRANT ALTER ROUTINE           ON mysqltest_db1.* TO mysqltest_u1@localhost;
+GRANT CREATE ROUTINE          ON mysqltest_db1.* TO mysqltest_u1@localhost;
+GRANT EXECUTE                 ON mysqltest_db1.* TO mysqltest_u1@localhost;
+GRANT FILE                    ON *.* TO mysqltest_u1@localhost;
+GRANT CREATE USER             ON *.* TO mysqltest_u1@localhost;
+GRANT PROCESS                 ON *.* TO mysqltest_u1@localhost;
+GRANT RELOAD                  ON *.* TO mysqltest_u1@localhost;
+GRANT REPLICATION CLIENT      ON *.* TO mysqltest_u1@localhost;
+GRANT REPLICATION SLAVE       ON *.* TO mysqltest_u1@localhost;
+GRANT SHOW DATABASES          ON *.* TO mysqltest_u1@localhost;
+GRANT SHUTDOWN                ON *.* TO mysqltest_u1@localhost;
+GRANT USAGE                   ON *.* TO mysqltest_u1@localhost;
+
+SHOW GRANTS FOR mysqltest_u1@localhost;
+Grants for mysqltest_u1@localhost
+GRANT RELOAD, SHUTDOWN, PROCESS, FILE, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT, CREATE USER ON *.* TO 'mysqltest_u1'@'localhost'
+GRANT CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE ROUTINE, ALTER ROUTINE, EVENT ON `mysqltest_db1`.* TO 'mysqltest_u1'@'localhost'
+
+# connection: con1 (mysqltest_u1@mysqltest_db1)
+
+SHOW CREATE TABLE t1;
+ERROR 42000: SHOW command denied to user 'mysqltest_u1'@'localhost' for table 't1'
+
+# connection: default
+
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;
+SHOW GRANTS FOR mysqltest_u1@localhost;
+Grants for mysqltest_u1@localhost
+GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost'
+
+# -- 
+# -- Check that global SELECT allows SHOW CREATE TABLE.
+# -- 
+
+GRANT SELECT ON mysqltest_db1.* TO mysqltest_u1@localhost;
+
+SHOW GRANTS FOR mysqltest_u1@localhost;
+Grants for mysqltest_u1@localhost
+GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost'
+GRANT SELECT ON `mysqltest_db1`.* TO 'mysqltest_u1'@'localhost'
+
+# connection: con1 (mysqltest_u1@mysqltest_db1)
+
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+
+# connection: default
+
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;
+SHOW GRANTS FOR mysqltest_u1@localhost;
+Grants for mysqltest_u1@localhost
+GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost'
+
+# -- 
+# -- Check that global INSERT allows SHOW CREATE TABLE.
+# -- 
+
+GRANT INSERT ON mysqltest_db1.* TO mysqltest_u1@localhost;
+
+SHOW GRANTS FOR mysqltest_u1@localhost;
+Grants for mysqltest_u1@localhost
+GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost'
+GRANT INSERT ON `mysqltest_db1`.* TO 'mysqltest_u1'@'localhost'
+
+# connection: con1 (mysqltest_u1@mysqltest_db1)
+
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+
+# connection: default
+
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;
+SHOW GRANTS FOR mysqltest_u1@localhost;
+Grants for mysqltest_u1@localhost
+GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost'
+
+# -- 
+# -- Check that global UPDATE allows SHOW CREATE TABLE.
+# -- 
+
+GRANT UPDATE ON mysqltest_db1.* TO mysqltest_u1@localhost;
+
+SHOW GRANTS FOR mysqltest_u1@localhost;
+Grants for mysqltest_u1@localhost
+GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost'
+GRANT UPDATE ON `mysqltest_db1`.* TO 'mysqltest_u1'@'localhost'
+
+# connection: con1 (mysqltest_u1@mysqltest_db1)
+
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+
+# connection: default
+
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;
+SHOW GRANTS FOR mysqltest_u1@localhost;
+Grants for mysqltest_u1@localhost
+GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost'
+
+# -- 
+# -- Check that global DELETE allows SHOW CREATE TABLE.
+# -- 
+
+GRANT DELETE ON mysqltest_db1.* TO mysqltest_u1@localhost;
+
+SHOW GRANTS FOR mysqltest_u1@localhost;
+Grants for mysqltest_u1@localhost
+GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost'
+GRANT DELETE ON `mysqltest_db1`.* TO 'mysqltest_u1'@'localhost'
+
+# connection: con1 (mysqltest_u1@mysqltest_db1)
+
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+
+# connection: default
+
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;
+SHOW GRANTS FOR mysqltest_u1@localhost;
+Grants for mysqltest_u1@localhost
+GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost'
+
+# -- 
+# -- Check that global CREATE allows SHOW CREATE TABLE.
+# -- 
+
+GRANT CREATE ON mysqltest_db1.* TO mysqltest_u1@localhost;
+
+SHOW GRANTS FOR mysqltest_u1@localhost;
+Grants for mysqltest_u1@localhost
+GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost'
+GRANT CREATE ON `mysqltest_db1`.* TO 'mysqltest_u1'@'localhost'
+
+# connection: con1 (mysqltest_u1@mysqltest_db1)
+
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+
+# connection: default
+
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;
+SHOW GRANTS FOR mysqltest_u1@localhost;
+Grants for mysqltest_u1@localhost
+GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost'
+
+# -- 
+# -- Check that global DROP allows SHOW CREATE TABLE.
+# -- 
+
+GRANT DROP ON mysqltest_db1.* TO mysqltest_u1@localhost;
+
+SHOW GRANTS FOR mysqltest_u1@localhost;
+Grants for mysqltest_u1@localhost
+GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost'
+GRANT DROP ON `mysqltest_db1`.* TO 'mysqltest_u1'@'localhost'
+
+# connection: con1 (mysqltest_u1@mysqltest_db1)
+
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+
+# connection: default
+
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;
+SHOW GRANTS FOR mysqltest_u1@localhost;
+Grants for mysqltest_u1@localhost
+GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost'
+
+# -- 
+# -- Check that global ALTER allows SHOW CREATE TABLE.
+# -- 
+
+GRANT ALTER ON mysqltest_db1.* TO mysqltest_u1@localhost;
+
+SHOW GRANTS FOR mysqltest_u1@localhost;
+Grants for mysqltest_u1@localhost
+GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost'
+GRANT ALTER ON `mysqltest_db1`.* TO 'mysqltest_u1'@'localhost'
+
+# connection: con1 (mysqltest_u1@mysqltest_db1)
+
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+
+# connection: default
+
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;
+SHOW GRANTS FOR mysqltest_u1@localhost;
+Grants for mysqltest_u1@localhost
+GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost'
+
+# -- 
+# -- Check that global INDEX allows SHOW CREATE TABLE.
+# -- 
+
+GRANT INDEX ON mysqltest_db1.* TO mysqltest_u1@localhost;
+
+SHOW GRANTS FOR mysqltest_u1@localhost;
+Grants for mysqltest_u1@localhost
+GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost'
+GRANT INDEX ON `mysqltest_db1`.* TO 'mysqltest_u1'@'localhost'
+
+# connection: con1 (mysqltest_u1@mysqltest_db1)
+
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+
+# connection: default
+
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;
+SHOW GRANTS FOR mysqltest_u1@localhost;
+Grants for mysqltest_u1@localhost
+GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost'
+
+# -- 
+# -- Check that global REFERENCES allows SHOW CREATE TABLE.
+# -- 
+
+GRANT REFERENCES ON mysqltest_db1.* TO mysqltest_u1@localhost;
+
+SHOW GRANTS FOR mysqltest_u1@localhost;
+Grants for mysqltest_u1@localhost
+GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost'
+GRANT REFERENCES ON `mysqltest_db1`.* TO 'mysqltest_u1'@'localhost'
+
+# connection: con1 (mysqltest_u1@mysqltest_db1)
+
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+
+# connection: default
+
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;
+SHOW GRANTS FOR mysqltest_u1@localhost;
+Grants for mysqltest_u1@localhost
+GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost'
+
+# -- 
+# -- Check that global GRANT OPTION allows SHOW CREATE TABLE.
+# -- 
+
+GRANT GRANT OPTION ON mysqltest_db1.* TO mysqltest_u1@localhost;
+
+SHOW GRANTS FOR mysqltest_u1@localhost;
+Grants for mysqltest_u1@localhost
+GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost'
+GRANT USAGE ON `mysqltest_db1`.* TO 'mysqltest_u1'@'localhost' WITH GRANT OPTION
+
+# connection: con1 (mysqltest_u1@mysqltest_db1)
+
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+
+# connection: default
+
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;
+SHOW GRANTS FOR mysqltest_u1@localhost;
+Grants for mysqltest_u1@localhost
+GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost'
+
+# -- 
+# -- Check that global CREATE VIEW allows SHOW CREATE TABLE.
+# -- 
+
+GRANT CREATE VIEW ON mysqltest_db1.* TO mysqltest_u1@localhost;
+
+SHOW GRANTS FOR mysqltest_u1@localhost;
+Grants for mysqltest_u1@localhost
+GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost'
+GRANT CREATE VIEW ON `mysqltest_db1`.* TO 'mysqltest_u1'@'localhost'
+
+# connection: con1 (mysqltest_u1@mysqltest_db1)
+
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+
+# connection: default
+
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;
+SHOW GRANTS FOR mysqltest_u1@localhost;
+Grants for mysqltest_u1@localhost
+GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost'
+
+# -- 
+# -- Check that global SHOW VIEW allows SHOW CREATE TABLE.
+# -- 
+
+GRANT SHOW VIEW ON mysqltest_db1.* TO mysqltest_u1@localhost;
+
+SHOW GRANTS FOR mysqltest_u1@localhost;
+Grants for mysqltest_u1@localhost
+GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost'
+GRANT SHOW VIEW ON `mysqltest_db1`.* TO 'mysqltest_u1'@'localhost'
+
+# connection: con1 (mysqltest_u1@mysqltest_db1)
+
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+
+# connection: default
+
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;
+SHOW GRANTS FOR mysqltest_u1@localhost;
+Grants for mysqltest_u1@localhost
+GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost'
+
+# -- 
+# -- Check that table-level SELECT allows SHOW CREATE TABLE.
+# -- 
+
+GRANT SELECT ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
+
+SHOW GRANTS FOR mysqltest_u1@localhost;
+Grants for mysqltest_u1@localhost
+GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost'
+GRANT SELECT ON `mysqltest_db1`.`t1` TO 'mysqltest_u1'@'localhost'
+
+# connection: con1 (mysqltest_u1@mysqltest_db1)
+
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+
+# connection: default
+
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;
+SHOW GRANTS FOR mysqltest_u1@localhost;
+Grants for mysqltest_u1@localhost
+GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost'
+
+# -- 
+# -- Check that table-level INSERT allows SHOW CREATE TABLE.
+# -- 
+
+GRANT INSERT ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
+
+SHOW GRANTS FOR mysqltest_u1@localhost;
+Grants for mysqltest_u1@localhost
+GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost'
+GRANT INSERT ON `mysqltest_db1`.`t1` TO 'mysqltest_u1'@'localhost'
+
+# connection: con1 (mysqltest_u1@mysqltest_db1)
+
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+
+# connection: default
+
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;
+SHOW GRANTS FOR mysqltest_u1@localhost;
+Grants for mysqltest_u1@localhost
+GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost'
+
+# -- 
+# -- Check that table-level UPDATE allows SHOW CREATE TABLE.
+# -- 
+
+GRANT UPDATE ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
+
+SHOW GRANTS FOR mysqltest_u1@localhost;
+Grants for mysqltest_u1@localhost
+GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost'
+GRANT UPDATE ON `mysqltest_db1`.`t1` TO 'mysqltest_u1'@'localhost'
+
+# connection: con1 (mysqltest_u1@mysqltest_db1)
+
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+
+# connection: default
+
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;
+SHOW GRANTS FOR mysqltest_u1@localhost;
+Grants for mysqltest_u1@localhost
+GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost'
+
+# -- 
+# -- Check that table-level DELETE allows SHOW CREATE TABLE.
+# -- 
+
+GRANT DELETE ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
+
+SHOW GRANTS FOR mysqltest_u1@localhost;
+Grants for mysqltest_u1@localhost
+GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost'
+GRANT DELETE ON `mysqltest_db1`.`t1` TO 'mysqltest_u1'@'localhost'
+
+# connection: con1 (mysqltest_u1@mysqltest_db1)
+
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+
+# connection: default
+
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;
+SHOW GRANTS FOR mysqltest_u1@localhost;
+Grants for mysqltest_u1@localhost
+GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost'
+
+# -- 
+# -- Check that table-level CREATE allows SHOW CREATE TABLE.
+# -- 
+
+GRANT CREATE ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
+
+SHOW GRANTS FOR mysqltest_u1@localhost;
+Grants for mysqltest_u1@localhost
+GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost'
+GRANT CREATE ON `mysqltest_db1`.`t1` TO 'mysqltest_u1'@'localhost'
+
+# connection: con1 (mysqltest_u1@mysqltest_db1)
+
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+
+# connection: default
+
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;
+SHOW GRANTS FOR mysqltest_u1@localhost;
+Grants for mysqltest_u1@localhost
+GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost'
+
+# -- 
+# -- Check that table-level DROP allows SHOW CREATE TABLE.
+# -- 
+
+GRANT DROP ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
+
+SHOW GRANTS FOR mysqltest_u1@localhost;
+Grants for mysqltest_u1@localhost
+GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost'
+GRANT DROP ON `mysqltest_db1`.`t1` TO 'mysqltest_u1'@'localhost'
+
+# connection: con1 (mysqltest_u1@mysqltest_db1)
+
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+
+# connection: default
+
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;
+SHOW GRANTS FOR mysqltest_u1@localhost;
+Grants for mysqltest_u1@localhost
+GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost'
+
+# -- 
+# -- Check that table-level ALTER allows SHOW CREATE TABLE.
+# -- 
+
+GRANT ALTER ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
+
+SHOW GRANTS FOR mysqltest_u1@localhost;
+Grants for mysqltest_u1@localhost
+GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost'
+GRANT ALTER ON `mysqltest_db1`.`t1` TO 'mysqltest_u1'@'localhost'
+
+# connection: con1 (mysqltest_u1@mysqltest_db1)
+
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+
+# connection: default
+
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;
+SHOW GRANTS FOR mysqltest_u1@localhost;
+Grants for mysqltest_u1@localhost
+GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost'
+
+# -- 
+# -- Check that table-level INDEX allows SHOW CREATE TABLE.
+# -- 
+
+GRANT INDEX ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
+
+SHOW GRANTS FOR mysqltest_u1@localhost;
+Grants for mysqltest_u1@localhost
+GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost'
+GRANT INDEX ON `mysqltest_db1`.`t1` TO 'mysqltest_u1'@'localhost'
+
+# connection: con1 (mysqltest_u1@mysqltest_db1)
+
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+
+# connection: default
+
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;
+SHOW GRANTS FOR mysqltest_u1@localhost;
+Grants for mysqltest_u1@localhost
+GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost'
+
+# -- 
+# -- Check that table-level REFERENCES allows SHOW CREATE TABLE.
+# -- 
+
+GRANT REFERENCES ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
+
+SHOW GRANTS FOR mysqltest_u1@localhost;
+Grants for mysqltest_u1@localhost
+GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost'
+GRANT REFERENCES ON `mysqltest_db1`.`t1` TO 'mysqltest_u1'@'localhost'
+
+# connection: con1 (mysqltest_u1@mysqltest_db1)
+
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+
+# connection: default
+
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;
+SHOW GRANTS FOR mysqltest_u1@localhost;
+Grants for mysqltest_u1@localhost
+GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost'
+
+# -- 
+# -- Check that table-level GRANT OPTION allows SHOW CREATE TABLE.
+# -- 
+
+GRANT GRANT OPTION ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
+
+SHOW GRANTS FOR mysqltest_u1@localhost;
+Grants for mysqltest_u1@localhost
+GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost'
+GRANT USAGE ON `mysqltest_db1`.`t1` TO 'mysqltest_u1'@'localhost' WITH GRANT OPTION
+
+# connection: con1 (mysqltest_u1@mysqltest_db1)
+
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+
+# connection: default
+
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;
+SHOW GRANTS FOR mysqltest_u1@localhost;
+Grants for mysqltest_u1@localhost
+GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost'
+
+# -- 
+# -- Check that table-level CREATE VIEW allows SHOW CREATE TABLE.
+# -- 
+
+GRANT CREATE VIEW ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
+
+SHOW GRANTS FOR mysqltest_u1@localhost;
+Grants for mysqltest_u1@localhost
+GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost'
+GRANT CREATE VIEW ON `mysqltest_db1`.`t1` TO 'mysqltest_u1'@'localhost'
+
+# connection: con1 (mysqltest_u1@mysqltest_db1)
+
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+
+# connection: default
+
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;
+SHOW GRANTS FOR mysqltest_u1@localhost;
+Grants for mysqltest_u1@localhost
+GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost'
+
+# -- 
+# -- Check that table-level SHOW VIEW allows SHOW CREATE TABLE.
+# -- 
+
+GRANT SHOW VIEW ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
+
+SHOW GRANTS FOR mysqltest_u1@localhost;
+Grants for mysqltest_u1@localhost
+GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost'
+GRANT SHOW VIEW ON `mysqltest_db1`.`t1` TO 'mysqltest_u1'@'localhost'
+
+# connection: con1 (mysqltest_u1@mysqltest_db1)
+
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+
+# connection: default
+
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;
+SHOW GRANTS FOR mysqltest_u1@localhost;
+Grants for mysqltest_u1@localhost
+GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost'
+
+# -- 
+# -- Cleanup.
+# -- 
+
+DROP DATABASE mysqltest_db1;
+DROP USER mysqltest_u1@localhost;
+
+# End of Bug#38347.
+

=== modified file 'mysql-test/t/grant.test'
--- a/mysql-test/t/grant.test	2009-10-09 09:57:55 +0000
+++ b/mysql-test/t/grant.test	2009-10-22 12:51:51 +0000
@@ -1556,3 +1556,326 @@ disconnect conn1;
 
 # Wait till we reached the initial number of concurrent sessions
 --source include/wait_until_count_sessions.inc
+
+--echo #########################################################################
+--echo #
+--echo # Bug#38347: ALTER ROUTINE privilege allows SHOW CREATE TABLE.
+--echo #
+--echo #########################################################################
+
+--echo
+--echo # --
+--echo # -- Prepare the environment.
+--echo # --
+
+DELETE FROM mysql.user WHERE User LIKE 'mysqltest_%';
+DELETE FROM mysql.db WHERE User LIKE 'mysqltest_%';
+DELETE FROM mysql.tables_priv WHERE User LIKE 'mysqltest_%';
+DELETE FROM mysql.columns_priv WHERE User LIKE 'mysqltest_%';
+FLUSH PRIVILEGES;
+
+--disable_warnings
+DROP DATABASE IF EXISTS mysqltest_db1;
+--enable_warnings
+
+CREATE DATABASE mysqltest_db1;
+
+CREATE TABLE mysqltest_db1.t1(a INT);
+
+--echo
+--echo # --
+--echo # -- Check that global privileges don't allow SHOW CREATE TABLE.
+--echo # --
+
+GRANT EVENT                   ON mysqltest_db1.* TO mysqltest_u1@localhost;
+GRANT CREATE TEMPORARY TABLES ON mysqltest_db1.* TO mysqltest_u1@localhost;
+GRANT LOCK TABLES             ON mysqltest_db1.* TO mysqltest_u1@localhost;
+GRANT ALTER ROUTINE           ON mysqltest_db1.* TO mysqltest_u1@localhost;
+GRANT CREATE ROUTINE          ON mysqltest_db1.* TO mysqltest_u1@localhost;
+GRANT EXECUTE                 ON mysqltest_db1.* TO mysqltest_u1@localhost;
+
+GRANT FILE                    ON *.* TO mysqltest_u1@localhost;
+GRANT CREATE USER             ON *.* TO mysqltest_u1@localhost;
+GRANT PROCESS                 ON *.* TO mysqltest_u1@localhost;
+GRANT RELOAD                  ON *.* TO mysqltest_u1@localhost;
+GRANT REPLICATION CLIENT      ON *.* TO mysqltest_u1@localhost;
+GRANT REPLICATION SLAVE       ON *.* TO mysqltest_u1@localhost;
+GRANT SHOW DATABASES          ON *.* TO mysqltest_u1@localhost;
+GRANT SHUTDOWN                ON *.* TO mysqltest_u1@localhost;
+GRANT USAGE                   ON *.* TO mysqltest_u1@localhost;
+
+--echo
+SHOW GRANTS FOR mysqltest_u1@localhost;
+
+--echo
+--echo # connection: con1 (mysqltest_u1@mysqltest_db1)
+--connect (con1,localhost,mysqltest_u1,,mysqltest_db1)
+--connection con1
+
+--echo
+--error ER_TABLEACCESS_DENIED_ERROR
+SHOW CREATE TABLE t1;
+
+--echo
+--echo # connection: default
+--connection default
+
+--disconnect con1
+
+--echo
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;
+SHOW GRANTS FOR mysqltest_u1@localhost;
+
+--echo
+--echo # -- 
+--echo # -- Check that global SELECT allows SHOW CREATE TABLE.
+--echo # -- 
+
+--echo
+GRANT SELECT ON mysqltest_db1.* TO mysqltest_u1@localhost;
+
+--source include/bug38347.inc
+
+--echo
+--echo # -- 
+--echo # -- Check that global INSERT allows SHOW CREATE TABLE.
+--echo # -- 
+
+--echo
+GRANT INSERT ON mysqltest_db1.* TO mysqltest_u1@localhost;
+
+--source include/bug38347.inc
+
+--echo
+--echo # -- 
+--echo # -- Check that global UPDATE allows SHOW CREATE TABLE.
+--echo # -- 
+
+--echo
+GRANT UPDATE ON mysqltest_db1.* TO mysqltest_u1@localhost;
+
+--source include/bug38347.inc
+
+--echo
+--echo # -- 
+--echo # -- Check that global DELETE allows SHOW CREATE TABLE.
+--echo # -- 
+
+--echo
+GRANT DELETE ON mysqltest_db1.* TO mysqltest_u1@localhost;
+
+--source include/bug38347.inc
+
+--echo
+--echo # -- 
+--echo # -- Check that global CREATE allows SHOW CREATE TABLE.
+--echo # -- 
+
+--echo
+GRANT CREATE ON mysqltest_db1.* TO mysqltest_u1@localhost;
+
+--source include/bug38347.inc
+
+--echo
+--echo # -- 
+--echo # -- Check that global DROP allows SHOW CREATE TABLE.
+--echo # -- 
+
+--echo
+GRANT DROP ON mysqltest_db1.* TO mysqltest_u1@localhost;
+
+--source include/bug38347.inc
+
+--echo
+--echo # -- 
+--echo # -- Check that global ALTER allows SHOW CREATE TABLE.
+--echo # -- 
+
+--echo
+GRANT ALTER ON mysqltest_db1.* TO mysqltest_u1@localhost;
+
+--source include/bug38347.inc
+
+--echo
+--echo # -- 
+--echo # -- Check that global INDEX allows SHOW CREATE TABLE.
+--echo # -- 
+
+--echo
+GRANT INDEX ON mysqltest_db1.* TO mysqltest_u1@localhost;
+
+--source include/bug38347.inc
+
+--echo
+--echo # -- 
+--echo # -- Check that global REFERENCES allows SHOW CREATE TABLE.
+--echo # -- 
+
+--echo
+GRANT REFERENCES ON mysqltest_db1.* TO mysqltest_u1@localhost;
+
+--source include/bug38347.inc
+
+--echo
+--echo # -- 
+--echo # -- Check that global GRANT OPTION allows SHOW CREATE TABLE.
+--echo # -- 
+
+--echo
+GRANT GRANT OPTION ON mysqltest_db1.* TO mysqltest_u1@localhost;
+
+--source include/bug38347.inc
+
+--echo
+--echo # -- 
+--echo # -- Check that global CREATE VIEW allows SHOW CREATE TABLE.
+--echo # -- 
+
+--echo
+GRANT CREATE VIEW ON mysqltest_db1.* TO mysqltest_u1@localhost;
+
+--source include/bug38347.inc
+
+--echo
+--echo # -- 
+--echo # -- Check that global SHOW VIEW allows SHOW CREATE TABLE.
+--echo # -- 
+
+--echo
+GRANT SHOW VIEW ON mysqltest_db1.* TO mysqltest_u1@localhost;
+
+--source include/bug38347.inc
+
+--echo
+--echo # -- 
+--echo # -- Check that table-level SELECT allows SHOW CREATE TABLE.
+--echo # -- 
+
+--echo
+GRANT SELECT ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
+
+--source include/bug38347.inc
+
+--echo
+--echo # -- 
+--echo # -- Check that table-level INSERT allows SHOW CREATE TABLE.
+--echo # -- 
+
+--echo
+GRANT INSERT ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
+
+--source include/bug38347.inc
+
+--echo
+--echo # -- 
+--echo # -- Check that table-level UPDATE allows SHOW CREATE TABLE.
+--echo # -- 
+
+--echo
+GRANT UPDATE ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
+
+--source include/bug38347.inc
+
+--echo
+--echo # -- 
+--echo # -- Check that table-level DELETE allows SHOW CREATE TABLE.
+--echo # -- 
+
+--echo
+GRANT DELETE ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
+
+--source include/bug38347.inc
+
+--echo
+--echo # -- 
+--echo # -- Check that table-level CREATE allows SHOW CREATE TABLE.
+--echo # -- 
+
+--echo
+GRANT CREATE ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
+
+--source include/bug38347.inc
+
+--echo
+--echo # -- 
+--echo # -- Check that table-level DROP allows SHOW CREATE TABLE.
+--echo # -- 
+
+--echo
+GRANT DROP ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
+
+--source include/bug38347.inc
+
+--echo
+--echo # -- 
+--echo # -- Check that table-level ALTER allows SHOW CREATE TABLE.
+--echo # -- 
+
+--echo
+GRANT ALTER ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
+
+--source include/bug38347.inc
+
+--echo
+--echo # -- 
+--echo # -- Check that table-level INDEX allows SHOW CREATE TABLE.
+--echo # -- 
+
+--echo
+GRANT INDEX ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
+
+--source include/bug38347.inc
+
+--echo
+--echo # -- 
+--echo # -- Check that table-level REFERENCES allows SHOW CREATE TABLE.
+--echo # -- 
+
+--echo
+GRANT REFERENCES ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
+
+--source include/bug38347.inc
+
+--echo
+--echo # -- 
+--echo # -- Check that table-level GRANT OPTION allows SHOW CREATE TABLE.
+--echo # -- 
+
+--echo
+GRANT GRANT OPTION ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
+
+--source include/bug38347.inc
+
+--echo
+--echo # -- 
+--echo # -- Check that table-level CREATE VIEW allows SHOW CREATE TABLE.
+--echo # -- 
+
+--echo
+GRANT CREATE VIEW ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
+
+--source include/bug38347.inc
+
+--echo
+--echo # -- 
+--echo # -- Check that table-level SHOW VIEW allows SHOW CREATE TABLE.
+--echo # -- 
+
+--echo
+GRANT SHOW VIEW ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
+
+--source include/bug38347.inc
+
+--echo
+--echo # -- 
+--echo # -- Cleanup.
+--echo # -- 
+
+--echo
+DROP DATABASE mysqltest_db1;
+
+DROP USER mysqltest_u1@localhost;
+
+--echo
+--echo # End of Bug#38347.
+--echo

=== modified file 'sql/sql_acl.h'
--- a/sql/sql_acl.h	2009-10-20 13:40:06 +0000
+++ b/sql/sql_acl.h	2009-10-22 12:51:51 +0000
@@ -85,6 +85,11 @@
 #define DEFAULT_CREATE_PROC_ACLS \
 (ALTER_PROC_ACL | EXECUTE_ACL)
 
+#define SHOW_CREATE_TABLE_ACLS \
+(SELECT_ACL | INSERT_ACL | UPDATE_ACL | DELETE_ACL | \
+ CREATE_ACL | DROP_ACL | ALTER_ACL | INDEX_ACL | \
+ TRIGGER_ACL | REFERENCES_ACL | GRANT_ACL | CREATE_VIEW_ACL | SHOW_VIEW_ACL)
+
 /*
   Defines to change the above bits to how things are stored in tables
   This is needed as the 'host' and 'db' table is missing a few privileges

=== modified file 'sql/sql_parse.cc'
--- a/sql/sql_parse.cc	2009-10-19 13:00:38 +0000
+++ b/sql/sql_parse.cc	2009-10-22 12:51:51 +0000
@@ -2990,18 +2990,41 @@ end_with_restore_list:
       else
       {
         ulong save_priv;
-        if (check_access(thd, SELECT_ACL, first_table->db,
+
+        /*
+          If it is an INFORMATION_SCHEMA table, SELECT_ACL privilege is the
+          only privilege allowed. For any other privilege check_access()
+          reports an error. That's how internal implementation protects
+          INFORMATION_SCHEMA from updates.
+
+          For ordinary tables any privilege from the SHOW_CREATE_TABLE_ACLS
+          set is sufficient.
+        */
+
+        ulong check_privs= test(first_table->schema_table) ?
+                           SELECT_ACL : SHOW_CREATE_TABLE_ACLS;
+
+        if (check_access(thd, check_privs, first_table->db,
                          &save_priv, FALSE, FALSE,
                          test(first_table->schema_table)))
           goto error;
+
         /*
-          save_priv contains any privileges actually granted by check_access.
-          If there are no global privileges (save_priv == 0) and no table level
-          privileges, access is denied.
+          save_priv contains any privileges actually granted by check_access
+          (i.e. save_priv contains global (user- and database-level)
+          privileges).
+
+          The fact that check_access() returned FALSE does not mean that
+          access is granted. We need to check if save_priv contains any
+          table-specific privilege. If not, we need to check table-level
+          privileges.
+
+          If there are no global privileges and no table-level privileges,
+          access is denied.
         */
-        if (!save_priv &&
-            !has_any_table_level_privileges(thd, TABLE_ACLS,
-                                            first_table))
+
+        if (!(save_priv & (SHOW_CREATE_TABLE_ACLS)) &&
+            !has_any_table_level_privileges(thd, SHOW_CREATE_TABLE_ACLS, first_table))
         {
           my_error(ER_TABLEACCESS_DENIED_ERROR, MYF(0),
                   "SHOW", thd->security_ctx->priv_user,
@@ -3010,9 +3033,7 @@ end_with_restore_list:
         }
       }
 
-      /*
-        Access is granted. Execute command.
-      */
+      /* Access is granted. Execute the command.  */
       res= mysqld_show_create(thd, first_table);
       break;
     }


Attachment: [text/bzr-bundle] bzr/alik@sun.com-20091022125151-w5vgfq2g37025zrx.bundle
Thread
bzr commit into mysql-5.5.0-next-mr-runtime branch (alik:2891)Bug#38347Alexander Nozdrin22 Oct