Below is the list of changes that have just been committed into a local
6.0 repository of thek. When thek 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, 2008-04-23 13:40:31+02:00, thek@adventure.(none) +3 -0
Bug#31501 Stored Routines: droping stored procedure revokes all assotiated privileges
When a routine was dropped all associated privileges were removed even
though they weren't associated with the definer of the routine.
This patch changes the behavior so that only the automatic privileges set
by the definer of the routine are dropped when the routine is dropped.
mysql-test/r/sp-destruct.result@stripped, 2008-04-23 13:40:26+02:00, thek@adventure.(none) +51 -0
Added test case
mysql-test/t/sp-destruct.test@stripped, 2008-04-23 13:40:26+02:00, thek@adventure.(none) +48 -0
Added test case
sql/sql_acl.cc@stripped, 2008-04-23 13:40:26+02:00, thek@adventure.(none) +33 -9
In order to compare the credentials of the definer of the
procedure with grant_proc->user, the stored procedure must be
retrieved from the database and loaded into sp_head.
diff -Nrup a/mysql-test/r/sp-destruct.result b/mysql-test/r/sp-destruct.result
--- a/mysql-test/r/sp-destruct.result 2007-06-28 19:34:47 +02:00
+++ b/mysql-test/r/sp-destruct.result 2008-04-23 13:40:26 +02:00
@@ -82,9 +82,60 @@ ERROR HY000: Failed to load routine test
drop trigger t1_ai;
drop table t1;
drop function bug14233_1;
+Warnings:
+Warning 1601 Creation context of stored routine `test`.`bug14233_1` is invalid
+Error 1415 Not allowed to return a result set from a function
drop function bug14233_2;
+Warnings:
+Warning 1601 Creation context of stored routine `test`.`bug14233_2` is invalid
+Error 1320 No RETURN found in FUNCTION test.bug14233_2
drop procedure bug14233_3;
+Warnings:
+Warning 1601 Creation context of stored routine `test`.`bug14233_3` is invalid
+Error 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'wpsj sa ^#!@ ' at line 3
show procedure status;
Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation
show function status;
Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation
+#
+# Bug#31501 Stored Routines: droping stored procedure revokes all assotiated privileges
+#
+SET GLOBAL automatic_sp_privileges=ON;
+DROP PROCEDURE IF EXISTS test.bug31501_proc1;
+Warnings:
+Note 1305 PROCEDURE bug31501_proc1 does not exist
+create procedure bug31501_proc1 (OUT param1 INT) BEGIN
+SELECT COUNT(*) INTO param1 FROM t;
+END; //
+** Grant CREATE ROUTINE to user bug31501_user.
+GRANT CREATE ROUTINE ON test.* TO 'bug31501_user'@'localhost';
+SHOW GRANTS FOR 'bug31501_user'@'localhost';
+Grants for bug31501_user@localhost
+GRANT USAGE ON *.* TO 'bug31501_user'@'localhost'
+GRANT CREATE ROUTINE ON `test`.* TO 'bug31501_user'@'localhost'
+** Connecting as bug31501_user
+create procedure bug31501_proc2 (OUT param1 INT) BEGIN
+SELECT COUNT(*) INTO param1 FROM t;
+END; //
+** Creating SP and get automatic privileges (gaining EXECUTE,ALTER ROUTINE)
+SHOW GRANTS FOR 'bug31501_user'@'localhost';
+Grants for bug31501_user@localhost
+GRANT USAGE ON *.* TO 'bug31501_user'@'localhost'
+GRANT CREATE ROUTINE ON `test`.* TO 'bug31501_user'@'localhost'
+GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `test`.`bug31501_proc2` TO 'bug31501_user'@'localhost'
+** Dropping procedure with definer=root
+** This won't result in any grant changes because we don't remove grants from other users automatically.
+DROP PROCEDURE test.bug31501_proc1;
+SHOW GRANTS FOR 'bug31501_user'@'localhost';
+Grants for bug31501_user@localhost
+GRANT USAGE ON *.* TO 'bug31501_user'@'localhost'
+GRANT CREATE ROUTINE ON `test`.* TO 'bug31501_user'@'localhost'
+GRANT EXECUTE, ALTER ROUTINE ON PROCEDURE `test`.`bug31501_proc2` TO 'bug31501_user'@'localhost'
+** Not dropping procedure as definer=bug31501_user
+DROP PROCEDURE test.bug31501_proc2;
+** Execution grants for bug31501_user should now be revoked.
+SHOW GRANTS FOR 'bug31501_user'@'localhost';
+Grants for bug31501_user@localhost
+GRANT USAGE ON *.* TO 'bug31501_user'@'localhost'
+GRANT CREATE ROUTINE ON `test`.* TO 'bug31501_user'@'localhost'
+SET GLOBAL automatic_sp_privileges=DEFAULT;
diff -Nrup a/mysql-test/t/sp-destruct.test b/mysql-test/t/sp-destruct.test
--- a/mysql-test/t/sp-destruct.test 2007-08-07 11:40:51 +02:00
+++ b/mysql-test/t/sp-destruct.test 2008-04-23 13:40:26 +02:00
@@ -154,3 +154,51 @@ drop procedure bug14233_3;
# Assert: These should show nothing.
show procedure status;
show function status;
+
+--echo #
+--echo # Bug#31501 Stored Routines: droping stored procedure revokes all assotiated privileges
+--echo #
+
+SET GLOBAL automatic_sp_privileges=ON;
+DROP PROCEDURE IF EXISTS test.bug31501_proc1;
+
+delimiter //;
+create procedure bug31501_proc1 (OUT param1 INT) BEGIN
+ SELECT COUNT(*) INTO param1 FROM t;
+END; //
+delimiter ;//
+
+--echo ** Grant CREATE ROUTINE to user bug31501_user.
+GRANT CREATE ROUTINE ON test.* TO 'bug31501_user'@'localhost';
+SHOW GRANTS FOR 'bug31501_user'@'localhost';
+
+--echo ** Connecting as bug31501_user
+--connect (bug31501_con,localhost,bug31501_user,,test,,)
+--connection bug31501_con
+delimiter //;
+create procedure bug31501_proc2 (OUT param1 INT) BEGIN
+ SELECT COUNT(*) INTO param1 FROM t;
+END; //
+delimiter ;//
+--echo ** Creating SP and get automatic privileges (gaining EXECUTE,ALTER ROUTINE)
+SHOW GRANTS FOR 'bug31501_user'@'localhost';
+
+--connection default
+
+--echo ** Dropping procedure with definer=root
+--echo ** This won't result in any grant changes because we don't remove grants from other users automatically.
+DROP PROCEDURE test.bug31501_proc1;
+SHOW GRANTS FOR 'bug31501_user'@'localhost';
+
+--echo ** Not dropping procedure as definer=bug31501_user
+DROP PROCEDURE test.bug31501_proc2;
+--echo ** Execution grants for bug31501_user should now be revoked.
+SHOW GRANTS FOR 'bug31501_user'@'localhost';
+--connection default
+--disconnect bug31501_con
+SET GLOBAL automatic_sp_privileges=DEFAULT;
+
+
+
+
+
diff -Nrup a/sql/sql_acl.cc b/sql/sql_acl.cc
--- a/sql/sql_acl.cc 2008-04-14 13:30:04 +02:00
+++ b/sql/sql_acl.cc 2008-04-23 13:40:26 +02:00
@@ -2830,6 +2830,7 @@ static int replace_routine_table(THD *th
TRUE);
store_record(table,record[1]); // store at pos 1
+
if (table->file->index_read_idx_map(table->record[0], 0,
(uchar*) table->field[0]->ptr,
HA_WHOLE_KEY,
@@ -2851,9 +2852,9 @@ static int replace_routine_table(THD *th
}
store_proc_rights= get_rights_for_procedure(rights);
+ ulong j;
if (old_row_exists)
{
- ulong j;
store_record(table,record[1]);
j= (ulong) table->field[6]->val_int();
@@ -2882,7 +2883,8 @@ static int replace_routine_table(THD *th
goto table_error;
}
else if ((error= table->file->ha_delete_row(table->record[1])))
- goto table_error;
+ goto table_error;
+ }
}
else
{
@@ -6192,9 +6194,6 @@ bool sp_revoke_privileges(THD *thd, cons
if ((result= open_grant_tables(thd, tables)))
DBUG_RETURN(result != 1);
- /* Be sure to pop this before exiting this scope! */
- thd->push_internal_handler(&error_handler);
-
rw_wrlock(&LOCK_grant);
VOID(pthread_mutex_lock(&acl_cache->lock));
@@ -6206,13 +6205,38 @@ bool sp_revoke_privileges(THD *thd, cons
thd->clear_current_stmt_binlog_row_based();
/* Remove procedure access */
+ int type= is_proc? TYPE_ENUM_PROCEDURE:TYPE_ENUM_FUNCTION;
+ sp_cache **cache= (is_proc? &thd->sp_proc_cache:&thd->sp_func_cache);
+ LEX_STRING lex_db;
+ LEX_STRING lex_name;
+ lex_db.length= strlen(sp_db);
+ lex_name.length= strlen(sp_name);
+ lex_db.str= thd->strmake(sp_db, lex_db.length);
+ lex_name.str= thd->strmake(sp_name, lex_name.length);
+ ::sp_name user(lex_db,lex_name,TRUE);
+ user.init_qname(thd);
+ sp_head *sp= sp_find_routine(thd, type, &user, cache, FALSE);
+ if (sp == NULL)
+ {
+ thd->main_da.reset_diagnostics_area();
+ goto err;
+ }
+
+ /*
+ Be sure to pop this before exiting this scope!
+ NOTE: handler can't be pushed before sp_find_routine because the error
+ system currently doesn't support more than one active handler.
+ */
+ thd->push_internal_handler(&error_handler);
do
{
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))
+ if (sp && !my_strcasecmp(system_charset_info, grant_proc->db, sp_db) &&
+ !my_strcasecmp(system_charset_info, grant_proc->tname, sp_name) &&
+ !my_strcasecmp(system_charset_info, grant_proc->user,
+ sp->m_definer_user.str))
{
LEX_USER lex_user;
lex_user.user.str= grant_proc->user;
@@ -6234,11 +6258,11 @@ bool sp_revoke_privileges(THD *thd, cons
}
} while (revoked);
+ thd->pop_internal_handler();
+err:
VOID(pthread_mutex_unlock(&acl_cache->lock));
rw_unlock(&LOCK_grant);
close_thread_tables(thd);
-
- thd->pop_internal_handler();
DBUG_RETURN(error_handler.has_errors());
}
