=== modified file 'mysql-test/r/automatic_sp_privileges_func.result' --- a/mysql-test/r/automatic_sp_privileges_func.result 2008-04-10 13:14:28 +0000 +++ b/mysql-test/r/automatic_sp_privileges_func.result 2008-06-12 22:21:33 +0000 @@ -57,3 +57,177 @@ DROP USER 'userTest'@'localhost'; DROP USER 'userTest1'@'localhost'; DROP TABLE t1; +# +# Bug#31501 Stored Routines: droping stored procedure revokes all assotiated privileges +# +GRANT CREATE ROUTINE ON test.* TO mysqluser1@localhost; +GRANT CREATE ROUTINE ON test.* TO mysqluser2@localhost; +GRANT CREATE ROUTINE ON test.* TO mysqluser2@10.0.0.1; +SET @global_automatic_sp_privileges = @@GLOBAL.automatic_sp_privileges; +SET GLOBAL automatic_sp_privileges=ON; +DROP PROCEDURE IF EXISTS test.proc_user1; +DROP PROCEDURE IF EXISTS test.proc_user2; +** Create a procedure as definer= mysqluser1 +CREATE PROCEDURE proc_user1 (OUT param1 INT) +SELECT COUNT(*) INTO param1 FROM t; + +** mysqluser1 gained privileges for procedure. +SHOW GRANTS FOR mysqluser1@localhost; +Grants for mysqluser1@localhost +GRANT USAGE ON *.* TO 'mysqluser1'@'localhost' +GRANT CREATE ROUTINE ON `test`.* TO 'mysqluser1'@'localhost' +GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `test`.`proc_user1` TO 'mysqluser1'@'localhost' + +** Connecting as mysqluser2 +** Create a procedure as definer= mysqluser2. +CREATE PROCEDURE proc_user2 (OUT param1 INT) +SELECT COUNT(*) INTO param1 FROM t; + +** Creating SP and get automatic privileges (gaining EXECUTE,ALTER ROUTINE) +SHOW GRANTS FOR mysqluser2@localhost; +Grants for mysqluser2@localhost +GRANT USAGE ON *.* TO 'mysqluser2'@'localhost' +GRANT CREATE ROUTINE ON `test`.* TO 'mysqluser2'@'localhost' +GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `test`.`proc_user2` TO 'mysqluser2'@'localhost' + +** Dropping procedure with definer= mysqluser1 as current_user= mysqluser2 +** First assign ALTER ROUTINE to mysqluser2 on test.proc_user1. +GRANT ALTER ROUTINE ON PROCEDURE test.proc_user1 TO mysqluser2@localhost; +** This won't result in any grant changes because we don't remove grants from other users automatically. +DROP PROCEDURE test.proc_user1; + +** Grants for mysqluser1 will still contain EXECUTE, ALTER ROUTINE privileges +SHOW GRANTS FOR mysqluser1@localhost; +Grants for mysqluser1@localhost +GRANT USAGE ON *.* TO 'mysqluser1'@'localhost' +GRANT CREATE ROUTINE ON `test`.* TO 'mysqluser1'@'localhost' +GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `test`.`proc_user1` TO 'mysqluser1'@'localhost' + +** Dropping procedure with definer= mysqluser2 as current_user= mysqluser2 +DROP PROCEDURE test.proc_user2; +** Execution grants for mysqluser2 should now be revoked: +SHOW GRANTS FOR mysqluser2@localhost; +Grants for mysqluser2@localhost +GRANT USAGE ON *.* TO 'mysqluser2'@'localhost' +GRANT CREATE ROUTINE ON `test`.* TO 'mysqluser2'@'localhost' +GRANT ALTER ROUTINE ON PROCEDURE `test`.`proc_user1` TO 'mysqluser2'@'localhost' + +** Revoke ALTER, EXECUTE ROUTINE privileges from mysqluser1 +REVOKE ALL PRIVILEGES ON PROCEDURE test.proc_user1 FROM mysqluser1@localhost; + +************************************************** + +** Repeating test with automatic_privileges=OFF; +SET GLOBAL automatic_sp_privileges=OFF; +** Create a procedure as definer= mysqluser1 +CREATE PROCEDURE proc_user1 (OUT param1 INT) +SELECT COUNT(*) INTO param1 FROM t; + +** mysqluser1 has not gained privileges for procedure: +SHOW GRANTS FOR mysqluser1@localhost; +Grants for mysqluser1@localhost +GRANT USAGE ON *.* TO 'mysqluser1'@'localhost' +GRANT CREATE ROUTINE ON `test`.* TO 'mysqluser1'@'localhost' + +** Connecting as mysqluser2 +** Create a procedure as definer= mysqluser2. +CREATE PROCEDURE proc_user2 (OUT param1 INT) +SELECT COUNT(*) INTO param1 FROM t; + +** Creating SP and but hasn't gained any automatic privileges: +SHOW GRANTS FOR mysqluser2@localhost; +Grants for mysqluser2@localhost +GRANT USAGE ON *.* TO 'mysqluser2'@'localhost' +GRANT CREATE ROUTINE ON `test`.* TO 'mysqluser2'@'localhost' +GRANT ALTER ROUTINE ON PROCEDURE `test`.`proc_user1` TO 'mysqluser2'@'localhost' + +** Dropping procedure with definer= mysqluser1 as current_user= mysqluser2 +** First assign ALTER ROUTINE to mysqluser2 on test.proc_user1. +GRANT ALTER ROUTINE ON PROCEDURE test.proc_user1 TO mysqluser2@localhost; +** This won't result in any grant changes because we don't remove grants from other users automatically. +DROP PROCEDURE test.proc_user1; + +** Grants for mysqluser1 will remain the same: +SHOW GRANTS FOR mysqluser1@localhost; +Grants for mysqluser1@localhost +GRANT USAGE ON *.* TO 'mysqluser1'@'localhost' +GRANT CREATE ROUTINE ON `test`.* TO 'mysqluser1'@'localhost' + +** Dropping procedure with definer= mysqluser2 as current_user= mysqluser2 +** First assign ALTER ROUTINE to mysqluser2 on test.proc_user1. +GRANT ALTER ROUTINE ON PROCEDURE test.proc_user2 TO mysqluser2@localhost; +DROP PROCEDURE test.proc_user2; +** Grants for mysqluser2 should now contain CREATE, ALTER ROUTINE: +SHOW GRANTS FOR 'mysqluser2'@'localhost'; +Grants for mysqluser2@localhost +GRANT USAGE ON *.* TO 'mysqluser2'@'localhost' +GRANT CREATE ROUTINE ON `test`.* TO 'mysqluser2'@'localhost' +GRANT ALTER ROUTINE ON PROCEDURE `test`.`proc_user2` TO 'mysqluser2'@'localhost' +GRANT ALTER ROUTINE ON PROCEDURE `test`.`proc_user1` TO 'mysqluser2'@'localhost' +REVOKE ALL PRIVILEGES ON PROCEDURE test.proc_user1 FROM mysqluser2@localhost; +REVOKE ALL PRIVILEGES ON PROCEDURE test.proc_user2 FROM mysqluser2@localhost; + +************************************************** + +** Running test with DEFINER directive + +SET GLOBAL automatic_sp_privileges=ON; +** Create a procedure as definer= mysqluser2 +CREATE DEFINER=mysqluser2@localhost PROCEDURE proc_user1 (OUT param1 INT) +SELECT COUNT(*) INTO param1 FROM t; +** Create a procedure as definer= mysqluser2. +CREATE DEFINER=mysqluser2@localhost PROCEDURE proc_user2 (OUT param1 INT) +SELECT COUNT(*) INTO param1 FROM t; + +** mysqluser2 gained automatic privileges for two procedures (gaining EXECUTE,ALTER ROUTINE) +SHOW GRANTS FOR 'mysqluser2'@'localhost'; +Grants for mysqluser2@localhost +GRANT USAGE ON *.* TO 'mysqluser2'@'localhost' +GRANT CREATE ROUTINE ON `test`.* TO 'mysqluser2'@'localhost' +GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `test`.`proc_user2` TO 'mysqluser2'@'localhost' +GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `test`.`proc_user1` TO 'mysqluser2'@'localhost' +** Make sure only user account tied to a certain host is modified: +SHOW GRANTS FOR mysqluser2@10.0.0.1; +Grants for mysqluser2@10.0.0.1 +GRANT USAGE ON *.* TO 'mysqluser2'@'10.0.0.1' +GRANT CREATE ROUTINE ON `test`.* TO 'mysqluser2'@'10.0.0.1' + +** Dropping procedure with definer= mysqluser1 as current_user= mysqluser2 +** Grants will be dropped automatically for mysqluser2. +** To be sure that mysqluser1 is unaffected we add EXECUTE ROUTINE privilege. +GRANT EXECUTE ON PROCEDURE test.proc_user1 TO mysqluser1@localhost; +DROP PROCEDURE test.proc_user1; + +** Grants for mysqluser1 will still have the EXECUTE privileges +SHOW GRANTS FOR mysqluser1@localhost; +Grants for mysqluser1@localhost +GRANT USAGE ON *.* TO 'mysqluser1'@'localhost' +GRANT CREATE ROUTINE ON `test`.* TO 'mysqluser1'@'localhost' +GRANT EXECUTE ON PROCEDURE `test`.`proc_user1` TO 'mysqluser1'@'localhost' + +** While mysqluser2 will have lost these privileges for proc_user1: +SHOW GRANTS FOR mysqluser2@localhost; +Grants for mysqluser2@localhost +GRANT USAGE ON *.* TO 'mysqluser2'@'localhost' +GRANT CREATE ROUTINE ON `test`.* TO 'mysqluser2'@'localhost' +GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `test`.`proc_user2` TO 'mysqluser2'@'localhost' + +** Dropping procedure with definer= mysqluser2 as current_user= mysqluser2 +DROP PROCEDURE test.proc_user2; +** Execution grants for mysqluser2 should now be revoked: +SHOW GRANTS FOR mysqluser2@localhost; +Grants for mysqluser2@localhost +GRANT USAGE ON *.* TO 'mysqluser2'@'localhost' +GRANT CREATE ROUTINE ON `test`.* TO 'mysqluser2'@'localhost' +** Make sure only user account tied to a certain host is modified: +SHOW GRANTS FOR mysqluser2@10.0.0.1; +Grants for mysqluser2@10.0.0.1 +GRANT USAGE ON *.* TO 'mysqluser2'@'10.0.0.1' +GRANT CREATE ROUTINE ON `test`.* TO 'mysqluser2'@'10.0.0.1' + +**************************************************** +** Show that it isn't possible to assign a definer without super user privileges +CREATE DEFINER=root@localhost PROCEDURE proc_user2 (OUT param1 INT) +SELECT COUNT(*) INTO param1 FROM t; +ERROR 42000: Access denied; you need the SUPER privilege for this operation +SET GLOBAL automatic_sp_privileges = @global_automatic_sp_privileges; === modified file 'mysql-test/t/automatic_sp_privileges_func.test' --- a/mysql-test/t/automatic_sp_privileges_func.test 2008-04-11 19:59:33 +0000 +++ b/mysql-test/t/automatic_sp_privileges_func.test 2008-06-12 22:21:33 +0000 @@ -148,3 +148,177 @@ DROP USER 'userTest1'@'localhost'; DROP TABLE t1; + +--echo # +--echo # Bug#31501 Stored Routines: droping stored procedure revokes all assotiated privileges +--echo # + +GRANT CREATE ROUTINE ON test.* TO mysqluser1@localhost; +GRANT CREATE ROUTINE ON test.* TO mysqluser2@localhost; +GRANT CREATE ROUTINE ON test.* TO mysqluser2@10.0.0.1; + +--connect (thread1, localhost, mysqluser1,,) +--connect (thread2, localhost, mysqluser2,,) +--connection default +SET @global_automatic_sp_privileges = @@GLOBAL.automatic_sp_privileges; +SET GLOBAL automatic_sp_privileges=ON; +--disable_warnings +DROP PROCEDURE IF EXISTS test.proc_user1; +DROP PROCEDURE IF EXISTS test.proc_user2; +--enable_warnings + +--connection thread1 +--echo ** Create a procedure as definer= mysqluser1 +CREATE PROCEDURE proc_user1 (OUT param1 INT) + SELECT COUNT(*) INTO param1 FROM t; +--echo +--echo ** mysqluser1 gained privileges for procedure. + SHOW GRANTS FOR mysqluser1@localhost; + +--echo +--echo ** Connecting as mysqluser2 +--connection thread2 +--echo ** Create a procedure as definer= mysqluser2. +CREATE PROCEDURE proc_user2 (OUT param1 INT) + SELECT COUNT(*) INTO param1 FROM t; + +--echo +--echo ** Creating SP and get automatic privileges (gaining EXECUTE,ALTER ROUTINE) +SHOW GRANTS FOR mysqluser2@localhost; + +--echo +--echo ** Dropping procedure with definer= mysqluser1 as current_user= mysqluser2 +--echo ** First assign ALTER ROUTINE to mysqluser2 on test.proc_user1. +--connection default +GRANT ALTER ROUTINE ON PROCEDURE test.proc_user1 TO mysqluser2@localhost; +--connection thread2 +--echo ** This won't result in any grant changes because we don't remove grants from other users automatically. +DROP PROCEDURE test.proc_user1; + +--connection thread1 +--echo +--echo ** Grants for mysqluser1 will still contain EXECUTE, ALTER ROUTINE privileges +SHOW GRANTS FOR mysqluser1@localhost; + +--connection thread2 +--echo +--echo ** Dropping procedure with definer= mysqluser2 as current_user= mysqluser2 +DROP PROCEDURE test.proc_user2; +--echo ** Execution grants for mysqluser2 should now be revoked: +SHOW GRANTS FOR mysqluser2@localhost; +--connection default +--echo +--echo ** Revoke ALTER, EXECUTE ROUTINE privileges from mysqluser1 +REVOKE ALL PRIVILEGES ON PROCEDURE test.proc_user1 FROM mysqluser1@localhost; +--echo +--echo ************************************************** +--echo +--echo ** Repeating test with automatic_privileges=OFF; +SET GLOBAL automatic_sp_privileges=OFF; +--connection thread1 +--echo ** Create a procedure as definer= mysqluser1 +CREATE PROCEDURE proc_user1 (OUT param1 INT) + SELECT COUNT(*) INTO param1 FROM t; +--echo +--echo ** mysqluser1 has not gained privileges for procedure: + SHOW GRANTS FOR mysqluser1@localhost; + +--echo +--echo ** Connecting as mysqluser2 +--connection thread2 +--echo ** Create a procedure as definer= mysqluser2. +CREATE PROCEDURE proc_user2 (OUT param1 INT) + SELECT COUNT(*) INTO param1 FROM t; + +--echo +--echo ** Creating SP and but hasn't gained any automatic privileges: +SHOW GRANTS FOR mysqluser2@localhost; + +--echo +--echo ** Dropping procedure with definer= mysqluser1 as current_user= mysqluser2 +--echo ** First assign ALTER ROUTINE to mysqluser2 on test.proc_user1. +--connection default +GRANT ALTER ROUTINE ON PROCEDURE test.proc_user1 TO mysqluser2@localhost; +--connection thread2 +--echo ** This won't result in any grant changes because we don't remove grants from other users automatically. +DROP PROCEDURE test.proc_user1; + +--connection thread1 +--echo +--echo ** Grants for mysqluser1 will remain the same: +SHOW GRANTS FOR mysqluser1@localhost; + +--echo +--echo ** Dropping procedure with definer= mysqluser2 as current_user= mysqluser2 +--echo ** First assign ALTER ROUTINE to mysqluser2 on test.proc_user1. +--connection default +GRANT ALTER ROUTINE ON PROCEDURE test.proc_user2 TO mysqluser2@localhost; +--connection thread2 + +DROP PROCEDURE test.proc_user2; +--echo ** Grants for mysqluser2 should now contain CREATE, ALTER ROUTINE: +SHOW GRANTS FOR 'mysqluser2'@'localhost'; + +--connection default +REVOKE ALL PRIVILEGES ON PROCEDURE test.proc_user1 FROM mysqluser2@localhost; +REVOKE ALL PRIVILEGES ON PROCEDURE test.proc_user2 FROM mysqluser2@localhost; +--echo +--echo ************************************************** +--echo +--echo ** Running test with DEFINER directive +--echo +SET GLOBAL automatic_sp_privileges=ON; + +--echo ** Create a procedure as definer= mysqluser2 +CREATE DEFINER=mysqluser2@localhost PROCEDURE proc_user1 (OUT param1 INT) + SELECT COUNT(*) INTO param1 FROM t; + +--echo ** Create a procedure as definer= mysqluser2. +CREATE DEFINER=mysqluser2@localhost PROCEDURE proc_user2 (OUT param1 INT) + SELECT COUNT(*) INTO param1 FROM t; + +--connection thread2 +--echo +--echo ** mysqluser2 gained automatic privileges for two procedures (gaining EXECUTE,ALTER ROUTINE) +SHOW GRANTS FOR 'mysqluser2'@'localhost'; +--echo ** Make sure only user account tied to a certain host is modified: +SHOW GRANTS FOR mysqluser2@10.0.0.1; + +--echo +--echo ** Dropping procedure with definer= mysqluser1 as current_user= mysqluser2 +--echo ** Grants will be dropped automatically for mysqluser2. +--echo ** To be sure that mysqluser1 is unaffected we add EXECUTE ROUTINE privilege. +--connection default +GRANT EXECUTE ON PROCEDURE test.proc_user1 TO mysqluser1@localhost; +--connection thread2 +DROP PROCEDURE test.proc_user1; + +--connection thread1 +--echo +--echo ** Grants for mysqluser1 will still have the EXECUTE privileges +SHOW GRANTS FOR mysqluser1@localhost; + +--connection thread2 +--echo +--echo ** While mysqluser2 will have lost these privileges for proc_user1: +SHOW GRANTS FOR mysqluser2@localhost; + +--echo +--echo ** Dropping procedure with definer= mysqluser2 as current_user= mysqluser2 +DROP PROCEDURE test.proc_user2; +--echo ** Execution grants for mysqluser2 should now be revoked: +SHOW GRANTS FOR mysqluser2@localhost; +--echo ** Make sure only user account tied to a certain host is modified: +SHOW GRANTS FOR mysqluser2@10.0.0.1; + +--echo +--echo **************************************************** +--echo ** Show that it isn't possible to assign a definer without super user privileges +--error ER_SPECIFIC_ACCESS_DENIED_ERROR +CREATE DEFINER=root@localhost PROCEDURE proc_user2 (OUT param1 INT) + SELECT COUNT(*) INTO param1 FROM t; +--connection default +--disconnect thread1 +--disconnect thread2 +SET GLOBAL automatic_sp_privileges = @global_automatic_sp_privileges; + === modified file 'sql/sp.cc' --- a/sql/sp.cc 2008-05-21 10:17:29 +0000 +++ b/sql/sp.cc 2008-06-12 22:21:33 +0000 @@ -969,7 +969,7 @@ */ int -sp_drop_routine(THD *thd, int type, sp_name *name) +sp_drop_routine(THD *thd, int type, sp_name *name, String *found_entry) { TABLE *table; int ret; @@ -991,6 +991,15 @@ DBUG_RETURN(SP_OPEN_TABLE_FAILED); if ((ret= db_find_routine_aux(thd, type, name, table)) == SP_OK) { + bool no_such_field= get_field(thd->mem_root, + table->field[MYSQL_PROC_FIELD_DEFINER], + found_entry); + if (no_such_field) + { + ret= SP_DELETE_ROW_FAILED; + goto error; + } + if (table->file->ha_delete_row(table->record[0])) ret= SP_DELETE_ROW_FAILED; } @@ -1000,7 +1009,8 @@ write_bin_log(thd, TRUE, thd->query, thd->query_length); sp_cache_invalidate(); } - + +error: close_thread_tables(thd); DBUG_RETURN(ret); } === modified file 'sql/sp.h' --- a/sql/sp.h 2008-04-08 16:36:01 +0000 +++ b/sql/sp.h 2008-06-12 22:21:33 +0000 @@ -83,7 +83,7 @@ sp_update_routine(THD *thd, int type, sp_name *name, st_sp_chistics *chistics); int -sp_drop_routine(THD *thd, int type, sp_name *name); +sp_drop_routine(THD *thd, int type, sp_name *name, String *sp_definer); /* Procedures for pre-caching of stored routines and building table list === modified file 'sql/sql_acl.cc' --- a/sql/sql_acl.cc 2008-05-21 10:17:29 +0000 +++ b/sql/sql_acl.cc 2008-06-12 22:21:33 +0000 @@ -30,6 +30,7 @@ #include #include "sp_head.h" #include "sp.h" +#include time_t mysql_db_table_last_check= 0L; @@ -6163,16 +6164,24 @@ /** - Revoke privileges for all users on a stored procedure. Use an error handler - that converts errors about missing grants into warnings. + Revoke privileges on a stored procedures where the definer of the procedure + match the invoker (current user). Use an error handler that converts errors + about missing grants into warnings. + @note + This statement will be replicated as a statement, even when using + row-based replication. The flag will be reset at the end of the + statement. + @param thd The current thread. @param db DB of the stored procedure @param name Name of the stored procedure - + @param + sp_definer Name + host of the definer + @retval 0 OK. @retval @@ -6180,15 +6189,34 @@ */ bool sp_revoke_privileges(THD *thd, const char *sp_db, const char *sp_name, - bool is_proc) + bool is_proc, String *sp_definer) { - uint counter, revoked; + int result; + Security_context *sctx= thd->security_ctx; TABLE_LIST tables[GRANT_TABLES]; HASH *hash= is_proc ? &proc_priv_hash : &func_priv_hash; Silence_routine_definer_errors error_handler; DBUG_ENTER("sp_revoke_privileges"); + char user_name_holder[USERNAME_LENGTH + 1]; + LEX_STRING user_name= { user_name_holder, USERNAME_LENGTH }; + + char host_name_holder[HOSTNAME_LENGTH + 1]; + LEX_STRING host_name= { host_name_holder, HOSTNAME_LENGTH }; + + parse_user(sp_definer->c_ptr(), sp_definer->length(), + user_name.str, &user_name.length, + host_name.str, &host_name.length); + + /* + Only revoke privileges if definer user name corresponds to the + current user. + */ + if (my_strcasecmp(system_charset_info, sctx->user, + user_name.str)) + DBUG_RETURN(0); + if ((result= open_grant_tables(thd, tables))) DBUG_RETURN(result != 1); @@ -6198,41 +6226,34 @@ rw_wrlock(&LOCK_grant); pthread_mutex_lock(&acl_cache->lock); - /* - This statement will be replicated as a statement, even when using - row-based replication. The flag will be reset at the end of the - statement. - */ thd->clear_current_stmt_binlog_row_based(); - + /* Remove procedure access */ - do + uint key_length= user_name.length+strlen(sp_db)+strlen(sp_name)+3; + uchar hash_key[key_length]; + + /* + Construct hash key= user name + \0 + database + \0 + sp name + \0 + */ + strmov(strmov(strmov((char*)hash_key,user_name.str)+1,sp_db)+1,sp_name); + + HASH_SEARCH_STATE state; + GRANT_NAME *grant_proc; + for (grant_proc= (GRANT_NAME*) hash_first(hash, hash_key, + key_length, &state); + grant_proc ; + grant_proc= (GRANT_NAME*) hash_next(hash, hash_key, + key_length, &state)) { - for (counter= 0, revoked= 0 ; counter < hash->records ; ) - { - GRANT_NAME *grant_proc= (GRANT_NAME*) hash_element(hash, counter); - if (!my_strcasecmp(system_charset_info, grant_proc->db, sp_db) && - !my_strcasecmp(system_charset_info, grant_proc->tname, sp_name)) - { - LEX_USER lex_user; - lex_user.user.str= grant_proc->user; - lex_user.user.length= strlen(grant_proc->user); - lex_user.host.str= grant_proc->host.hostname ? - grant_proc->host.hostname : (char*)""; - lex_user.host.length= grant_proc->host.hostname ? - strlen(grant_proc->host.hostname) : 0; + LEX_USER lex_user; + lex_user.user.str= grant_proc->user; + lex_user.user.length= strlen(grant_proc->user); + lex_user.host= host_name; - if (replace_routine_table(thd,grant_proc,tables[4].table,lex_user, - grant_proc->db, grant_proc->tname, - is_proc, ~(ulong)0, 1) == 0) - { - revoked= 1; - continue; - } - } - counter++; - } - } while (revoked); + replace_routine_table(thd,grant_proc,tables[4].table,lex_user, + grant_proc->db, grant_proc->tname, + is_proc, ~(ulong)0, 1); + } pthread_mutex_unlock(&acl_cache->lock); rw_unlock(&LOCK_grant); @@ -6244,18 +6265,16 @@ } -/* - Grant EXECUTE,ALTER privilege for a stored procedure - - SYNOPSIS - sp_grant_privileges() - thd The current thread. - db DB of the stored procedure - name Name of the stored procedure - - RETURN - 0 OK. - < 0 Error. Error message not yet sent. +/** + Grant EXECUTE,ALTER privilege for a stored procedure to current user or + the statement assigned definer if specified. + + @param thd The current thread. + @param db DB of the stored procedure + @param name Name of the stored procedure + + @retval 0 Success + @retval <0 Error. Error message not yet sent. */ int sp_grant_privileges(THD *thd, const char *sp_db, const char *sp_name, @@ -6270,24 +6289,32 @@ char passwd_buff[SCRAMBLED_PASSWORD_CHAR_LENGTH+1]; DBUG_ENTER("sp_grant_privileges"); - if (!(combo=(LEX_USER*) thd->alloc(sizeof(st_lex_user)))) - DBUG_RETURN(TRUE); - - combo->user.str= sctx->user; - - pthread_mutex_lock(&acl_cache->lock); - - if ((au= find_acl_user(combo->host.str=(char*)sctx->host_or_ip,combo->user.str,FALSE))) - goto found_acl; - if ((au= find_acl_user(combo->host.str=(char*)sctx->host, combo->user.str,FALSE))) - goto found_acl; - if ((au= find_acl_user(combo->host.str=(char*)sctx->ip, combo->user.str,FALSE))) - goto found_acl; - if((au= find_acl_user(combo->host.str=(char*)"%", combo->user.str, FALSE))) - goto found_acl; - - pthread_mutex_unlock(&acl_cache->lock); - DBUG_RETURN(TRUE); + if (thd->lex->definer) + { + combo= thd->lex->definer; + pthread_mutex_lock(&acl_cache->lock); + if ((au= find_acl_user(combo->host.str, combo->user.str, FALSE))) + goto found_acl; + pthread_mutex_unlock(&acl_cache->lock); + DBUG_RETURN(TRUE); + } + else + { + if (!(combo=(LEX_USER*) thd->alloc(sizeof(st_lex_user)))) + DBUG_RETURN(TRUE); + combo->user.str= sctx->user; + pthread_mutex_lock(&acl_cache->lock); + if ((au= find_acl_user(combo->host.str=(char*)sctx->host_or_ip,combo->user.str,FALSE))) + goto found_acl; + if ((au= find_acl_user(combo->host.str=(char*)sctx->host, combo->user.str,FALSE))) + goto found_acl; + if ((au= find_acl_user(combo->host.str=(char*)sctx->ip, combo->user.str,FALSE))) + goto found_acl; + if((au= find_acl_user(combo->host.str=(char*)"%", combo->user.str, FALSE))) + goto found_acl; + pthread_mutex_unlock(&acl_cache->lock); + DBUG_RETURN(TRUE); + } found_acl: pthread_mutex_unlock(&acl_cache->lock); @@ -6303,7 +6330,6 @@ combo->host.str= thd->strmake(combo->host.str,combo->host.length); combo->user.str= thd->strmake(combo->user.str,combo->user.length); - if(au && au->salt_len) { if (au->salt_len == SCRAMBLE_LENGTH) === modified file 'sql/sql_acl.h' --- a/sql/sql_acl.h 2008-03-28 13:33:31 +0000 +++ b/sql/sql_acl.h 2008-06-12 22:21:33 +0000 @@ -263,7 +263,7 @@ void fill_effective_table_privileges(THD *thd, GRANT_INFO *grant, const char *db, const char *table); bool sp_revoke_privileges(THD *thd, const char *sp_db, const char *sp_name, - bool is_proc); + bool is_proc, String *sp_definer); int sp_grant_privileges(THD *thd, const char *sp_db, const char *sp_name, bool is_proc); bool check_routine_level_acl(THD *thd, const char *db, const char *name, === modified file 'sql/sql_parse.cc' --- a/sql/sql_parse.cc 2008-05-21 10:17:29 +0000 +++ b/sql/sql_parse.cc 2008-06-12 22:21:33 +0000 @@ -4057,11 +4057,7 @@ switch (sp_result) { case SP_OK: #ifndef NO_EMBEDDED_ACCESS_CHECKS - /* only add privileges if really neccessary */ - if (sp_automatic_privileges && !opt_noacl && - check_routine_access(thd, DEFAULT_CREATE_PROC_ACLS, - lex->sphead->m_db.str, name, - lex->sql_command == SQLCOM_CREATE_PROCEDURE, 1)) + if (sp_automatic_privileges && !opt_noacl) { if (sp_grant_privileges(thd, lex->sphead->m_db.str, name, lex->sql_command == SQLCOM_CREATE_PROCEDURE)) @@ -4283,8 +4279,13 @@ case SQLCOM_DROP_FUNCTION: { int sp_result; - int type= (lex->sql_command == SQLCOM_DROP_PROCEDURE ? - TYPE_ENUM_PROCEDURE : TYPE_ENUM_FUNCTION); + + String sp_definer; + char *db= lex->spname->m_db.str; + char *name= lex->spname->m_name.str; + int type= lex->sql_command == SQLCOM_DROP_PROCEDURE ? + TYPE_ENUM_PROCEDURE : + TYPE_ENUM_FUNCTION; sp_result= sp_routine_exists_in_table(thd, type, lex->spname); mysql_reset_errors(thd, 0); @@ -4299,23 +4300,14 @@ if (end_active_trans(thd)) goto error; -#ifndef NO_EMBEDDED_ACCESS_CHECKS - if (sp_automatic_privileges && !opt_noacl && - sp_revoke_privileges(thd, db, name, - lex->sql_command == SQLCOM_DROP_PROCEDURE)) - { - push_warning(thd, MYSQL_ERROR::WARN_LEVEL_WARN, - ER_PROC_AUTO_REVOKE_FAIL, - ER(ER_PROC_AUTO_REVOKE_FAIL)); - } -#endif + /* Conditionally writes to binlog */ int type= lex->sql_command == SQLCOM_DROP_PROCEDURE ? TYPE_ENUM_PROCEDURE : TYPE_ENUM_FUNCTION; - sp_result= sp_drop_routine(thd, type, lex->spname); + sp_result= sp_drop_routine(thd, type, lex->spname, &sp_definer); } else { @@ -4346,6 +4338,17 @@ } } res= sp_result; +#ifndef NO_EMBEDDED_ACCESS_CHECKS + if (sp_automatic_privileges && !opt_noacl && + sp_revoke_privileges(thd, db, name, + lex->sql_command == SQLCOM_DROP_PROCEDURE, + &sp_definer)) + { + push_warning(thd, MYSQL_ERROR::WARN_LEVEL_WARN, + ER_PROC_AUTO_REVOKE_FAIL, + ER(ER_PROC_AUTO_REVOKE_FAIL)); + } +#endif switch (sp_result) { case SP_OK: my_ok(thd);