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!

--
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------

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

On Jan 7, 2008 2:34 AM, Shawn Green <shawn@mysql.com> 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/...trictions.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------