MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Tatjana A Nuernberg Date:September 17 2007 5:54am
Subject:bk commit into 4.1 tree (tnurnberg:1.2675) BUG#20901
View as plain text  
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@stripped, 2007-09-17 07:54:49+02:00, tnurnberg@stripped +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@stripped, 2007-09-17 07:54:47+02:00, tnurnberg@stripped +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@stripped, 2007-09-17 07:54:47+02:00, tnurnberg@stripped +3 -3
    Bug #20901: CREATE privilege is enough to insert into a table
    
    Sort output for a defined state.

  mysql-test/t/create.test@stripped, 2007-09-17 07:54:47+02:00, tnurnberg@stripped +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@stripped, 2007-09-17 07:54:47+02:00, tnurnberg@stripped +1 -1
    Bug #20901: CREATE privilege is enough to insert into a table
    
    Sort output for a defined state.

  sql/sql_parse.cc@stripped, 2007-09-17 07:54:47+02:00, tnurnberg@stripped +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.
Thread
bk commit into 4.1 tree (tnurnberg:1.2675) BUG#20901Tatjana A Nuernberg17 Sep