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]
