List:Commits« Previous MessageNext Message »
From:Alexander Nozdrin Date:March 26 2009 6:08am
Subject:bzr commit into mysql-6.0 branch (alik:2750) Bug#38347
View as plain text  
#At file:///mnt/raid/alik/MySQL/bzr/bug38347/6.0-rt-bug38347/ based on revid:davi.arnaut@stripped

 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.

    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-03-26 06:08:24 +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-02-16 14:47:53 +0000
+++ b/mysql-test/r/grant.result	2009-03-26 06:08:24 +0000
@@ -1359,3 +1359,690 @@ DROP USER 'userbug33464'@'localhost';
 USE test;
 DROP DATABASE dbbug33464;
 SET @@global.log_bin_trust_function_creators= @old_log_bin_trust_function_creators;
+#########################################################################
+#
+# 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-02-12 21:10:28 +0000
+++ b/mysql-test/t/grant.test	2009-03-26 06:08:24 +0000
@@ -1473,3 +1473,326 @@ SET @@global.log_bin_trust_function_crea
 
 # 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	2008-08-18 05:43:50 +0000
+++ b/sql/sql_acl.h	2009-03-26 06:08:24 +0000
@@ -86,6 +86,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-03-18 12:49:07 +0000
+++ b/sql/sql_parse.cc	2009-03-26 06:08:24 +0000
@@ -2900,18 +2900,28 @@ ddl_blocker_err:
       else
       {
         ulong save_priv;
-        if (check_access(thd, SELECT_ACL, first_table->db,
+
+        if (check_access(thd, SHOW_CREATE_TABLE_ACLS, 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,
@@ -2920,9 +2930,8 @@ ddl_blocker_err:
         }
       }
 #endif /* NO_EMBDEDDED_ACCESS_CHECKS */
-      /*
-        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-20090326060824-7b8h0dw3gpzav2qn.bundle
Thread
bzr commit into mysql-6.0 branch (alik:2750) Bug#38347Alexander Nozdrin26 Mar