MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Alexander Nozdrin Date:August 20 2010 4:27pm
Subject:bzr commit into mysql-5.5-bugfixing branch (alik:3122) Bug#27480
View as plain text  
#At file:///mnt/raid/alik/MySQL/bzr/00/bug27480/mysql-5.5-runtime-bug27480.2/ based on revid:alik@stripped

 3122 Alexander Nozdrin	2010-08-20
      Fix for Bug#27480 (Extend CREATE TEMPORARY TABLES privilege
      to allow temp table operations).
      
      The problem was that CREATE TEMPORARY TABLES privilege was
      enugh only to create temporary tables. All operations such as
      SELECT, INSERT, UPDATE, etc. required a separate privileges.
      
      The fix is to allow all operations on temporary tables if
      CREATE TEMPORARY TABLES privilege is granted.

    added:
      mysql-test/r/create_notembedded.result
      mysql-test/t/create_notembedded.test
    modified:
      sql/sql_acl.cc
      sql/sql_alter.cc
      sql/sql_parse.cc
=== added file 'mysql-test/r/create_notembedded.result'
--- a/mysql-test/r/create_notembedded.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/r/create_notembedded.result	2010-08-20 16:27:13 +0000
@@ -0,0 +1,146 @@
+DROP DATABASE IF EXISTS mysqltest2;
+CREATE DATABASE mysqltest2;
+GRANT CREATE, SELECT ON test.* TO mysqltest_u1@localhost;
+GRANT CREATE TEMPORARY TABLES ON mysqltest2.* TO mysqltest_u1@localhost;
+CREATE TEMPORARY TABLE tmp1(a INT);
+CREATE TEMPORARY TABLE tmp2(a INT);
+CREATE TABLE t1(a INT);
+ERROR 42000: CREATE command denied to user 'mysqltest_u1'@'localhost' for table 't1'
+CREATE TABLE test.t1(a INT);
+INSERT INTO tmp1 VALUES (11), (12), (13);
+INSERT INTO tmp2 VALUES (21), (22), (23);
+CREATE TEMPORARY TABLE tmp3(b INT);
+INSERT INTO tmp3 SELECT a FROM tmp1 UNION SELECT a FROM tmp2;
+SELECT * FROM tmp3;
+b
+11
+12
+13
+21
+22
+23
+SELECT * FROM tmp1;
+a
+11
+12
+13
+SELECT * FROM tmp1, tmp2;
+a	a
+11	21
+12	21
+13	21
+11	22
+12	22
+13	22
+11	23
+12	23
+13	23
+SELECT * FROM tmp1, t1, tmp2;
+ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 't1'
+SELECT * FROM tmp1, test.t1, tmp2;
+a	a	a
+UPDATE tmp1 SET a = a * 10;
+SELECT * FROM tmp1;
+a
+110
+120
+130
+UPDATE tmp2 SET a = a - 20 WHERE a >= 10;
+SELECT * FROM tmp2;
+a
+1
+2
+3
+UPDATE tmp1, tmp3
+SET a = a * 1, b = b * 1
+WHERE b < 100;
+SELECT * FROM tmp1;
+a
+110
+120
+130
+SELECT * FROM tmp3;
+b
+11
+12
+13
+21
+22
+23
+DELETE FROM tmp1;
+SELECT * FROM tmp1;
+a
+DELETE FROM tmp2 WHERE a > 2;
+SELECT * FROM tmp2;
+a
+1
+2
+DELETE FROM tmp2;
+INSERT INTO tmp1 VALUES (1), (2), (3);
+INSERT INTO tmp2 VALUES (2), (3), (4);
+SELECT * FROM tmp1;
+a
+1
+2
+3
+SELECT * FROM tmp2;
+a
+2
+3
+4
+DELETE a1, a2
+FROM tmp1 AS a1 INNER JOIN tmp2 AS a2
+WHERE a1.a = a2.a;
+SELECT * FROM tmp1;
+a
+1
+SELECT * FROM tmp2;
+a
+4
+LOCK TABLES tmp1 READ, tmp2 WRITE;
+UNLOCK TABLES;
+ALTER TABLE tmp1 ADD COLUMN b INT;
+ALTER TABLE tmp1 DROP COLUMN b;
+ALTER TABLE tmp1 RENAME TO new_tmp1;
+ALTER TABLE new_tmp1 RENAME TO tmp1;
+TRUNCATE TABLE tmp1;
+CREATE INDEX idx1 ON tmp1(a);
+DROP INDEX idx1 ON tmp1;
+INSERT INTO tmp1 VALUES (1), (2), (3);
+INSERT INTO tmp2 VALUES (11), (12), (13);
+ANALYZE TABLE tmp1, tmp2;
+Table	Op	Msg_type	Msg_text
+mysqltest2.tmp1	analyze	status	OK
+mysqltest2.tmp2	analyze	status	OK
+INSERT INTO tmp1 VALUES (1), (2), (3);
+INSERT INTO tmp2 VALUES (11), (12), (13);
+CHECK TABLE tmp1, tmp2;
+Table	Op	Msg_type	Msg_text
+mysqltest2.tmp1	check	status	OK
+mysqltest2.tmp2	check	status	OK
+INSERT INTO tmp1 VALUES (1), (2), (3);
+INSERT INTO tmp2 VALUES (11), (12), (13);
+CHECKSUM TABLE tmp1, tmp2;
+Table	Checksum
+mysqltest2.tmp1	xxx
+mysqltest2.tmp2	xxx
+INSERT INTO tmp1 VALUES (1), (2), (3);
+INSERT INTO tmp2 VALUES (11), (12), (13);
+OPTIMIZE TABLE tmp1, tmp2;
+Table	Op	Msg_type	Msg_text
+mysqltest2.tmp1	optimize	status	OK
+mysqltest2.tmp2	optimize	status	OK
+INSERT INTO tmp1 VALUES (1), (2), (3);
+INSERT INTO tmp2 VALUES (11), (12), (13);
+REPAIR TABLE tmp1, tmp2;
+Table	Op	Msg_type	Msg_text
+mysqltest2.tmp1	repair	status	OK
+mysqltest2.tmp2	repair	status	OK
+DROP TABLE tmp1;
+DROP TABLE tmp2;
+CREATE TEMPORARY TABLE tmp1(a INT) ENGINE = MyISAM;
+CREATE TEMPORARY TABLE tmp2(a INT) ENGINE = MyISAM;
+CREATE TEMPORARY TABLE mrg1(a INT) ENGINE = MRG_MyISAM UNION=(tmp1, tmp2);
+DROP DATABASE mysqltest2;
+DROP TABLE t1;
+DROP USER mysqltest_u1@localhost;

