From: Date: March 23 2007 12:12pm Subject: bk commit into 5.0 tree (anozdrin:1.2488) BUG#9504 List-Archive: http://lists.mysql.com/commits/22843 X-Bug: 9504 Message-Id: <20070323111217.8202F275C1@booka.opbmk> Below is the list of changes that have just been committed into a local 5.0 repository of alik. When alik 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-03-23 14:12:11+03:00, anozdrin@stripped +5 -0 Fix for BUG#9504: Stored procedures: execute privilege doesn't make 'use database' okay. The problem was that we didn't check stored-routine privileges in check_grant_db(). The patch adds this check. mysql-test/r/grant.result@stripped, 2007-03-23 14:12:09+03:00, anozdrin@stripped +47 -0 Update result file. mysql-test/r/sp-security.result@stripped, 2007-03-23 14:12:09+03:00, anozdrin@stripped +26 -9 Update result fil. mysql-test/t/grant.test@stripped, 2007-03-23 14:12:09+03:00, anozdrin@stripped +83 -0 Added test case for BUG#9504. mysql-test/t/sp-security.test@stripped, 2007-03-23 14:12:09+03:00, anozdrin@stripped +48 -29 Update test. sql/sql_acl.cc@stripped, 2007-03-23 14:12:09+03:00, anozdrin@stripped +29 -2 Check stored routines privileges. # This is a BitKeeper patch. What follows are the unified diffs for the # set of deltas contained in the patch. The rest of the patch, the part # that BitKeeper cares about, is below these diffs. # User: anozdrin # Host: booka.opbmk # Root: /home/alik/Documents/MySQL/devel/5.0-marvel-9504 --- 1.218/sql/sql_acl.cc 2007-03-16 11:15:49 +03:00 +++ 1.219/sql/sql_acl.cc 2007-03-23 14:12:09 +03:00 @@ -3893,6 +3893,26 @@ } +static bool check_grant_db_routine(THD *thd, const char *db, HASH *hash) +{ + Security_context *sctx= thd->security_ctx; + + for (uint idx= 0; idx < hash->records; ++idx) + { + GRANT_NAME *item= (GRANT_NAME*) hash_element(hash, idx); + + if (strcmp(item->user, sctx->priv_user) == 0 && + strcmp(item->db, db) == 0 && + compare_hostname(&item->host, sctx->host, sctx->ip)) + { + return FALSE; + } + } + + return TRUE; +} + + /* Check if a user has the right to access a database Access is accepted if he has a grant for any table/routine in the database @@ -3904,9 +3924,10 @@ Security_context *sctx= thd->security_ctx; char helping [NAME_LEN+USERNAME_LENGTH+2]; uint len; - bool error= 1; + bool error= TRUE; len= (uint) (strmov(strmov(helping, sctx->priv_user) + 1, db) - helping) + 1; + rw_rdlock(&LOCK_grant); for (uint idx=0 ; idx < column_priv_hash.records ; idx++) @@ -3917,11 +3938,17 @@ !memcmp(grant_table->hash_key,helping,len) && compare_hostname(&grant_table->host, sctx->host, sctx->ip)) { - error=0; // Found match + error= FALSE; /* Found match. */ break; } } + + if (error) + error= check_grant_db_routine(thd, db, &proc_priv_hash) && + check_grant_db_routine(thd, db, &func_priv_hash); + rw_unlock(&LOCK_grant); + return error; } --- 1.61/mysql-test/r/grant.result 2007-01-24 16:45:25 +03:00 +++ 1.62/mysql-test/r/grant.result 2007-03-23 14:12:09 +03:00 @@ -972,4 +972,51 @@ ERROR HY000: String '1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY' is too long for host name (should be no longer than 60) GRANT PROCESS ON * TO user@localhost; ERROR 3D000: No database selected +DROP DATABASE IF EXISTS mysqltest1; +DROP DATABASE IF EXISTS mysqltest2; +DROP DATABASE IF EXISTS mysqltest3; +DROP DATABASE IF EXISTS mysqltest4; +CREATE DATABASE mysqltest1; +CREATE DATABASE mysqltest2; +CREATE DATABASE mysqltest3; +CREATE DATABASE mysqltest4; +CREATE PROCEDURE mysqltest1.p_def() SQL SECURITY DEFINER +SELECT 1; +CREATE PROCEDURE mysqltest2.p_inv() SQL SECURITY INVOKER +SELECT 1; +CREATE FUNCTION mysqltest3.f_def() RETURNS INT SQL SECURITY DEFINER +RETURN 1; +CREATE FUNCTION mysqltest4.f_inv() RETURNS INT SQL SECURITY INVOKER +RETURN 1; +GRANT EXECUTE ON PROCEDURE mysqltest1.p_def TO mysqltest_1@localhost; +GRANT EXECUTE ON PROCEDURE mysqltest2.p_inv TO mysqltest_1@localhost; +GRANT EXECUTE ON FUNCTION mysqltest3.f_def TO mysqltest_1@localhost; +GRANT EXECUTE ON FUNCTION mysqltest4.f_inv TO mysqltest_1@localhost; +GRANT ALL PRIVILEGES ON test.* TO mysqltest_1@localhost; + +---> connection: bug9504_con1 +use mysqltest1; +use mysqltest2; +use mysqltest3; +use mysqltest4; +use test; +CALL mysqltest1.p_def(); +1 +1 +CALL mysqltest2.p_inv(); +1 +1 +SELECT mysqltest3.f_def(); +mysqltest3.f_def() +1 +SELECT mysqltest4.f_inv(); +mysqltest4.f_inv() +1 + +---> connection: default +DROP DATABASE mysqltest1; +DROP DATABASE mysqltest2; +DROP DATABASE mysqltest3; +DROP DATABASE mysqltest4; +DROP USER mysqltest_1@localhost; End of 5.0 tests --- 1.51/mysql-test/t/grant.test 2007-01-24 16:45:26 +03:00 +++ 1.52/mysql-test/t/grant.test 2007-03-23 14:12:09 +03:00 @@ -875,4 +875,87 @@ disconnect con1; connection default; + +# +# BUG#9504: Stored procedures: execute privilege doesn't make 'use database' +# okay. +# + +# Prepare. + +--disable_warnings +DROP DATABASE IF EXISTS mysqltest1; +DROP DATABASE IF EXISTS mysqltest2; +DROP DATABASE IF EXISTS mysqltest3; +DROP DATABASE IF EXISTS mysqltest4; +--enable_warnings + +CREATE DATABASE mysqltest1; +CREATE DATABASE mysqltest2; +CREATE DATABASE mysqltest3; +CREATE DATABASE mysqltest4; + +CREATE PROCEDURE mysqltest1.p_def() SQL SECURITY DEFINER + SELECT 1; + +CREATE PROCEDURE mysqltest2.p_inv() SQL SECURITY INVOKER + SELECT 1; + +CREATE FUNCTION mysqltest3.f_def() RETURNS INT SQL SECURITY DEFINER + RETURN 1; + +CREATE FUNCTION mysqltest4.f_inv() RETURNS INT SQL SECURITY INVOKER + RETURN 1; + +GRANT EXECUTE ON PROCEDURE mysqltest1.p_def TO mysqltest_1@localhost; +GRANT EXECUTE ON PROCEDURE mysqltest2.p_inv TO mysqltest_1@localhost; +GRANT EXECUTE ON FUNCTION mysqltest3.f_def TO mysqltest_1@localhost; +GRANT EXECUTE ON FUNCTION mysqltest4.f_inv TO mysqltest_1@localhost; + +GRANT ALL PRIVILEGES ON test.* TO mysqltest_1@localhost; + +# Test. + +--connect (bug9504_con1,localhost,mysqltest_1,,) +--echo +--echo ---> connection: bug9504_con1 + +# - Check that we can switch to the db; + +use mysqltest1; + +use mysqltest2; + +use mysqltest3; + +use mysqltest4; + +# - Check that we can call stored routines; + +use test; + +CALL mysqltest1.p_def(); + +CALL mysqltest2.p_inv(); + +SELECT mysqltest3.f_def(); + +SELECT mysqltest4.f_inv(); + +# Cleanup. + +--connection default +--echo +--echo ---> connection: default + +--disconnect bug9504_con1 + +DROP DATABASE mysqltest1; +DROP DATABASE mysqltest2; +DROP DATABASE mysqltest3; +DROP DATABASE mysqltest4; + +DROP USER mysqltest_1@localhost; + + --echo End of 5.0 tests --- 1.32/mysql-test/r/sp-security.result 2007-02-26 13:49:23 +03:00 +++ 1.33/mysql-test/r/sp-security.result 2007-03-23 14:12:09 +03:00 @@ -8,22 +8,29 @@ drop procedure db1_secret.dummy; use db1_secret; create table t1 ( u varchar(64), i int ); +insert into t1 values('test', 0); create procedure stamp(i int) insert into db1_secret.t1 values (user(), i); show procedure status like 'stamp'; Db Name Type Definer Modified Created Security_type Comment db1_secret stamp PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER -create function db() returns varchar(64) return database(); +create function db() returns varchar(64) +begin +declare v varchar(64); +select u into v from t1 limit 1; +return v; +end| show function status like 'db'; Db Name Type Definer Modified Created Security_type Comment db1_secret db FUNCTION root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER call stamp(1); select * from t1; u i +test 0 root@localhost 1 select db(); db() -db1_secret +test grant execute on procedure db1_secret.stamp to user1@'%'; grant execute on function db1_secret.db to user1@'%'; grant execute on procedure db1_secret.stamp to ''@'%'; @@ -31,25 +38,34 @@ call db1_secret.stamp(2); select db1_secret.db(); db1_secret.db() -db1_secret +test select * from db1_secret.t1; ERROR 42000: SELECT command denied to user 'user1'@'localhost' for table 't1' create procedure db1_secret.dummy() begin end; ERROR 42000: Access denied for user 'user1'@'localhost' to database 'db1_secret' drop procedure db1_secret.dummy; ERROR 42000: PROCEDURE db1_secret.dummy does not exist +drop procedure db1_secret.stamp; +ERROR 42000: alter routine command denied to user 'user1'@'localhost' for routine 'db1_secret.stamp' +drop function db1_secret.db; +ERROR 42000: alter routine command denied to user 'user1'@'localhost' for routine 'db1_secret.db' call db1_secret.stamp(3); select db1_secret.db(); db1_secret.db() -db1_secret +test select * from db1_secret.t1; ERROR 42000: SELECT command denied to user ''@'localhost' for table 't1' create procedure db1_secret.dummy() begin end; ERROR 42000: Access denied for user ''@'%' to database 'db1_secret' drop procedure db1_secret.dummy; ERROR 42000: PROCEDURE db1_secret.dummy does not exist +drop procedure db1_secret.stamp; +ERROR 42000: alter routine command denied to user ''@'%' for routine 'db1_secret.stamp' +drop function db1_secret.db; +ERROR 42000: alter routine command denied to user ''@'%' for routine 'db1_secret.db' select * from t1; u i +test 0 root@localhost 1 user1@localhost 2 anon@localhost 3 @@ -64,21 +80,22 @@ call stamp(4); select * from t1; u i +test 0 root@localhost 1 user1@localhost 2 anon@localhost 3 root@localhost 4 select db(); db() -db1_secret +test call db1_secret.stamp(5); -ERROR 42000: Access denied for user 'user1'@'localhost' to database 'db1_secret' +ERROR 42000: INSERT command denied to user 'user1'@'localhost' for table 't1' select db1_secret.db(); -ERROR 42000: Access denied for user 'user1'@'localhost' to database 'db1_secret' +ERROR 42000: SELECT command denied to user 'user1'@'localhost' for table 't1' call db1_secret.stamp(6); -ERROR 42000: Access denied for user ''@'%' to database 'db1_secret' +ERROR 42000: INSERT command denied to user ''@'localhost' for table 't1' select db1_secret.db(); -ERROR 42000: Access denied for user ''@'%' to database 'db1_secret' +ERROR 42000: SELECT command denied to user ''@'localhost' for table 't1' drop database if exists db2; create database db2; use db2; --- 1.34/mysql-test/t/sp-security.test 2006-07-13 17:12:27 +04:00 +++ 1.35/mysql-test/t/sp-security.test 2007-03-23 14:12:09 +03:00 @@ -28,6 +28,7 @@ use db1_secret; create table t1 ( u varchar(64), i int ); +insert into t1 values('test', 0); # A test procedure and function create procedure stamp(i int) @@ -35,7 +36,16 @@ --replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00' show procedure status like 'stamp'; -create function db() returns varchar(64) return database(); +delimiter |; +create function db() returns varchar(64) +begin + declare v varchar(64); + + select u into v from t1 limit 1; + + return v; +end| +delimiter ;| --replace_column 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00' show function status like 'db'; @@ -63,14 +73,18 @@ select db1_secret.db(); # ...but not this ---error 1142 +--error ER_TABLEACCESS_DENIED_ERROR select * from db1_secret.t1; # ...and not this ---error 1044 +--error ER_DBACCESS_DENIED_ERROR create procedure db1_secret.dummy() begin end; ---error 1305 +--error ER_SP_DOES_NOT_EXIST drop procedure db1_secret.dummy; +--error ER_PROCACCESS_DENIED_ERROR +drop procedure db1_secret.stamp; +--error ER_PROCACCESS_DENIED_ERROR +drop function db1_secret.db; # @@ -83,14 +97,18 @@ select db1_secret.db(); # ...but not this ---error 1142 +--error ER_TABLEACCESS_DENIED_ERROR select * from db1_secret.t1; # ...and not this ---error 1044 +--error ER_DBACCESS_DENIED_ERROR create procedure db1_secret.dummy() begin end; ---error 1305 +--error ER_SP_DOES_NOT_EXIST drop procedure db1_secret.dummy; +--error ER_PROCACCESS_DENIED_ERROR +drop procedure db1_secret.stamp; +--error ER_PROCACCESS_DENIED_ERROR +drop function db1_secret.db; # @@ -121,9 +139,9 @@ connection con2user1; # This should not work ---error 1044 +--error ER_TABLEACCESS_DENIED_ERROR call db1_secret.stamp(5); ---error 1044 +--error ER_TABLEACCESS_DENIED_ERROR select db1_secret.db(); # @@ -132,9 +150,9 @@ connection con3anon; # This should not work ---error 1044 +--error ER_TABLEACCESS_DENIED_ERROR call db1_secret.stamp(6); ---error 1044 +--error ER_TABLEACCESS_DENIED_ERROR select db1_secret.db(); # @@ -165,7 +183,7 @@ create procedure p () insert into t2 values (1); # Check that this doesn't work. ---error 1142 +--error ER_TABLEACCESS_DENIED_ERROR call p(); connect (con4user2,localhost,user2,,); @@ -174,7 +192,7 @@ use db2; # This should not work, since p is executed with definer's (user1's) rights. ---error 1370 +--error ER_PROCACCESS_DENIED_ERROR call p(); select * from t2; @@ -207,9 +225,9 @@ drop procedure p; # This should NOT work ---error 1370 +--error ER_PROCACCESS_DENIED_ERROR alter procedure q modifies sql data; ---error 1370 +--error ER_PROCACCESS_DENIED_ERROR drop procedure q; connection con1root; @@ -260,30 +278,30 @@ connection con2usera; call sptest.p1(1); ---error 1370 +--error ER_PROCACCESS_DENIED_ERROR grant execute on procedure sptest.p1 to userb@localhost; ---error 1370 +--error ER_PROCACCESS_DENIED_ERROR drop procedure sptest.p1; connection con3userb; ---error 1370 +--error ER_PROCACCESS_DENIED_ERROR call sptest.p1(2); ---error 1370 +--error ER_PROCACCESS_DENIED_ERROR grant execute on procedure sptest.p1 to userb@localhost; ---error 1370 +--error ER_PROCACCESS_DENIED_ERROR drop procedure sptest.p1; connection con4userc; call sptest.p1(3); grant execute on procedure sptest.p1 to userb@localhost; ---error 1370 +--error ER_PROCACCESS_DENIED_ERROR drop procedure sptest.p1; connection con3userb; call sptest.p1(4); ---error 1370 +--error ER_PROCACCESS_DENIED_ERROR grant execute on procedure sptest.p1 to userb@localhost; ---error 1370 +--error ER_PROCACCESS_DENIED_ERROR drop procedure sptest.p1; connection con1root; @@ -332,7 +350,7 @@ connect (user1,localhost,user1,,test); connection user1; use mysqltest; --- error 1370 +-- error ER_PROCACCESS_DENIED_ERROR select bug_9503(); connection root; @@ -401,13 +419,13 @@ connect (n1,localhost,mysqltest_1,,information_schema,$MASTER_MYPORT,$MASTER_MYSOCK); connection n1; ---error 1370 +--error ER_PROCACCESS_DENIED_ERROR call mysqltest_1.p1(); disconnect n1; # Test also without a current database connect (n2,localhost,mysqltest_1,,*NO-ONE*,$MASTER_MYPORT,$MASTER_MYSOCK); connection n2; ---error 1370 +--error ER_PROCACCESS_DENIED_ERROR call mysqltest_1.p1(); disconnect n2; @@ -433,9 +451,9 @@ create user user_bug12812@localhost IDENTIFIED BY 'ABC'| --replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK connect (test_user_12812,localhost,user_bug12812,ABC,test)| ---error 1370 +--error ER_PROCACCESS_DENIED_ERROR SELECT test.bug12812()| ---error 1370 +--error ER_PROCACCESS_DENIED_ERROR CREATE VIEW v1 AS SELECT test.bug12812()| # Cleanup connection default| @@ -489,7 +507,8 @@ # -# BUG#14533: 'desc tbl' in stored procedure causes error 1142 +# BUG#14533: 'desc tbl' in stored procedure causes error +# ER_TABLEACCESS_DENIED_ERROR # create database db_bug14533; use db_bug14533;