Eddie Cornejo wrote:
I really hope I'm not overlooking something simple...
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.
I should note that it's a mysql script I plan to have the
administrator run as 'mysql -u root -p < uninstall.txt' - therefore
I'm limited in what I can do (to some degree)
After unsuccessfully trying to solve this through a variety of other
means, I've come to the conclusion that if I record all of the
usernames I create in a table, then I might be able to iterate over
that table and drop my users. This means creating a stored procedure
temporarily for the sole purpose of getting the benefits of CURSORs..
So I've come up with this:
DELIMITER //
CREATE PROCEDURE deleteAllUsers() DETERMINISTIC MODIFIES SQL DATA
BEGIN
DECLARE name VARCHAR(12);
DECLARE done INT DEFAULT 0;
DECLARE allUsers CURSOR FOR SELECT username FROM user WHERE active = TRUE;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN allUsers;
REPEAT
FETCH allUsers INTO name;
DROP USER name;
UNTIL done END REPEAT;
CLOSE allUsers;
END;
//
DELIMITER ;
It looks simple enough, however DROP USER tries to drop 'name' rather
than the next username in the list :(
I feel like I'm overlooking something really simple - however for the
life of me I can't figure it out. Is it possible to use DROP USER with
a variable name in a stored procedure? If not, is there some way to
drop a bunch of users given a table of usernames? Perhaps this problem
has only ever been tackled using a higher level language - but I was
hoping to solve it in a little mysql script.
Thanks. Hope you have a great 2008!
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.
http://dev.mysql.com/doc/refman/5.0/en/drop-user.html
One way that people have tried to work around this kind of limitation is
through the use of prepared statements. However our prepared statement
interface does not support the preparation of DROP USER statements
http://dev.mysql.com/doc/refman/5.0/en/sqlps.html
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.
http://dev.mysql.com/doc/refman/5.0/en/flush.html
Does this give you enough information for you to automate your table
maintenance?
--
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
