On Jan 7, 2008 2:34 AM, Shawn Green <[EMAIL PROTECTED]> wrote: > > Eddie Cornejo wrote: > > I'm writing a cleanup script to remove database items created by my > > application. One of the things I would like to remove are all user > > accounts created through my application... This is proving to be > > harder than it sounds. > > There are two bits of information you do not seem to have. First is that > the DROP USER command only takes string literals as its parameter, it is > not engineered to take variables (either declared or user) as its parameter.
Thank you for your reply. After some other searching I found that CREATE USER has the same issue as outlined in MySQL bug 19584 reported in May 2006 http://bugs.mysql.com/bug.php?id=19584 It seems that it was accepted as a feature request that has not yet been implemented. > The second piece of information is that you do not need to run a DROP > USER command to delete user accounts. If you have sufficient > permissions, you can edit the `mysql`.`user` table directly and just > DELETE those rows you want to eliminate. Any changes you make will not > become visible to the server until after you either restart the daemon > or issue a FLUSH PRIVILEGES command. Yeah. This suffers from two points First, DROP USER abstracts the concept of dropping a user from how it's implemented. This is advantageous as I don't know (nor really should I care) where this user's id has been used. So I shouldn't have to worry whether he has specific permissions in tables_priv or procs_priv (or some other table_priv implemented in the next version of mysql). DROP USER should handle all this for me nicely, and playing around with the tables directly just means that I'll have to keep supporting my code as mysql develops. Secondly it appears that FLUSH PRIVILEGES cannot be called from a stored function - but is quite valid in a stored procedure. http://dev.mysql.com/doc/refman/5.0/en/routine-restrictions.html "For stored functions (but not stored procedures), the following additional statements or operations are disallowed: * FLUSH statements." This restriction is also true for stored procedures called from stored functions. So my current solution (as inelegant as it may be) is to have a stored procedure call my stored function that manually modifies the mysql.user table, scan through all other (known) table_priv tables, then returns, whereby my stored procedure does a flush and then does a select to indicate success. Yuck. > Does this give you enough information for you to automate your table > maintenance? Yes! Thank you very much. I was hoping I had missed something in the use of DROP USER or stored routine variables, but it appears that it simply isn't possible to use them the way I wanted. I'm looking forward to the implementation of the feature requested in bug 19584, but its been almost two years now so I don't think it's high on the list of feature requests. Regards, Eddie Cornejo > > -- > Shawn Green, Support Engineer > MySQL Inc., USA, www.mysql.com > Office: Blountville, TN > __ ___ ___ ____ __ > / |/ /_ __/ __/ __ \/ / > / /|_/ / // /\ \/ /_/ / /__ > /_/ /_/\_, /___/\___\_\___/ > <___/ > Join the Quality Contribution Program Today! > http://dev.mysql.com/qualitycontribution.html > > -- Eddie Cornejo -----BEGIN GEEK CODE BLOCK----- Version: 3.12 GIT d? s: a- C+++ UL+++ P++ L++ E- W+ N- o K- w++ O M-- V PS+ PE Y PGP++ t 5 X+ R tv-- b+ DI++++ D++ G e++ h r+++ y+++ ------END GEEK CODE BLOCK------ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
