bk commit into 4.1 tree (tnurnberg:1.2675) BUG#20901

From: Tatjana A Nuernberg <azundris(at)mysql.com>
Date: Mon Sep 17 2007 - 01:54:51 EDT

Below is the list of changes that have just been committed into a local 4.1 repository of tnurnberg. When tnurnberg does a push these changes will be propagated to the main repository and, within 24 hours after the push, to the public repository.
For information on how to access the public repository see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@1.2675, 2007-09-17 07:54:49+02:00, tnurnberg@mysql.com +5 -0   Bug #20901: CREATE privilege is enough to insert into a table   

  CREATE privilege let you CREATE...SELECT into an existing table,   and one you didn't had INSERT on to boot. On existing table,   CREATE...SELECT will now fail with an error, CREATE TABLE IF   NOT EXISTS...SELECT with a warning; in either case, no rows will   be inserted.

  mysql-test/r/create.result@1.91, 2007-09-17 07:54:47+02:00, tnurnberg@mysql.com +32 -7     Bug #20901: CREATE privilege is enough to insert into a table     

    Prove we can no longer insert into a table we don't have INSERT     privilege on with CREATE...SELECT just because we have CREATE.     Show that everything else still works.

  mysql-test/r/grant.result@1.44, 2007-09-17 07:54:47+02:00, tnurnberg@mysql.com +3 -3     Bug #20901: CREATE privilege is enough to insert into a table     

    Sort output for a defined state.

  mysql-test/t/create.test@1.64, 2007-09-17 07:54:47+02:00, tnurnberg@mysql.com +39 -2     Bug #20901: CREATE privilege is enough to insert into a table     

    Prove we can no longer insert into a table we don't have INSERT     privilege on with CREATE...SELECT just because we have CREATE.     Show that everything else still works.

  mysql-test/t/grant.test@1.37, 2007-09-17 07:54:47+02:00, tnurnberg@mysql.com +1 -1     Bug #20901: CREATE privilege is enough to insert into a table     

    Sort output for a defined state.

  sql/sql_parse.cc@1.500, 2007-09-17 07:54:47+02:00, tnurnberg@mysql.com +24 -2     Bug #20901: CREATE privilege is enough to insert into a table     

    CREATE...SELECT with existing table already fails deep in the     bowels of the server, in select_create::prepare() ->     create_table_from_items() -> mysql_create_table(). Make sure     we also catch CREATE TABLE IF NOT EXISTS...SELECT, in which     case we throw a table exists warning and do not insert any rows.     Do accomplish this, we check for the combination of CREATE/SELECT,     IF NOT EXISTS, on a non-temporary table. In that case, we try to     open the target table; if that fails, execution will proceed,     otherwise, it will be cut short.

diff -Nrup a/mysql-test/r/create.result b/mysql-test/r/create.result

--- a/mysql-test/r/create.result	2007-04-02 10:39:23 +02:00

+++ b/mysql-test/r/create.result	2007-09-17 07:54:47 +02:00

@@ -237,15 +237,14 @@ create table if not exists t1 select 1,2  Warnings:
 Note 1050 Table 't1' already exists
 create table if not exists t1 select 1,2,3,4; -ERROR 21S01: Column count doesn't match value count at row 1
+Warnings:
+Note 1050 Table 't1' already exists

 create table if not exists t1 select 1;  Warnings:
 Note 1050 Table 't1' already exists
 select * from t1;

 1	2	3
 1	2	3
-0	1	2
-0	0	1

 drop table t1;
 create table t1 (a int not null, b int, primary key (a));  insert into t1 values (1,1);
@@ -255,17 +254,15 @@ Note 1050 Table 't1' already exists  select * from t1;

 a	b
 1	1
-0	2

 create table if not exists t1 select 3 as 'a',4 as 'b';  Warnings:
 Note 1050 Table 't1' already exists
 create table if not exists t1 select 3 as 'a',3 as 'b'; -ERROR 23000: Duplicate entry '3' for key 1
