List:General Discussion« Previous MessageNext Message »
From:Claudio Nanni Date:March 18 2009 6:52pm
Subject:Re: how can I make a stored procedure executable by "public"?
View as plain text  
Ok sorry, I did not understand at first.

GRANT INSERT on mydb.audit_table to ''@'%';

should do the work.



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
>     (
>     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
> -- 
> Jim Lyons
> Web developer / Database administrator

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