Gleb, I had already submitted that bug, around a week ago, you can see my version here:
http://bugs.mysql.com/bug.php?id=12307 Apparently it's been fixed in 5.0.11.. Now documenting. You may want to update your bug to point to mine and close it off ;) The procedure in the bug causes some interesting issues when thinking about security levels also. Basically, it was a test case procedure that I had wrote as an example for my blog (I'm doing kind of a 5.0 new features run on it at the moment) - that does the kind of thing being asked about here. >From the security perspective, you can see that SQL SECURITY is set to INVOKER. In other words the procedure will run with all the privileges that the person that calls the procedure has.. This is done because we would pick up on who's running the procedure, to compare against a list of "privileged" users to make sure that they can actually run the procedure. However, as this is the case - they can't then go and UPDATE the mysql.user table, without direct privileges to it. If you ran it with SQL SECURITY DEFINER, and created the procedure as say, root, then CURRENT_USER() will always return [EMAIL PROTECTED], which would be kind of useless in this context. To run this under SQL SECURITY INVOKER you would have to, at the very minimum, "GRANT SELECT (user), UPDATE (password) ON mysql.user TO 'invoker'@'host' ...". To run the FLUSH (which doesn't look like it's going to be allowed anyway), you would also need to GRANT the RELOAD privilege to the user as well. Then - what's to stop the person going an updating the table directly..? ;) Well, to get this to work all you have to do is set the procedure to run as SQL SECURITY DEFINER, and create the procedures with a user that has all of the privileges to run them, drop all of the user checking stuff - then simply GRANT EXECUTE on each procedure to each user that should be allowed to run them.. This way the users will have no direct table level privileges, just very specific PROCEDURE EXECUTE permissions. To add the privileges with the example above would be "GRANT EXECUTE ON PROCEDURE admin.update_user_password TO .." to each user that you want to allow to update a password. Hope this helps out a little with the original question as well.. Mark Mark Leith Cool-Tools UK Limited http://www.cool-tools.co.uk http://leithal.cool-tools.co.uk > -----Original Message----- > From: Gleb Paharenko [mailto:[EMAIL PROTECTED] > Sent: 10 August 2005 09:55 > To: mysql@lists.mysql.com > Subject: Re: Table and Data access only through procedures > > Hello. > > > > MySQL 5.0.10 offers modifying tables from stored procedures, > > however I've met some problems. I've tried to create a procedure > > like your DELETE_USER, but DROP USER seems to accept only literal > > strings, not variables, while direct modifying of GRANT tables and > > calling FLUSH privileges leads server to hang. See my bug report: > > > > http://bugs.mysql.com/bug.php?id=12485 > -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.5/67 - Release Date: 09/08/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
