how can I make a stored procedure executable by "public"?

--0016e6475852f19b7d0465534711
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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

--0016e6475852f19b7d0465534711--

Re: how can I make a stored procedure executable by "public"?

--000e0cd251349ddcf50465632c3c
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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

> 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
>

--000e0cd251349ddcf50465632c3c--

Re: how can I make a stored procedure executable by "public"?

--0016e646900c1c854b046566305d
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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 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
>
>> 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

--0016e646900c1c854b046566305d--

Re: how can I make a stored procedure executable by "public"?

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
> > 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.htm l)
>
> 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 > >
>
> 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


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: how can I make a stored procedure executable by "public"?

--0016e6464f72425218046569976d
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

great! thanks much.

On Wed, Mar 18, 2009 at 1:52 PM, Claudio Nanni 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@gmail.com>> 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.htm l)
>>
>> 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 >> >
>>
>>
>> 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

--0016e6464f72425218046569976d--