List:General Discussion« Previous MessageNext Message »
From:Jim Lyons Date:March 18 2009 3:17pm
Subject:Re: how can I make a stored procedure executable by "public"?
View as plain text  
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>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>
>
>> 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

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