From: Alexander Nozdrin Date: February 16 2011 3:37pm Subject: bzr commit into mysql-trunk branch (alexander.nozdrin:3529) Bug#27480 List-Archive: http://lists.mysql.com/commits/131452 X-Bug: 27480 Message-Id: <201102161538.p1GErijZ008121@acsinet15.oracle.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============0741398936315480281==" --===============0741398936315480281== MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline #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)) --===============0741398936315480281== MIME-Version: 1.0 Content-Type: text/bzr-bundle; charset="us-ascii"; name="bzr/alexander.nozdrin@stripped" Content-Transfer-Encoding: 7bit Content-Disposition: inline # Bazaar merge directive format 2 (Bazaar 0.90) # revision_id: alexander.nozdrin@stripped\ # a5ndqvwtx4jbrs57 # target_branch: file:///home/alik/MySQL/bzr/00/bug27480/mysql-trunk-\ # bug27480/ # testament_sha1: c99431d8834ca30b6e9b62b223ba0e757a9976fa # timestamp: 2011-02-16 18:37:48 +0300 # source_branch: file:///home/alik/MySQL/bzr/mysql-trunk/ # base_revision_id: alexander.nozdrin@stripped\ # 9mzw9f7mhqc4el2x # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWXajdyMACBZfgFSweff////v 3+C/////YA6vj1koFt0AAACQo5GgMBsNCUlUAAdWaEkkCamaYVT/TIAmFPamQGoaaRsTE0jxIPKA 9TCUhU/Sm9TMk9U/SjRoNAAaAAAAAAGgDhoZNNDTI0NMjIMjI0MgMTRk0AZMjEMM1KSaaPJNqNA9 Q00AAAAGgAAAADhoZNNDTI0NMjIMjI0MgMTRk0AZMjEMJEggJpoAmE0E0yTIKbT1Rp5Rk0PU8oGg eTUKQiBKl16qvpMyPUYIX3X26732eRbd52sD2OE833D0rikoE34ePjV+pbyXjbA3ML/y3/pf+GJn kze7MD9bdITeTCcbHLz+X0fReXcYstlIrzhF+jX+MEpxp7rGTFd24YVijTMMMZdce27wlpm9ZIVh LhFk42O+UfvkSZJvXArMiuJE4QMYNgAE0KBA2N0xyjaVnSbM+tKzeEX291zfWlK0s1Utl+ncCXBg JTBLiEA4CV8Sopenb+vJbtR3AwlRB4GciJ2FkFMHeBckc3MCXYGTcjyRKhhkyZkmYXpONwgSzQue 5yozcHaCCcw8u1lp4elCx2FoVZBHnRSJYOxlky2vpbKZUk6zi/CIZdKG0Vvil9squrmf26e4QZYy +RBsmFQGkzdRxukIys4T+M0YfoRMNjCPsKVdChrI2yHCgZ9RBIUDGaRSGM952v9RdL+sOPA8z6/v oFAZfzGYjAGkGYbQwCwXULTSZloMLhZfH41oTP8inXaxHIUlRnHwL6Azth9CPj/LGvh4+WVyuuCv eF+VbA1C21GpH5dBwfkSJEvkjR58FGwmjZgM1SFjRhiMMAggGcT3hIJNpsJhoCQZhMJhIg/7puXE 3GEG7QI4B9mg9YOYvU4T8pCT+SvnbK3ce2xtdj1e7gl0HMTQOYij+KGfwOKElSEk91ToAAAQCAQg PZ3kA56tbqmLNnqDBE6jWkkcmm0boO/rE1Ow+ZM6uEzD4KL9szbCzRN4rmCKIixRwkzi5TStCQ4U OIiHzGAwaraATKiwCwMS4tZluYWFhfAOIH7Ez2BK0EuYAYHeFA8D/wSPuOC8QZvYtIPNjZDk8xLr 95tNQkoGaOYYnfKvDQZ6/dJXBw0+vPW+cQW7BQHcEaLggRuvTQK3OF6GKESEfuvFs0WI4o58dHkb o4yvBa2JNkNEExhrj7t8Ei08DlaygUUjQEBItLpSHfDkRIgHAMSGEr6SoDEFtCAkFgXmIPFlThSD yiYUtWMVgJg2XXi9knOxZRRtSC8v+n53EitcL/I0HKyobY4Z5lJbB/42UFdQcY2aJTY4nyAMLIGc C0qQLAoH6RoErBX0EYWs2MUDP83AYN7WkEBcaux42BKoRWBoQwg2bILTIrbd03+drgqaGdXxcWYF uBsDSc1Nc0tgv2Z00tHRJdLTOReUEsOlcg0srtMrOrUeMy3eGgtOe1jGNNbUdRatyO1m5dKX1Ae0 rz7+Es5SKROfHc8xE9MB2LhhaTdKA7Q07AgabQjojh6TMVKyggy0B2ztNnhGuLpdjvpXNxLKVD+C +rk69GWGrRosO+AN5xAuOpMtPz39w15Bv+/gvMu456+JfOeoVl2cjvJjDxOmWSDE1hzMklmXmZAQ GbKnkdcMDF1i0ww8ToTWQliOKmosMtpVSJHunmdlwOK0d3eX9DDc9z3U0yXcM3y2nJpbtM6HPWYq u3azpic2UOeu0uMiyQF5r0Kim9agbDrBhYUNwGg2FChPoov/RSNR+ol60wwIN48I7BqrqKKYxhJD PPhG2hQSrzVJXVq5ZXnwJNId3YlG8nHG3QHnIvKmy0sPPCf6lglTTYajEnnnq22RtpoFxzQHIvsv vKciKmEZDwGPGDqRdls5ltLIRVBxWrRiiYRZeMGlpYqgkRnkhMRLEkBrEWhIIhVMtHypYipT20ob QIinE5VSQaSC8mRsnT3WEeZkR1MzXMtV5BbnurYbRwZUSprv3milpee/54TOpsXvg7t6udXwat2x WRQ4kxxRlQqSkUTHkY58qZ4eHKsrSdl1mO/gTJGZiwd9pxlTOx52IrAaC4tMjjZarETmXhwLS642 bypAsiRSkvR7pyA95AqSjMSNobninwWkJLQJWFwa0isQKZCTq5isArY+26rX3L6Z7Yi+IKzif9F9 JcaxZa9OeFCYWwMZgScYSMGEylMJkiTHgrbuGv2FOAX+rJ8vupnbUJVvykENlUWxIhAxvr9iMB7W Eje/G1SzIYGN42jJBUklwfQP+jD6hUf4A8HcHYMkFoXcLMeYyQOFDwxorZkMw/5pJUEVa/Aki1yV g2AxFATCpUIF+ItNhIsAliKwmTAt/j+ZsAgxCEItCQbFITGSCAJJwhGUAvzZMFgBVIHUGKCQXQH0 MAf9A4PoUIsHtEYBOTEBnNAutQE7dZoD+YG+hyMSgEgFh+pM/kTN/RupyDoJaAWZAiAHw5m9iWCB VLzgWpByxKimWBTRaBIGAosANRetIkTExGSQQLXgBgLAMUgoD6RQmC5BCBQBehG0V4dRI1AaCojS akIjBiUhi2kxNXAUDSGYECrrXPIB0DACgTBkgV4rELaxHKRQTQhjFJIMSkBUFiXjoIvIFQFgXEAM JoRUKF0UsE9HBsEwI7huvQ5GhHEwjpiTnxnP+L6R4fRHueFHEdlpD4gjKw8QrAYTDAxjAfYtuJAX wOnqLfPr9wluO0ffptdq6iYXRc9bwhX5XIJCzCy8EkQfMqzHxK4IQIDNeF8isTId61IC3kqKaQKc Zd3xIYF3szIx2q0J8ZPiJw4QxRg3XZFUSEEfeskm8W/IeFoujXULfMg4Wb6nlqcPEhjHK0PkXhf1 L2EZetigBBnqPESs2pI0hOWXHBOOauLKipHHTeXooMakkgHeXBLZRuyGHhOPMkzqsenpjfIBfAeT RVkCFiV65pI5yR9DQ8jBSGmQz9Q/mexIxSWORlrn+ws1yCc5s5o7Zeh/sM942QXDwwS8j3mRb44J W0s9TK9I19yQPYeU5I/OhFohVtNmB8he45Fo2VtEtYkZaMC30IPE9KndKEYdToGENwJyIUySGTIx crZL0WUjMEUF3bPvEew1lXb4nwDUWM4o2ovuRvcURUBk2vDM4QbS19AFMDmkvns41Ox7mzD4cOTK 3LJWGkWRo+ZPpt0PqsfWRhrSZs4fdwQtLp5lNgpH9KlJL6zmUJHnHbGBI78Pf4WZaCeHJBjqZ2M8 BCHIKDEqQJHOiRSyiRrwba3AUjzsBpIFoKjS4secohxlhR8OTOWzeFHJKKtewiOTTodxO50ciHIx UAzh5hmU2EbCYtAWSnw3+J3Lp4xAGBB8S8ofV7uq9Nfcu0hsgryofJ8TYalQVFZEniwFMQByFI47 KovmDA+ZSMeAV42oDKr8RzdBHnEDPqgpmeLZY4xFMwNXY/iSMLoYLv4tF2uw/FNg/iK6qSoKpTES MPQDZiXna0PXpWWYxgPzMT7Grf/YgvS0gdeZ11pYon46+R1EpR2YcTjkrzVsXFuvgcufUS3yfYpK EySa4pgyMryw1sV/DxKm9mCwQRUaVDxQwDFJEIgGQyGEyB46LokFDYMrlgjNX29A9BhF2FKUSCIP mar+vUqd6xsSRr3J2gNe7efafasMV9JfpIV1+nAUhGKmU+HrVhu3EAvAiBSiAP2rXe2snm3CSJIQ 3orzGjKUwKG4mJHiJQkpfX1qtNuJtsXIaSxoWHuQjkvpOF6tszbQmhjbagIVrAaXoQe0BzYI5pM6 cFr7xBYY6vAQsUwTEdj2mppKbCn2k4yTYeSSNIFwjMmrSU080Bzu18ZBnI/CSEbD2QnZ5tDS8FEh WFyKkhGHbvktRdcd8OuFnY0LVqTPDsS0pAmeGa7y/M9hKsjWdSm4G2/CINsAKEkvbEQQIliaizkE uxdNC4ny7HJU9UU5e0C0ZGZFm4SMg/ezVYmtwsz9q6XWarTDES8i5SU7fUmSaY0cDpV8CGHhCz1y kX6ACD6mYTtR0f7sNV8xW5YJrbxXwYbkYFA91xaMVEeelLX80fwbbbeRoX3ygc2QSEgmjbexQMQ0 kHUzAgHgfL6N7PcbFdAiDlOihigZrkCwEgOVKCK0ISD2gUQIcDGkiS3oqQnAACAQRlkQQkU3bJVq hCyaaKopwl6LESCBD3bSQYmqOZ8lonac+BsDcq+BKpbX8OF60iJJItFWiG6ElM5p1FcnG69Ceom6 GgxmlaFEGvtWyg1kpxm3GqQ2mYpef6pDSLBvqCE5SUxH4KvVdnyYxYwGxViXHcgtc0az27hWUlU+ A2CUhfEtHj8T2PLWJGxeR0ORwZeVBf4QTLdhIYQ4eb45E3KZ0kD5bzcZxEpeEjMKSawhKLiw7vOJ 4eb7i7kinChIO1G7kYA= --===============0741398936315480281==--