=== added file 'mysql-test/t/create_notembedded.test'
--- a/mysql-test/t/create_notembedded.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/t/create_notembedded.test	2010-08-20 16:27:13 +0000
@@ -0,0 +1,153 @@
+# Grant tests not performed with embedded server
+-- source include/not_embedded.inc
+
+#
+# Bug#27480: Extend CREATE TEMPORARY TABLES privilege to allow temp table
+# operations.
+#
+
+--disable_warnings
+DROP DATABASE IF EXISTS mysqltest2;
+--enable_warnings
+
+CREATE DATABASE mysqltest2;
+
+GRANT CREATE, SELECT ON test.* TO mysqltest_u1@localhost;
+GRANT CREATE TEMPORARY TABLES ON mysqltest2.* TO mysqltest_u1@localhost;
+
+--connect (con1, localhost, mysqltest_u1, , mysqltest2)
+
+CREATE TEMPORARY TABLE tmp1(a INT);
+CREATE TEMPORARY TABLE tmp2(a INT);
+
+--error ER_TABLEACCESS_DENIED_ERROR
+CREATE TABLE t1(a INT);
+
+CREATE TABLE test.t1(a INT);
+
+# Check INSERT INTO.
+
+INSERT INTO tmp1 VALUES (11), (12), (13);
+INSERT INTO tmp2 VALUES (21), (22), (23);
+
+CREATE TEMPORARY TABLE tmp3(b INT);
+INSERT INTO tmp3 SELECT a FROM tmp1 UNION SELECT a FROM tmp2;
+SELECT * FROM tmp3;
+
+# Check SELECT.
+
+SELECT * FROM tmp1;
+
+SELECT * FROM tmp1, tmp2;
+
+--error ER_TABLEACCESS_DENIED_ERROR
+SELECT * FROM tmp1, t1, tmp2;
+
+SELECT * FROM tmp1, test.t1, tmp2;
+
+# Check UPDATE.
+
+UPDATE tmp1 SET a = a * 10;
+SELECT * FROM tmp1;
+
+UPDATE tmp2 SET a = a - 20 WHERE a >= 10;
+SELECT * FROM tmp2;
+
+UPDATE tmp1, tmp3
+SET a = a * 1, b = b * 1
+WHERE b < 100;
+
+SELECT * FROM tmp1;
+SELECT * FROM tmp3;
+
+# Check DELETE.
+
+DELETE FROM tmp1;
+SELECT * FROM tmp1;
+
+DELETE FROM tmp2 WHERE a > 2;
+SELECT * FROM tmp2;
+
+DELETE FROM tmp2;
+
+INSERT INTO tmp1 VALUES (1), (2), (3);
+INSERT INTO tmp2 VALUES (2), (3), (4);
+
+SELECT * FROM tmp1;
+SELECT * FROM tmp2;
+
+DELETE a1, a2
+FROM tmp1 AS a1 INNER JOIN tmp2 AS a2
+WHERE a1.a = a2.a;
+
+SELECT * FROM tmp1;
+SELECT * FROM tmp2;
+
+# Check LOCK TABLES.
+
+LOCK TABLES tmp1 READ, tmp2 WRITE;
+UNLOCK TABLES;
+
+# Check ALTER TABLE.
+
+ALTER TABLE tmp1 ADD COLUMN b INT;
+ALTER TABLE tmp1 DROP COLUMN b;
+
+ALTER TABLE tmp1 RENAME TO new_tmp1;
+ALTER TABLE new_tmp1 RENAME TO tmp1;
+
+# Check TRUNCATE.
+
+TRUNCATE TABLE tmp1;
+
+# Check CREATE INDEX / DROP INDEX.
+
+CREATE INDEX idx1 ON tmp1(a);
+DROP INDEX idx1 ON tmp1;
+
+# Check admin statements
+
+INSERT INTO tmp1 VALUES (1), (2), (3);
+INSERT INTO tmp2 VALUES (11), (12), (13);
+
+ANALYZE TABLE tmp1, tmp2;
+
+INSERT INTO tmp1 VALUES (1), (2), (3);
+INSERT INTO tmp2 VALUES (11), (12), (13);
+
+CHECK TABLE tmp1, tmp2;
+
+INSERT INTO tmp1 VALUES (1), (2), (3);
+INSERT INTO tmp2 VALUES (11), (12), (13);
+
+--replace_column 2 xxx
+CHECKSUM TABLE tmp1, tmp2;
+
+INSERT INTO tmp1 VALUES (1), (2), (3);
+INSERT INTO tmp2 VALUES (11), (12), (13);
+
+OPTIMIZE TABLE tmp1, tmp2;
+
+INSERT INTO tmp1 VALUES (1), (2), (3);
+INSERT INTO tmp2 VALUES (11), (12), (13);
+
+REPAIR TABLE tmp1, tmp2;
+
+# Check DROP TABLE.
+
+DROP TABLE tmp1;
+DROP TABLE tmp2;
+
+# Check merge tables.
+
+CREATE TEMPORARY TABLE tmp1(a INT) ENGINE = MyISAM;
+CREATE TEMPORARY TABLE tmp2(a INT) ENGINE = MyISAM;
+CREATE TEMPORARY TABLE mrg1(a INT) ENGINE = MRG_MyISAM UNION=(tmp1, tmp2);
+
+--connection default
+--disconnect con1
+
+DROP DATABASE mysqltest2;
+DROP TABLE t1;
+
+DROP USER mysqltest_u1@localhost;

