Description:
Using revision-id:magne.mahre@oracle.com-20110104152359-l51xr7j27ca4mc1s .
I didn't check 5.1 and 5.5.
If a user has EXECUTE privilege on a stored function, and then this user disconnects, is dropped (with DROP USER) and recreated (with CREATE USER) by the DBA, and this _new_ user reconnects, then this _new_ user has the privilege of the old user (EXECUTE on the stored function).
This does not sound normal.
http://dev.mysql.com/doc/refman/5.5/en/privilege-changes.html
says
"If you modify the grant tables indirectly using account-management statements such as GRANT, REVOKE, SET PASSWORD, or RENAME USER, the server notices these changes and loads the grant tables into memory again immediately. "
-> I claim that my DROP USER is an indirect (and very recommended) way and should cause a reload like GRANT/REVOKE/RENAME USER.
"If you modify the grant tables directly using statements such as INSERT, UPDATE, or DELETE, your changes have no effect on privilege checking until you either restart the server or tell it to reload the tables. If you change the grant tables directly but forget to reload them, your changes have no effect until you restart the server. This may leave you wondering why your changes seem to make no difference! To tell the server to reload the grant tables, perform a flush-privileges operation."
-> I didn't use such INSERT/UPDATE/DELETE, I should not need FLUSH PRIVILEGES.
How to repeat:
Run this in an MTR test:
--echo as root, create objects and user
connection default;
select connection_id(), user(), current_user();
create database somedb;
use somedb;
delimiter |;
create function f1() returns int
begin
return 36;
end|
delimiter ;|
create user user2@localhost identified by '';
grant execute on function f1 to user2@localhost;
--echo as user2
connect (con2, localhost, user2,,);
connection con2;
select connection_id(), user(), current_user();
--echo user2 has EXECUTE on f1:
show grants;
--echo
--echo Now user2 disconnects
disconnect con2;
--echo and we wait until it is complete
--source include/wait_until_disconnected.inc
--echo
--echo as root
connection default;
--echo we can seet that user2 has completely disconnected,
show processlist;
--echo user2 exists:
select Host, User from mysql.user where User="user2";
--echo user2's privilege on f1 is present
select * from mysql.procs_priv;
--echo
--echo now we drop user2:
drop user user2@localhost;
--echo
--echo it's absent from mysql.user:
select Host, User from mysql.user where User="user2";
--echo user2's privilege on f1 has been deleted:
select * from mysql.procs_priv;
#flush privileges; # hides the bug
--echo Now root recreates user2 with no privilege:
create user user2@localhost identified by '';
--echo
--echo as user2
connect (con2, localhost, user2,,);
connection con2;
select connection_id(), user(), current_user();
--echo BUG user2 still has its old privilege
--echo according to SHOW GRANTS; DROP USER
--echo didn't delete it entirely!?
show grants;
--echo
--echo as root, do FLUSH PRIVILEGES
connection default;
flush privileges;
--echo
--echo as user2, now it's correct:
connection con2;
show grants;
--echo as root, cleanup to make MTR happy
connection default;
drop database somedb;
drop user user2@localhost;
Suggested fix:
It looks like DROP USER should contain an implicit FLUSH PRIVILEGES when it has finished dropping the user.
Note, the manual says:
http://dev.mysql.com/doc/refman/5.5/en/drop-user.html
"DROP USER does not automatically close any open user sessions. Rather, in the event that a user with an open session is dropped, the statement does not take effect until that user's session is closed."
-> but I took great care to wait for the old user2 to be disconnected before dropping it, so it should be really dropped
