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#20901 | Tatjana A Nuernberg | 17 Sep |
