On Apr 30, 2008, at 10:13 AM, Alexander Nozdrin wrote:
> Hi Kristofer,
>
> I think some work still has to be done in the scope of this bug.
> Please see
> my notes below.
>
>> ChangeSet@stripped, 2008-04-29 13:36:29+02:00, thek@adventure.(none)
>> +7 -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.
>
> Okay, first of all, let's clarify what we're going to achieve.
>
> 1. The Manual says:
> - The ALTER ROUTINE privilege is needed to alter or drop stored
> routines.
> This privilege is granted automatically to the creator of a
> routine if
> necessary, and dropped when the routine creator drops the routine.
>
> - The EXECUTE privilege is required to execute stored routines.
> However,
> this privilege is granted automatically to the creator of a
> routine if
> necessary (and dropped when the creator drops the routine).
>
> - If the automatic_sp_privileges system variable is 0, the EXECUTE
> and
> ALTER ROUTINE privileges are not automatically granted and dropped.
>
> First of all, I believe, 'the creator of a routine' is 'the definer
> of a
> routine', because we don't have a notion of 'a creator' rather than
> definer. I would ask docs-team to explicitly clarify that.
You certainly have the notion of a creator when the CREATE statement is
executed. It's the account for the user who executes the statement.
I myself would expect that to be the user who gets the privileges, not
the DEFINER value.
Otherwise, what would happen if the DEFINER account does not exist? You
can do this only as root, but it's possible. Should you get an
additional
warning?
But you're right. The desired behavior should be clarified in the
manual,
once we know what that behavior is.
> So, that means the following:
> (expect automatic_sp_privileges = ON)
>
> - CREATE PROCEDURE p1() SELECT 1;
> -> GRANT EXECUTE, ALTER ON db.p1 TO <current user>
> (since definer is the current user)
>
> - CREATE DEFINER=a@b PROCEDURE p1() SELECT 1;
> -> GRANT EXECUTE, ALTER ON db.p1 TO a@b
> (since definer is a@b)
>
> (and frankly speaking, your patch does not work that way AFAICS.
> Also, I'd
> add such a tests to the test case)
>
> Then, the question is -- what about SQL SECURITY INVOKER? What is the
> reason to automatically grant access for the definer to stored routine
> declared as SQL SECURITY INVOKER? Should we really do that? I'd
> clarify
> that with PeterG and/or architects.
>
> Finally, what if a stored routine was created with
> automatic_sp_privileges == OFF and then dropped with
> automatic_sp_privileges == ON? I guess, the user privileges will be
> dropped. This is a way for the user to shoot himself in the foot.
> So, I'd:
> - request to clarify that in the manual;
> - rephrase your second statement (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) -- it is *not* only
> automatic
> privileges.
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
www.mysql.com
