#At file:///home/alik/MySQL/bzr/00/bug27480/mysql-trunk-bug27480/ based on revid:alexander.nozdrin@stripped
3529 Alexander Nozdrin 2011-02-16
Patch for Bug#27480 (Extend CREATE TEMPORARY TABLES privilege to allow
temp table operations).
The idea of this patch is to check CREATE TEMPORARY TABLES privilege
for CREATE TEMPORARY TABLE statement only. Privileges are not checked
for temporary tables for other statements.
modified:
mysql-test/r/grant.result
mysql-test/t/grant.test
sql/sql_acl.cc
sql/sql_base.h
sql/sql_parse.cc
=== modified file 'mysql-test/r/grant.result'
--- a/mysql-test/r/grant.result 2011-02-08 15:54:12 +0000
+++ b/mysql-test/r/grant.result 2011-02-16 15:37:31 +0000
@@ -2348,3 +2348,74 @@ DROP USER mysqltest_u1@localhost;
# End of Bug#38347.
+
+# --
+# -- Bug#27480
+# --
+
+# -- Check that only CREATE TEMPORARY TABLES is required to create
+# -- temporary tables.
+
+DROP DATABASE IF EXISTS mysqltest_db1;
+CREATE DATABASE mysqltest_db1;
+GRANT CREATE TEMPORARY TABLES ON mysqltest_db1.* TO mysqltest_u1@localhost;
+GRANT ALL PRIVILEGES ON mysqltest_db1.* TO mysqltest_u2@localhost;
+REVOKE CREATE TEMPORARY TABLES ON mysqltest_db1.* FROM mysqltest_u2@localhost;
+
+# -- connect con1, mysqltest_u1@localhost, mysqltest_db1
+CREATE TEMPORARY TABLE t1(a INT);
+CREATE TEMPORARY TABLE t2(a INT);
+CREATE TEMPORARY TABLE t3(a INT, b INT, PRIMARY KEY (a));
+
+# -- Check that CREATE TEMPORARY TABLES is enough to deal with temporary
+# -- tables:
+# -- * Basic DML ...
+
+INSERT INTO t1 VALUES(1), (2), (3);
+INSERT INTO t2 SELECT a FROM t1;
+UPDATE t1 SET a = a * 10;
+UPDATE t1 SET a = 100 WHERE a = 10;
+UPDATE t1, t2 SET t1.a = 200 WHERE t1.a = t2.a * 10 AND t1.a = 20;
+DELETE FROM t1 WHERE a = 100;
+DELETE t1 FROM t1, t2 WHERE t1.a = t2.a * 100 AND t1.a = 200;
+SELECT * FROM t1 ORDER BY a;
+a
+30
+DELETE FROM t1;
+DELETE FROM t2;
+INSERT INTO t3 VALUES (1, 111), (2, 222), (3, 333);
+REPLACE INTO t3 VALUES (1, 1111), (4, 444), (0, 001);
+REPLACE INTO t2 SELECT b FROM t3;
+SELECT * FROM t2 ORDER BY a;
+a
+1
+222
+333
+444
+1111
+SELECT * FROM t3 ORDER BY a;
+a b
+0 1
+1 1111
+2 222
+3 333
+4 444
+
+# -- * Basic DDL ...
+
+ALTER TABLE t1 ADD COLUMN b INT;
+INSERT INTO t1 VALUES(1, 10), (2, 20), (3, 30);
+TRUNCATE TABLE t1;
+SELECT * FROM t1;
+a b
+
+# -- connect con2, mysqltest_u2@localhost, mysqltest_db1
+CREATE TEMPORARY TABLE t2(a INT);
+ERROR 42000: Access denied for user 'mysqltest_u2'@'localhost' to database 'mysqltest_db1'
+
+# -- connection: default
+# -- disconnect con1
+# -- disconnect con2
+DROP DATABASE mysqltest_db1;
+DROP USER mysqltest_u1@localhost;
+DROP USER mysqltest_u2@localhost;
=== modified file 'mysql-test/t/grant.test'
--- a/mysql-test/t/grant.test 2010-12-15 16:15:40 +0000
+++ b/mysql-test/t/grant.test 2011-02-16 15:37:31 +0000
@@ -2009,3 +2009,93 @@ DROP USER mysqltest_u1@localhost;
--echo
--echo # End of Bug#38347.
--echo
+
+--echo
+--echo # --
+--echo # -- Bug#27480
+--echo # --
+--echo
+
+--echo # -- Check that only CREATE TEMPORARY TABLES is required to create
+--echo # -- temporary tables.
+--echo
+
+--disable_warnings
+DROP DATABASE IF EXISTS mysqltest_db1;
+--enable_warnings
+
+CREATE DATABASE mysqltest_db1;
+
+GRANT CREATE TEMPORARY TABLES ON mysqltest_db1.* TO mysqltest_u1@localhost;
+
+GRANT ALL PRIVILEGES ON mysqltest_db1.* TO mysqltest_u2@localhost;
+REVOKE CREATE TEMPORARY TABLES ON mysqltest_db1.* FROM mysqltest_u2@localhost;
+
+--echo
+--echo # -- connect con1, mysqltest_u1@localhost, mysqltest_db1
+--connect (con1,localhost,mysqltest_u1,,mysqltest_db1)
+
+CREATE TEMPORARY TABLE t1(a INT);
+CREATE TEMPORARY TABLE t2(a INT);
+CREATE TEMPORARY TABLE t3(a INT, b INT, PRIMARY KEY (a));
+
+--echo
+--echo # -- Check that CREATE TEMPORARY TABLES is enough to deal with temporary
+--echo # -- tables:
+--echo # -- * Basic DML ...
+--echo
+
+INSERT INTO t1 VALUES(1), (2), (3);
+INSERT INTO t2 SELECT a FROM t1;
+
+UPDATE t1 SET a = a * 10;
+UPDATE t1 SET a = 100 WHERE a = 10;
+UPDATE t1, t2 SET t1.a = 200 WHERE t1.a = t2.a * 10 AND t1.a = 20;
+
+DELETE FROM t1 WHERE a = 100;
+DELETE t1 FROM t1, t2 WHERE t1.a = t2.a * 100 AND t1.a = 200;
+
+SELECT * FROM t1 ORDER BY a;
+
+DELETE FROM t1;
+DELETE FROM t2;
+
+INSERT INTO t3 VALUES (1, 111), (2, 222), (3, 333);
+REPLACE INTO t3 VALUES (1, 1111), (4, 444), (0, 001);
+REPLACE INTO t2 SELECT b FROM t3;
+
+SELECT * FROM t2 ORDER BY a;
+
+SELECT * FROM t3 ORDER BY a;
+
+--echo
+--echo # -- * Basic DDL ...
+--echo
+
+ALTER TABLE t1 ADD COLUMN b INT;
+
+INSERT INTO t1 VALUES(1, 10), (2, 20), (3, 30);
+TRUNCATE TABLE t1;
+
+SELECT * FROM t1;
+
+--echo
+--echo # -- connect con2, mysqltest_u2@localhost, mysqltest_db1
+--connect (con2,localhost,mysqltest_u2,,mysqltest_db1)
+
+--error ER_DBACCESS_DENIED_ERROR
+CREATE TEMPORARY TABLE t2(a INT);
+
+--echo
+--echo # -- connection: default
+--connection default
+
+--echo # -- disconnect con1
+--disconnect con1
+
+--echo # -- disconnect con2
+--disconnect con2
+
+DROP DATABASE mysqltest_db1;
+DROP USER mysqltest_u1@localhost;
+DROP USER mysqltest_u2@localhost;
=== modified file 'sql/sql_acl.cc'
--- a/sql/sql_acl.cc 2011-01-24 15:05:24 +0000
+++ b/sql/sql_acl.cc 2011-02-16 15:37:31 +0000
@@ -4623,6 +4623,17 @@ bool check_grant(THD *thd, ulong want_ac
tl->get_table_name(),
FALSE);
+ if (grant_table)
+ {
+ tl->grant.grant_table= grant_table; // Remember for column test
+ tl->grant.version= grant_version;
+ tl->grant.privilege|= grant_table->privs;
+ tl->grant.want_privilege= ((want_access & COL_ACLS) & ~tl->grant.privilege);
+ }
+
+ if (is_temporary_table(tl))
+ continue;
+
if (!grant_table)
{
want_access &= ~tl->grant.privilege;
@@ -4636,11 +4647,6 @@ bool check_grant(THD *thd, ulong want_ac
if (any_combination_will_do)
continue;
- tl->grant.grant_table= grant_table; // Remember for column test
- tl->grant.version= grant_version;
- tl->grant.privilege|= grant_table->privs;
- tl->grant.want_privilege= ((want_access & COL_ACLS) & ~tl->grant.privilege);
-
if (!(~tl->grant.privilege & want_access))
continue;
@@ -4798,7 +4804,9 @@ bool check_column_grant_in_table_ref(THD
}
if (grant->want_privilege)
- return check_grant_column(thd, grant, db_name, table_name, name,
+ return is_temporary_table(table_ref) ?
+ FALSE :
+ check_grant_column(thd, grant, db_name, table_name, name,
length, sctx);
else
return FALSE;
@@ -4842,8 +4850,13 @@ bool check_grant_all_columns(THD *thd, u
for (; !fields->end_of_fields(); fields->next())
{
+ Field *field= fields->field();
const char *field_name= fields->name();
+ if (field && field->table &&
+ is_temporary_table(field->table->pos_in_table_list))
+ continue;
+
if (table_name != fields->get_table_name())
{
table_name= fields->get_table_name();
=== modified file 'sql/sql_base.h'
--- a/sql/sql_base.h 2011-01-17 16:27:07 +0000
+++ b/sql/sql_base.h 2011-02-16 15:37:31 +0000
@@ -577,7 +577,16 @@ private:
inline bool is_temporary_table(TABLE_LIST *tl)
{
- return tl->table ? (tl->table->s->tmp_table != NO_TMP_TABLE) : FALSE;
+ if (tl->view || tl->schema_table)
+ return FALSE;
+
+ if (!tl->table)
+ return FALSE;
+
+ if (!tl->table->s)
+ return FALSE;
+
+ return tl->table->s->tmp_table != NO_TMP_TABLE;
}
=== modified file 'sql/sql_parse.cc'
--- a/sql/sql_parse.cc 2011-02-15 17:36:23 +0000
+++ b/sql/sql_parse.cc 2011-02-16 15:37:31 +0000
@@ -3049,9 +3049,8 @@ end_with_restore_list:
{
DBUG_ASSERT(first_table == all_tables && first_table != 0);
TABLE_LIST *aux_tables= thd->lex->auxiliary_table_list.first;
- multi_delete *del_result;
- if ((res= multi_delete_precheck(thd, all_tables)))
+ if ((res= multi_delete_precheck(thd , all_tables)))
break;
/* condition will be TRUE on SP re-excuting */
@@ -3071,6 +3070,8 @@ end_with_restore_list:
goto error;
}
+ multi_delete *del_result;
+
if (!thd->is_fatal_error &&
(del_result= new multi_delete(aux_tables, lex->table_count)))
{
@@ -4649,6 +4650,9 @@ static bool execute_sqlcom_select(THD *t
bool check_single_table_access(THD *thd, ulong privilege,
TABLE_LIST *all_tables, bool no_errors)
{
+ if (is_temporary_table(all_tables))
+ return 0;
+
Security_context * backup_ctx= thd->security_ctx;
/* we need to switch to the saved context (if any) */
@@ -5074,6 +5078,9 @@ check_table_access(THD *thd, ulong requi
for (; i < number && tables != first_not_own_table && tables;
tables= tables->next_global, i++)
{
+ if (is_temporary_table(tables))
+ continue;
+
ulong want_access= requirements;
if (tables->security_ctx)
sctx= tables->security_ctx;
@@ -6763,6 +6770,15 @@ bool multi_delete_precheck(THD *thd, TAB
TABLE_LIST **save_query_tables_own_last= thd->lex->query_tables_own_last;
DBUG_ENTER("multi_delete_precheck");
+ for (TABLE_LIST *tl= aux_tables; tl; tl= tl->next_global)
+ {
+ if (tl->table)
+ continue;
+
+ if (tl->correspondent_table)
+ tl->table= tl->correspondent_table->table;
+ }
+
/* sql_yacc guarantees that tables and aux_tables are not zero */
DBUG_ASSERT(aux_tables != 0);
if (check_table_access(thd, SELECT_ACL, tables, FALSE, UINT_MAX, FALSE))
Attachment: [text/bzr-bundle] bzr/alexander.nozdrin@oracle.com-20110216153731-a5ndqvwtx4jbrs57.bundle
| Thread |
|---|
| • bzr commit into mysql-trunk branch (alexander.nozdrin:3529) Bug#27480 | Alexander Nozdrin | 16 Feb |
