List:General Discussion« Previous MessageNext Message »
From:Jim Lyons Date:March 18 2009 7:21pm
Subject:Re: how can I make a stored procedure executable by "public"?
View as plain text  
great!  thanks much.

On Wed, Mar 18, 2009 at 1:52 PM, Claudio Nanni <claudio.nanni@stripped>wrote:

> Ok sorry, I did not understand at first.
>
> GRANT INSERT on mydb.audit_table to ''@'%';
>
> should do the work.
>
> Cheers
>
> Claudio
>
> Jim Lyons wrote:
>
>> Thanks, Claudio, but that's not quite it.  I'm not writing any procedure.
>>  I'm inserting code into procedures other people write.  I am taking each
>> procedure out of the mysql.proc table, inserting a few lines of code right
>> at the start of the body, and saving back into the proc table.  These lines
>> of code insert a line into my audit table.  I don't have any control over
>> what other people write, I just want to record when their procedures get
>> called.
>>
>> The genral log logs original calls to procedures, but I don't see that it
>> records calls made to one procedure from within another.
>>
>> On Wed, Mar 18, 2009 at 6:42 AM, Claudio Nanni
> <claudio.nanni@stripped<mailto:
>> claudio.nanni@stripped>> wrote:
>>
>>    Hello Jim,
>>
>>    If I unserstand well your needs the steps you need to do are:
>>
>>    Create one user X with insert privileges on the mydb.audit_table
>>
>>    Create the stored procedure specifying the user X both in the
>>    DEFINER section and in the SQL_SECURITY section
>>
>>    of the create procedure statement
>>    (http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html)
>>
>>    In this way who calls the stored procedure will have the table
>>    written with the grants of the original definer X.
>>
>>    Is it what you are looking for?
>>
>>    Claudio
>>
>>
>>    2009/3/17 Jim Lyons <jlyons4435@stripped
>>    <mailto:jlyons4435@stripped>>
>>
>>
>>        I am writing a tracking procedure that will be inserted into
>>        every procedure
>>        (regardless of who writes the procedure) that will insert a
>>        record into an
>>        audit table.  This means the procedure, regardless of who
>>        writes it, must
>>        have the permission to insert into the table.  I am going to
>>        modify the code
>>        of the procedures once they're stored in the database and the
>>        authors of the
>>        procedures will probably not know that I will be doing it
>>        (although it's not
>>        really a secret) and the way they code will not be altered in
>>        any way.
>>
>>        I would like to write a grant command like:
>>
>>        grant insert on mydb.audit_table to public
>>
>>        but I don't see anything in the manual Is there any way that I
>>        can do this.
>>        I know I can grant ALL privileges to a user, but I want to
>>        grant one
>>        privilege to all users, without having to loop through the
>>        mysql.user table
>>        and explicitly granting the insert privilege.
>>
>>        I guess I could put it in test, but then everyone could do
>>        anything with it,
>>        which would not be particularly desirable.  The table should
>>        be "insert
>>        only", not readable or updateable by anyone but the owner of
>>        "mydb".
>>
>>        Is there any way I can do this?
>>
>>        Thanks,
>>        Jim
>>
>>        --
>>        Jim Lyons
>>        Web developer / Database administrator
>>        http://www.weblyons.com
>>
>>
>>
>>
>>
>> --
>> Jim Lyons
>> Web developer / Database administrator
>> http://www.weblyons.com
>>
>
>


-- 
Jim Lyons
Web developer / Database administrator
http://www.weblyons.com

Thread
how can I make a stored procedure executable by "public"?Jim Lyons17 Mar
  • Re: how can I make a stored procedure executable by "public"?Claudio Nanni18 Mar
    • Re: how can I make a stored procedure executable by "public"?Jim Lyons18 Mar
      • Re: how can I make a stored procedure executable by "public"?Claudio Nanni18 Mar
        • Re: how can I make a stored procedure executable by "public"?Jim Lyons18 Mar