=== modified file 'sql/sql_acl.cc'
--- a/sql/sql_acl.cc	2010-08-20 07:34:34 +0000
+++ b/sql/sql_acl.cc	2010-08-20 16:27:13 +0000
@@ -4072,6 +4072,45 @@ bool check_grant(THD *thd, ulong want_ac
     sctx = test(table_list->security_ctx) ?
       table_list->security_ctx : thd->security_ctx;
 
+    do
+    {
+      TABLE *table= table_list->table;
+
+      /*
+        NOTE: for "multi"-statements (multi delete, update, etc.) we need
+        to check correspondent_table.
+      */
+
+      if (!table && table_list->correspondent_table)
+        table= table_list->correspondent_table->table;
+
+      if (!table ||
+          !is_temporary_table(table) ||
+          !(table_list->grant.privilege & CREATE_TMP_ACL))
+      {
+        /*
+          This is either not a temporary file, or we don't have
+          CREATE_TMP_ACL.
+        */
+        break;
+      }
+
+      /*
+        This is a temporary table and we have CREATE_TMP_ACL on it, thus we
+        can do everything with this table.
+      */
+
+      table_list->grant.privilege |= SELECT_ACL |
+                                     INSERT_ACL |
+                                     UPDATE_ACL |
+                                     DELETE_ACL |
+                                     DROP_ACL |
+                                     INDEX_ACL |
+                                     ALTER_ACL |
+                                     LOCK_TABLES_ACL;
+      table->grant.privilege= table_list->grant.privilege;
+    } while (FALSE);
+
     const ACL_internal_table_access *access=
       get_cached_table_access(&table_list->grant.m_internal,
                               table_list->get_db_name(),

=== modified file 'sql/sql_alter.cc'
--- a/sql/sql_alter.cc	2010-08-16 14:25:23 +0000
+++ b/sql/sql_alter.cc	2010-08-20 16:27:13 +0000
@@ -17,6 +17,7 @@
                                              // check_merge_table_access
 #include "sql_table.h"                       // mysql_alter_table,
                                              // mysql_exchange_partition
+#include "sql_base.h"                        // is_temporary_table
 #include "sql_alter.h"
 
 bool Alter_table_statement::execute(THD *thd)
@@ -44,8 +45,9 @@ bool Alter_table_statement::execute(THD 
   if (thd->is_fatal_error) /* out of memory creating a copy of alter_info */
     DBUG_RETURN(TRUE);
   /*
-    We also require DROP priv for ALTER TABLE ... DROP PARTITION, as well
-    as for RENAME TO, as being done by SQLCOM_RENAME_TABLE
+    We also require DROP privilege for ALTER TABLE ... DROP PARTITION,
+    as well as for RENAME TO, as being done by SQLCOM_RENAME_TABLE.
+
   */
   if (alter_info.flags & (ALTER_DROP_PARTITION | ALTER_RENAME))
     priv_needed|= DROP_ACL;
@@ -68,9 +70,17 @@ bool Alter_table_statement::execute(THD 
   if (check_grant(thd, priv_needed, first_table, FALSE, UINT_MAX, FALSE))
     DBUG_RETURN(TRUE);                  /* purecov: inspected */
 
-  if (lex->name.str && !test_all_bits(priv, INSERT_ACL | CREATE_ACL))
+  /*
+    If this is an 'ALTER TABLE ... RENAME TO ...' form:
+      - CREATE_TMP_ACL is enough for temporary tables;
+      - INSERT_ACL and CREATE_ACL are needed for base tables.
+  */
+
+  if (lex->name.str &&
+      !(is_temporary_table(first_table) &&
+        first_table->grant.privilege & CREATE_TMP_ACL) &&
+      !test_all_bits(priv, INSERT_ACL | CREATE_ACL))
   {
-    // Rename of table
     TABLE_LIST tmp_table;
     bzero((char*) &tmp_table,sizeof(tmp_table));
     tmp_table.table_name= lex->name.str;

=== modified file 'sql/sql_parse.cc'
--- a/sql/sql_parse.cc	2010-08-20 16:00:19 +0000
+++ b/sql/sql_parse.cc	2010-08-20 16:27:13 +0000
@@ -2675,7 +2675,7 @@ end_with_restore_list:
 
     DBUG_ASSERT(first_table == all_tables && first_table != 0);
 
-    if (check_one_table_access(thd, INDEX_ACL, all_tables))
+    if (check_table_access(thd, INDEX_ACL, all_tables, FALSE, 1, FALSE))
       goto error; /* purecov: inspected */
     /*
       Currently CREATE INDEX or DROP INDEX cause a full table rebuild
@@ -3150,7 +3150,24 @@ end_with_restore_list:
     DBUG_ASSERT(first_table == all_tables && first_table != 0);
     if (!lex->drop_temporary)
     {
-      if (check_table_access(thd, DROP_ACL, all_tables, FALSE, UINT_MAX, FALSE))
+      for (TABLE_LIST *tl= all_tables; tl; tl= tl->next_global)
+      {
+        if (tl->schema_table || tl->derived)
+          continue;
+
+        char table_key[MAX_DBKEY_LENGTH];
+        uint table_key_length= create_table_def_key(thd, table_key, tl, 1);
+
+        tl->table= find_temporary_table(thd, table_key, table_key_length);
+      }
+
+      res= check_table_access(thd, DROP_ACL, all_tables,
+                              FALSE, UINT_MAX, FALSE);
+
+      for (TABLE_LIST *tl= all_tables; tl; tl= tl->next_global)
+        tl->table= NULL;
+
+      if (res)
 	goto error;				/* purecov: inspected */
     }
     else
@@ -5101,6 +5118,9 @@ check_table_access(THD *thd, ulong requi
       continue;
     thd->security_ctx= sctx;
 
+    if (is_temporary_table(tables))
+      want_access= CREATE_TMP_ACL;
+
     bool res= check_access(thd, want_access, tables->get_db_name(),
                            &tables->grant.privilege,
                            &tables->grant.m_internal,


Attachment: [text/bzr-bundle] bzr/alik@sun.com-20100820162713-ezpjw2a6ggcbdg16.bundle
Thread
bzr commit into mysql-5.5-bugfixing branch (alik:3122) Bug#27480Alexander Nozdrin20 Aug