Re: how can I make a stored procedure executable by public?
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 jlyons4...@gmail.com 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
Re: how can I make a stored procedure executable by public?
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.na...@gmail.comwrote: 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 jlyons4...@gmail.com 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
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 claudio.na...@gmail.com mailto:claudio.na...@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.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 jlyons4...@gmail.com mailto:jlyons4...@gmail.com 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=arch...@jab.org
Re: how can I make a stored procedure executable by public?
great! thanks much. On Wed, Mar 18, 2009 at 1:52 PM, Claudio Nanni claudio.na...@gmail.comwrote: 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.na...@gmail.commailto: claudio.na...@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.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 jlyons4...@gmail.com mailto:jlyons4...@gmail.com 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