+Warnings:
+Note 1050 Table 't1' already exists

 select * from t1;

 a	b
 1	1
-0	2
-3	4

 drop table t1;
 create table `t1 `(a int);
 ERROR 42000: Incorrect table name 't1 ' @@ -701,3 +698,31 @@ t2 CREATE TABLE `t2` (  drop table t1, t2;
 create table t1(a set("a,b","c,d") not null);  ERROR HY000: Illegal set 'a,b' value found during parsing
+create database mysqltest;
+use mysqltest;
+grant CREATE on mysqltest.* TO mysqltest@localhost;
+create table t1 (i INT);
+insert into t1 values (1);
+ERROR 42000: Access denied for user 'mysqltest'@'localhost' to database 'mysqltest'
+create table t2 (i INT);
+grant select, insert on mysqltest.t2 TO mysqltest@localhost;
+flush privileges;
+insert into t2 values (1);
+create table if not exists t1 select * from t2;
+Warnings:
+Note 1050 Table 't1' already exists
+create table if not exists t3 select * from t2;
+create table t4 select * from t2;
+create table t4 select * from t2;
+ERROR 42S01: Table 't4' already exists
+select * from t1;
+i
+select * from t3;
+i
+1
+select * from t4;
+i
+1
+drop table t1,t2,t3,t4;
+drop database mysqltest;
+use test;

diff -Nrup a/mysql-test/r/grant.result b/mysql-test/r/grant.result

--- a/mysql-test/r/grant.result	2007-04-17 13:52:49 +02:00

+++ b/mysql-test/r/grant.result	2007-09-17 07:54:47 +02:00

@@ -349,12 +349,12 @@ show grants for grant_user@localhost;  Grants for grant_user@localhost
 GRANT USAGE ON *.* TO 'grant_user'@'localhost'  GRANT INSERT (a, d, c, b) ON `test`.`t1` TO 'grant_user'@'localhost' -select Host,Db,User,Table_name,Column_name,Column_priv from mysql.columns_priv;
+select Host,Db,User,Table_name,Column_name,Column_priv from mysql.columns_priv order by Column_name;

 Host	Db	User	Table_name	Column_name	Column_priv
-localhost	test	grant_user	t1	b	Insert
-localhost	test	grant_user	t1	d	Insert
 localhost	test	grant_user	t1	a	Insert

+localhost	test	grant_user	t1	b	Insert

 localhost	test	grant_user	t1	c	Insert

+localhost	test	grant_user	t1	d	Insert

 revoke ALL PRIVILEGES on t1 from grant_user@localhost;  show grants for grant_user@localhost;
 Grants for grant_user@localhost
diff -Nrup a/mysql-test/t/create.test b/mysql-test/t/create.test

--- a/mysql-test/t/create.test	2007-04-02 10:39:23 +02:00

+++ b/mysql-test/t/create.test	2007-09-17 07:54:47 +02:00

@@ -202,7 +202,6 @@ drop table t1;  

 create table t1 select 1,2,3;
 create table if not exists t1 select 1,2; ---error 1136
 create table if not exists t1 select 1,2,3,4;  create table if not exists t1 select 1;  select * from t1;
@@ -217,7 +216,6 @@ insert into t1 values (1,1);  create table if not exists t1 select 2;  select * from t1;
 create table if not exists t1 select 3 as 'a',4 as 'b'; ---error 1062
 create table if not exists t1 select 3 as 'a',3 as 'b';  select * from t1;
 drop table t1;
@@ -608,5 +606,44 @@ drop table t1, t2;
 #
 --error 1105
 create table t1(a set("a,b","c,d") not null);
+
+#
+# Bug #20901 - CREATE privilege is enough to insert into a table
+#
+
+create database mysqltest;
+use mysqltest;
+
+grant CREATE on mysqltest.* TO mysqltest@localhost;
+create table t1 (i INT);
+
+connect (user1,localhost,mysqltest,,mysqltest);
+connection user1;
+--error 1044
+insert into t1 values (1);
+create table t2 (i INT);
+
+connection default;
+grant select, insert on mysqltest.t2 TO mysqltest@localhost;
+flush privileges;
+
+connection user1;
+insert into t2 values (1);
+create table if not exists t1 select * from t2;
+create table if not exists t3 select * from t2;
+create table t4 select * from t2;
+--error 1050
+create table t4 select * from t2;
+
+connection default;
+select * from t1;
+select * from t3;
+select * from t4;
+
+drop table t1,t2,t3,t4;
+
+disconnect user1;
+drop database mysqltest;
+use test;
 

 # End of 4.1 tests
diff -Nrup a/mysql-test/t/grant.test b/mysql-test/t/grant.test

--- a/mysql-test/t/grant.test	2007-04-17 13:52:49 +02:00

+++ b/mysql-test/t/grant.test	2007-09-17 07:54:47 +02:00

@@ -296,7 +296,7 @@ DROP DATABASE testdb10;  create table t1(a int, b int, c int, d int);  grant insert(b), insert(c), insert(d), insert(a) on t1 to grant_user@localhost;  show grants for grant_user@localhost;
-select Host,Db,User,Table_name,Column_name,Column_priv from mysql.columns_priv;
+select Host,Db,User,Table_name,Column_name,Column_priv from mysql.columns_priv order by Column_name;
 revoke ALL PRIVILEGES on t1 from grant_user@localhost;  show grants for grant_user@localhost;
 select Host,Db,User,Table_name,Column_name,Column_priv from mysql.columns_priv; diff -Nrup a/sql/sql_parse.cc b/sql/sql_parse.cc

--- a/sql/sql_parse.cc	2007-06-12 14:47:34 +02:00

+++ b/sql/sql_parse.cc	2007-09-17 07:54:47 +02:00

@@ -2575,6 +2575,7 @@ mysql_execute_command(THD *thd)
     if (select_lex->item_list.elements)		// With select
     {
       select_result *result;

+      uint counter;

 
       select_lex->options|= SELECT_NO_UNLOCK;
       unit->offset_limit_cnt= select_lex->offset_limit;
@@ -2583,9 +2584,30 @@ mysql_execute_command(THD *thd)
       if (unit->select_limit_cnt < select_lex->select_limit)
 	unit->select_limit_cnt= HA_POS_ERROR;	// No limit
 
-      if (!(res=open_and_lock_tables(thd,tables)))

+      if (((create_info.options & HA_LEX_CREATE_IF_NOT_EXISTS) &&
+           !(create_info.options & HA_LEX_CREATE_TMP_TABLE)) &&
+          !open_tables(thd, create_table, &counter))

       {
-	res= -1;				// If error

+        /*
+          If target table exists, CREATE...SELECT will fail in
+          select_create::prepare() -> create_table_from_items() ->
+          mysql_create_table. CREATE IF NOT EXISTS...SELECT would
+          just throw a warning for the existing table and would then
+          still SELECT into it. This is not what we want, independent
+          from whether we ever have the privileges to. Thus, we throw a
+          warning for the existing table here, then bail before SELECT.
+        */
+
+        push_warning_printf(thd, MYSQL_ERROR::WARN_LEVEL_NOTE,
+                            ER_TABLE_EXISTS_ERROR, ER(ER_TABLE_EXISTS_ERROR),
+                            create_table->real_name);
+        send_ok(thd);
+      }
+
+      else if (!(res=open_and_lock_tables(thd,tables)))
+      {
+        thd->net.report_error= 0;
+        res= -1;				// If error

         /*
           select_create is currently not re-execution friendly and
           needs to be created for every execution of a PS/SP.

-- 
MySQL Code Commits Mailing List
For list archives: 
http://lists.mysql.com/commits
To unsubscribe:    
http://lists.mysql.com/commits?unsub=lists@pantek.com

Received on Mon Sep 17 01:48:44 2007