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?
2009/3/17 Jim Lyons <jlyons4435@stripped>
> I am writing a tracking procedure that will be inserted into every
> (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
> of the procedures once they're stored in the database and the authors of
> procedures will probably not know that I will be doing it (although it's
> 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
> 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?
> Jim Lyons
> Web developer / Database administrator