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-10-08 10:53:50+02:00, tnurnberg@stripped +5 -0
Bug #20901: CREATE privilege is enough to insert into a table
CREATE TABLE IF NOT EXISTS ... SELECT let you insert into an existing
table as long as you had the CREATE privilege.
CREATE ... SELECT variants now always require INSERT privilege on target table.
mysql-test/r/create.result@stripped, 2007-10-08 10:53:48+02:00, tnurnberg@stripped +38 -0
Show that CREATE...SELECT requires INSERT privilege on target table.
mysql-test/r/grant.result@stripped, 2007-10-08 10:53:48+02:00, tnurnberg@stripped +3 -3
Sort output for a defined state.
mysql-test/t/create.test@stripped, 2007-10-08 10:53:48+02:00, tnurnberg@stripped +76 -0
Show that CREATE...SELECT requires INSERT privilege on target table.
mysql-test/t/grant.test@stripped, 2007-10-08 10:53:48+02:00, tnurnberg@stripped +1 -1
Sort output for a defined state.
sql/sql_parse.cc@stripped, 2007-10-08 10:53:48+02:00, tnurnberg@stripped +3 -2
Require INSERT privilege on target table for CREATE ... SELECT.
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-10-08 10:53:48 +02:00
@@ -701,3 +701,41 @@ 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);
+create table t4 (i INT);
+grant select, insert on mysqltest.t2 to mysqltest@localhost;
+grant insert on mysqltest.t4 to mysqltest@localhost;
+grant create, insert on mysqltest.t5 to mysqltest@localhost;
+grant create, insert on mysqltest.t6 to mysqltest@localhost;
+flush privileges;
+insert into t2 values (1);
+create table if not exists t1 select * from t2;
+ERROR 42000: INSERT command denied to user 'mysqltest'@'localhost' for table 't1'
+create table if not exists t3 select * from t2;
+ERROR 42000: INSERT command denied to user 'mysqltest'@'localhost' for table 't3'
+create table if not exists t4 select * from t2;
+Warnings:
+Note 1050 Table 't4' already exists
+create table if not exists t5 select * from t2;
+create table t6 select * from t2;
+create table t7 select * from t2;
+ERROR 42000: INSERT command denied to user 'mysqltest'@'localhost' for table 't7'
+create table t4 select * from t2;
+ERROR 42S01: Table 't4' already exists
+create table t1 select * from t2;
+ERROR 42000: INSERT command denied to user 'mysqltest'@'localhost' for table 't1'
+drop table t1,t2,t4,t5,t6;
+revoke create on mysqltest.* from mysqltest@localhost;
+revoke select, insert on mysqltest.t2 from mysqltest@localhost;
+revoke insert on mysqltest.t4 from mysqltest@localhost;
+revoke create, insert on mysqltest.t5 from mysqltest@localhost;
+revoke create, insert on mysqltest.t6 from mysqltest@localhost;
+flush privileges;
+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-10-08 10:53:48 +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-10-08 10:53:48 +02:00
@@ -609,4 +609,80 @@ 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;
+# show we don't have INSERT
+--error 1044
+insert into t1 values (1);
+# show we have CREATE
+create table t2 (i INT);
+create table t4 (i INT);
+
+connection default;
+grant select, insert on mysqltest.t2 to mysqltest@localhost;
+grant insert on mysqltest.t4 to mysqltest@localhost;
+# to specify ACLs for non-existent objects, must explictly |CREATE
+grant create, insert on mysqltest.t5 to mysqltest@localhost;
+grant create, insert on mysqltest.t6 to mysqltest@localhost;
+flush privileges;
+
+connection user1;
+insert into t2 values (1);
+
+
+# CREATE IF NOT EXISTS...SELECT, t1 exists, no INSERT, must fail
+--error 1142
+create table if not exists t1 select * from t2;
+
+# CREATE IF NOT EXISTS...SELECT, no t3 yet, no INSERT, must fail
+--error 1142
+create table if not exists t3 select * from t2;
+
+# CREATE IF NOT EXISTS...SELECT, t4 exists, have INSERT, must succeed
+create table if not exists t4 select * from t2;
+
+# CREATE IF NOT EXISTS...SELECT, no t5 yet, have INSERT, must succeed
+create table if not exists t5 select * from t2;
+
+
+# CREATE...SELECT, no t6 yet, have INSERT, must succeed
+create table t6 select * from t2;
+
+# CREATE...SELECT, no t7 yet, no INSERT, must fail
+--error 1142
+create table t7 select * from t2;
+
+# CREATE...SELECT, t4 exists, have INSERT, must still fail (exists)
+--error 1050
+create table t4 select * from t2;
+
+# CREATE...SELECT, t1 exists, no INSERT, must fail
+--error 1142
+create table t1 select * from t2;
+
+
+connection default;
+drop table t1,t2,t4,t5,t6;
+
+revoke create on mysqltest.* from mysqltest@localhost;
+revoke select, insert on mysqltest.t2 from mysqltest@localhost;
+revoke insert on mysqltest.t4 from mysqltest@localhost;
+revoke create, insert on mysqltest.t5 from mysqltest@localhost;
+revoke create, insert on mysqltest.t6 from mysqltest@localhost;
+flush privileges;
+
+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-10-08 10:53:48 +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-10-08 10:53:48 +02:00
@@ -2583,9 +2583,10 @@ 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 (!check_table_access(thd, INSERT_ACL, create_table,0) &&
+ !(res=open_and_lock_tables(thd,tables)))
{
- res= -1; // If error
+ res= -1; // If error
/*
select_create is currently not re-execution friendly and
needs to be created for every execution of a PS/SP.
