#!/usr/bin/perl


use Getopt::Std;
use DBI;

use strict;


##
##  Default settings, adjust as you see fit.
##

my $db_name = 'mysql';
my $db_user = 'root';
my $db_pass = '';       # I would only set this on a "secure" machine.


my $opt = {};
my $opt_ok = getopts('d:fhHp:ru:v', $opt);


if ($opt->{h} or not $opt_ok) {
    print usage();
    exit $opt_ok ? 0 : 1;
}
elsif ($opt->{H}) {
    eval 'use Pod::Text';
    if ($@) {
        # Pretty up the huge message if we know which one it is.
        chomp(my $msg = $@);
        if ($msg =~ /INC\scontains/) {
            $msg =~ s/^(.+?)\s\(.+/$1/s;
        }

        print qq{Cannot convert to POD because [$msg].\n};
        exit 1;
    }
    else {
        pod2text($0);
        exit 0;
    }
}



my $verbose = $opt->{v};
my $fake    = $opt->{f};
$verbose = 1 if $fake;

my $report  = $opt->{r};


$db_name = $opt->{d} if $opt->{d};
$db_user = $opt->{u} if $opt->{u};
$db_pass = $opt->{p} if $opt->{p};


my $dbh = DBI->connect(qq{DBI:mysql:$db_name}, $db_user, $db_pass);
my $sth = $dbh->prepare(qq{show databases});
$sth->execute;
my @databases;
while (my ($db) = $sth->fetchrow_array) {
    push @databases, $dbh->quote($db);
}

my $quoted = join(', ', @databases );

##
##  Find all of the users that are NOT linked to a known database.

$sth = $dbh->prepare(qq{SELECT User from db WHERE Db not in ($quoted)});
$sth->execute;
my @users;
while (my ($user) = $sth->fetchrow_array) {
    push @users, $user;
}


printf qq{Orphan users: [%s].\n}, join(', ', @users) if $report or $verbose;
exit 0 if $report;

for my $user (@users) {
    delete_user($user);
}
print qq{Flushing privileges.\n} if $verbose;
$dbh->do(qq{FLUSH PRIVILEGES}) unless $fake;
$sth->finish;
$dbh->disconnect;

print qq{Finished.\n\n} if $verbose;



sub delete_user {
    my $user = shift;

    print qq{- Deleting user "$user"\n} if $verbose;

    print qq{  columns_priv...\n} if $verbose;
    my $sth = $dbh->prepare(qq{DELETE FROM columns_priv WHERE User = ?});
    $sth->execute($user) unless $fake;

    print qq{  db...\n} if $verbose;
    $sth = $dbh->prepare(qq{DELETE FROM db WHERE User = ?});
    $sth->execute($user) unless $fake;

    print qq{  tables_priv...\n} if $verbose;
    $sth = $dbh->prepare(qq{DELETE FROM tables_priv WHERE User = ?});
    $sth->execute($user) unless $fake;

    print qq{  user...\n} if $verbose;
    $sth = $dbh->prepare(qq{DELETE FROM user WHERE User = ?});
    $sth->execute($user) unless $fake;

    print "\n" if $verbose;
}



sub usage {
    return qq{
USAGE

    $0 [options]


OPTIONS

    f   Fake;
        Act like we're doing work, but don't actually change the database.
        Implies -v.

    d   Database;
        Defaults to '$db_name';

    h   Help;
        Show this message and exit succesfully.

    H   POD
        Show POD documentation for this program and exit succesfully.

    p   Database password;
        Defaults to '$db_pass'.

    r   Report only;
        Only report orphan users.

    u   Database user;
        Defaults to '$db_user'.

    v   Verbose;
        Turn on "useful" runtime messages.

};


}


=pod

=head1 NAME

mysqlorphans - Reports and/or deletes orphaned MySQL users.


=head1 SYNOPSYS

    # Get standard help (use this first).
    % mysqlorphans -h
    
    # Report orphaned MySQL users
    % mysqlorphans -r

    # See what would be done if you cleared the orphans.
    % mysqlorphans -f

    # Actually clear the orphans.
    % mysqlorphans [-p]


Note, this script ships with the root password set as an empty string. If you've
got a root password set, and we hope you do, then straight execution of this
script with no options will raise an error about failing to connect to the
database. Our suggestion is that you leave the password empty in this file to
insure that this script can't be run accidently, but rather requires some level
of explicit user action. Namely the C<-p> switch.



=head1 DESCRIPTION

Sometimes MySQL users become defunct by way of having their database(s) dropped.
If your system has a lot of users, then finding these orphans can be difficult,
or at least time consuming. This script attempts to answer that problem by doing
the leg work of finding MySQL users that don't appear to have any remaining
privileges.

We strongly suggest that you execute this program with the C<-h> switch for help
before use. We further suggest that you use the C<-r> switch before any live
actions until you are comfortable with the operation of this program. To get
comfortable, you may want to use the C<-f> (fake) switch to find out what's
going to be done without actually doing it.


=head1 BUGS

This script isn't known to contain any operation problems. Please, let us know
if you find any.

=over 4

=item *

This script should ship in a real distribution package with the standard
installation tools.


=back


=head1 AUTHOR

Originally written by Rodney Broom.

Please, send any comments to perl@rbroom.com.

=head1 COPYRIGHT

Developed by Rodney Broom for R.Broom Consulting. All rights reserved, 2002.
You may use this under the same terms as Perl itself. This includes crediting
myself and R.Broom Consulting.


=head1 WARRANTEE

This software comes with no warrantee of any kind.


=cut