#!/usr/bin/perl

# mysql-show-grants canonicalizes and prints MySQL grants so you can effectively
# replicate, compare and version-control them.
#
# This program is copyright (c) 2007 Baron Schwartz.
# Feedback and improvements are welcome.
#
# THIS PROGRAM IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED
# WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF
# MERCHANTIBILITY AND FITNESS FOR A PARTICULAR PURPOSE.
#
# This program is free software; you can redistribute it and/or modify it under
# the terms of the GNU General Public License as published by the Free Software
# Foundation, version 2; OR the Perl Artistic License.  On UNIX and similar
# systems, you can issue `man perlgpl' or `man perlartistic' to read these
# licenses.
#
# You should have received a copy of the GNU General Public License along with
# this program; if not, write to the Free Software Foundation, Inc., 59 Temple
# Place, Suite 330, Boston, MA  02111-1307  USA.

use strict;
use warnings FATAL => 'all';

use DBI;
use English qw(-no_match_vars);
use Getopt::Long;
use List::Util qw(max);
use Term::ReadKey;

our $VERSION = '1.0.3';
our $DISTRIB = '1053';
our $SVN_REV = sprintf("%d", q$Revision: 779 $ =~ m/(\d+)/g);

# ############################################################################
# Get configuration information.
# ############################################################################

my @opt_spec = (
   { s => 'askpass',           d => 'Prompt for password for connections' },
   { s => 'database|D=s',      d => 'Database to use' },
   { s => 'defaults-file|F=s', d => 'Only read default options from the given file' },
   { s => 'drop|d',            d => 'Add DROP USER before each user' },
   { s => 'flush|f',           d => 'Add FLUSH PRIVILEGES' },
   { s => 'host|h=s',          d => 'Connect to host' },
   { s => 'ignore|i=s',        d => 'Ignore this comma-separated list of users' },
   { s => 'help',              d => 'Show this help message' },
   { s => 'only|o=s',          d => 'Only show grants for this comma-separated list of users' },
   { s => 'password|p=s',      d => 'Password to use when connecting' },
   { s => 'port|P=i',          d => 'Port number to use for connection' },
   { s => 'revoke|r',          d => 'Add REVOKE statements' },
   { s => 'separate|s',        d => 'List each GRANT or REVOKE separately' },
   { s => 'socket|S=s',        d => 'Socket file to use for connection' },
   { s => 'user|u=s',          d => 'User for login if not current user' },
   { s => 'version',           d => 'Output version information and exit' },
);
# This is the container for the command-line options' values to be stored in
# after processing.  Initial values are defaults.
my %opts;
# Post-process...
my %opt_seen;
foreach my $spec ( @opt_spec ) {
   my ( $long, $short ) = $spec->{s} =~ m/^([\w-]+)(?:\|([^!+=]*))?/;
   $spec->{k} = $short || $long;
   $spec->{l} = $long;
   $spec->{t} = $short;
   $spec->{n} = $spec->{s} =~ m/!/;
   $opts{$spec->{k}} = undef unless defined $opts{$spec->{k}};
   die "Duplicate option $spec->{k}" if $opt_seen{$spec->{k}}++;
}

Getopt::Long::Configure('no_ignore_case', 'bundling');
GetOptions( map { $_->{s} => \$opts{$_->{k}} } @opt_spec) or $opts{help} = 1;

if ( $opts{version} ) {
   print "mysql-show-grants  Ver $VERSION Distrib $DISTRIB Changeset $SVN_REV\n";
   exit(0);
}

# Turn comma-separated lists into arrays and hashes
if ( $opts{o} ) {
   my @users = map {
         my ( $user, $host ) = parse_user($_);
         { User => $user, Host => $host };
      }
      grep { $_ =~ m/\S/ }
      split(/,\s*/, $opts{o});
   $opts{o} = \@users;
}
if ( $opts{i} ) {
   my @users = map {
         my ( $user, $host ) = parse_user($_);
         "'$user'\@'$host'";
      }
      grep { $_ =~ m/\S/ }
      split(/,\s*/, $opts{i});
   $opts{i} = { map { $_ => 1 } @users };
}

if ( $opts{help} ) {
   print "Usage: mysql-show-grants <options> batch-file\n\n";
   my $maxw = max(map { length($_->{l}) + ($_->{n} ? 4 : 0)} @opt_spec);
   foreach my $spec ( sort { $a->{l} cmp $b->{l} } @opt_spec ) {
      my $long  = $spec->{n} ? "[no]$spec->{l}" : $spec->{l};
      my $short = $spec->{t} ? "-$spec->{t}" : '';
      printf("  --%-${maxw}s %-4s %s\n", $long, $short, $spec->{d});
   }
   print <<USAGE;

mysql-show-grants shows grants (user privileges) from a MySQL server.
Connection options are read from MySQL option files.  For more details, please
read the documentation:

   perldoc mysql-show-grants

USAGE
   exit(0);
}

# ############################################################################
# Get ready to do the main work.
# ############################################################################
my %conn = (
   F => 'mysql_read_default_file',
   h => 'host',
   P => 'port',
   S => 'mysql_socket'
);

# Connect to the database
if ( !$opts{p} && $opts{askpass} ) {
   print "Enter password: ";
   ReadMode('noecho');
   chomp($opts{p} = <STDIN>);
   ReadMode('normal');
   print "\n";
}

my $dsn = 'DBI:mysql:' . ( $opts{D} || '' ) . ';'
   . join(';', map  { "$conn{$_}=$opts{$_}" } grep { defined $opts{$_} } qw(F h P S))
   . ';mysql_read_default_group=mysql';
my $dbh = DBI->connect($dsn, @opts{qw(u p)}, { AutoCommit => 1, RaiseError => 1, PrintError => 0 } );

my ( $version, $ts ) = $dbh->selectrow_array("SELECT VERSION(), NOW()");
print join("\n",
   "-- Grants dumped by mysql-show-grants $VERSION",
   "-- Dumped from server $dbh->{mysql_hostinfo}, MySQL $version at $ts",
   ), "\n";

my $users = $opts{o} || $dbh->selectall_arrayref(
   'SELECT DISTINCT User, Host FROM mysql.user ORDER BY User, Host',
   { Slice => {} });

my $exit_status;

foreach my $u (@$users) {
   next if $opts{i} && $opts{i}->{"'$u->{User}'\@'$u->{Host}'"};

   my @grants;
   eval {
      @grants = @{ $dbh->selectcol_arrayref(
         "SHOW GRANTS FOR '$u->{User}'\@'$u->{Host}'") };
   };
   if ( $EVAL_ERROR ) {
      $exit_status = 1;
   }
   next unless @grants;

   if ( $opts{s} ) { # List each grant separately.
      @grants = map {
         my ( $grants, $on_what ) = $_ =~ m/GRANT (.*?) ON ((?:`|\*).*)$/;
         map { "GRANT $_ ON $on_what" } split(', ', $grants);
      } @grants;
      my $count;
      # If the row with IDENTIFIED BY has multiple grants, this will create many
      # such rows; strip it from all but the first.
      @grants = map {
         if ( $_ =~ m/IDENTIFIED BY/ ) {
            if ( $count++ ) {
               $_ =~ s/ IDENTIFIED BY.*//;
            }
         }
         $_;
      } @grants;
   }
   else { # Sort the actual grants lexically within each row for consistency.
      @grants = map {
         $_ =~ s/GRANT (.*?) ON (`|\*)/"GRANT " . join(', ', sort(split(', ', $1))) . " ON $2"/e;
         $_;
      } @grants;
   }

   # Sort the grant rows for consistency too, but the one with the password
   # should always come first.
   @grants = sort {
      $b =~ m/IDENTIFIED BY/ <=> $a =~ m/IDENTIFIED BY/ || $a cmp $b
   } @grants;

   # Print REVOKE statements.
   if ( $opts{r} ) {
      my @revoke = map {
         my @result;
         my ( $grants, $on_what, $user ) = $_
            =~ m/GRANT (.*?) ON ((?:`|\*).*?) TO ('[^']+'\@'[^']+')/;
         if ( $opts{s} ) {
            @result = map { "REVOKE $_ ON $on_what FROM $user" } split(', ', $grants);
         }
         else {
            @result = "REVOKE $grants ON $on_what FROM $user";
         }
         if ( $_ =~ m/WITH GRANT OPTION/ ) { # The WITH GRANT OPTION must be revoked separately
            push @result, "REVOKE GRANT OPTION ON *.* FROM $user" if $user;
         }
         @result;
      } @grants;

      print join(
         "\n",
         "-- Revoke statements for '$u->{User}'\@'$u->{Host}'",
         map {"$_;"} @revoke),
         "\n";
   }

   if ( $opts{d} ) {
      print join("\n",
         "DROP USER '$u->{User}'\@'$u->{Host}';",
         "DELETE FROM `mysql`.`user` WHERE `User`='$u->{User}' AND `Host`='$u->{Host}';",
         ), "\n";
   }
   
   print join( "\n", "-- Grants for '$u->{User}'\@'$u->{Host}'", map {"$_;"} @grants ), "\n";

   if ( $opts{f} && $opts{s} ) {
      print "FLUSH PRIVILEGES;\n";
   }

   $exit_status = 0;
}

if ( $opts{f} && !$opts{s} ) {
   print "FLUSH PRIVILEGES;\n";
}

$dbh->disconnect;
exit($exit_status);

# ############################################################################
# Subroutines
# ############################################################################
sub parse_user {
   my ( $spec ) = @_;
   my ( $user, $host )
      = $spec =~ m/
         ^               # Beginning of line
         '?([^'@]*)'?    # Username optionally enclosed by '
         (?:
            @            # Followed by @
            '?([^']*?)'? # And host optionally enclosed by '
         )?              # ... which is all optional
         $               # End of line
         /xms;
   $host ||= '%';
   return ( $user, $host );
}

# ############################################################################
# Documentation
# ############################################################################

=pod

=head1 NAME

mysql-show-grants - Canonicalize and print MySQL grants so you can effectively
replicate, compare and version-control them.

=head1 SYNOPSIS

   mysql-show-grants
   mysql-show-grants --separate --revoke | diff othergrants.sql -

=head1 OPTIONS

=over

=item --askpass

Prompt for password for connections.

=item --database

Database to use.

=item --defaults-file

Only read default options from the given file.

=item --drop

Adds DROP USER before each user in the output.

=item --flush

Adds FLUSH PRIVILEGES after output.  You might need this on pre-4.1.1 servers
if you want to drop a user completely.

=item --help

Displays a help message.

=item --host

Connect to host.

=item --ignore

Ignore this comma-separated list of users.

=item --only

Only print grants for this comma-separated list of users.

=item --password

Password to use when connecting.

=item --port

Port number to use for connection.

=item --revoke

Add REVOKE statements for each GRANT statement.

=item --separate

Lists each GRANT or REVOKE separately, instead of the default output from
MySQL's SHOW GRANTS command, which may list many privileges on a single line.
With L<"--flush">, places a FLUSH PRIVILEGES after each user, instead of once
at the end of all the output.

=item --socket

Socket file to use for connection.

=item --user

User for login if not current user.

=item --version

Output version information and exit.

=back

=head1 DESCRIPTION

mysql-show-grants extracts, orders, and then prints grants for MySQL user
accounts.

Why would you want this?  There are several reasons.

The first is to easily replicate users from one server to another; you can
simply extract the grants from the first server and pipe the output directly
into another server.

The second use is to place your grants into version control.  If you do a daily
automated grant dump into version control, you'll get lots of spurious
changesets for grants that don't change, because MySQL prints the actual grants
out in a seemingly random order.  For instance, one day it'll say

  GRANT DELETE, INSERT, UPDATE ON `test`.* TO 'foo'@'%';

And then another day it'll say

  GRANT INSERT, DELETE, UPDATE ON `test`.* TO 'foo'@'%';

The grants haven't changed, but the order has.  This script sorts the grants
within the line, between 'GRANT' and 'ON'.  If there are multiple rows from SHOW
GRANTS, it sorts the rows too, except that it always prints the row with the
user's password first, if it exists.  This removes three kinds of inconsistency
you'll get from running SHOW GRANTS, and avoids spurious changesets in version
control.

Third, if you want to diff grants across servers, it will be hard without
"canonicalizing" them, which mysql-show-grants does.  The output is fully
diff-able.

With the L<"--revoke">, L<"--separate"> and other options, mysql-show-grants
also makes it easy to revoke specific privileges from users.  This is tedious
otherwise.

=head1 SEE ALSO

Someone pointed out that this has been done before (not surprising, as it's not
all that complicated).  Visit http://www.futhark.ch/mysql/139.html for a simpler
implementation of the same general concept, though without the canonicalization.
I borrowed the idea of adding DROP USER from that script, and it inspired me
to add the REVOKE functionality too.

=head1 BUGS

Please use the Sourceforge bug tracker, forums, and mailing lists to request
support or report bugs: L<http://sourceforge.net/projects/mysqltoolkit/>.

=head1 SYSTEM REQUIREMENTS

You need the following Perl modules: DBI and DBD::mysql.

=head1 LICENSE

This program is copyright (c) 2007 Baron Schwartz.
Feedback and improvements are welcome.

THIS PROGRAM IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED
WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF
MERCHANTIBILITY AND FITNESS FOR A PARTICULAR PURPOSE.

This program is free software; you can redistribute it and/or modify it under
the terms of the GNU General Public License as published by the Free Software
Foundation, version 2; OR the Perl Artistic License.  On UNIX and similar
systems, you can issue `man perlgpl' or `man perlartistic' to read these
licenses.

You should have received a copy of the GNU General Public License along with
this program; if not, write to the Free Software Foundation, Inc., 59 Temple
Place, Suite 330, Boston, MA  02111-1307  USA.

=head1 AUTHOR

Baron Schwartz.

=head1 VERSION

This manual page documents Ver 1.0.3 Distrib 1053 $Revision $.

=cut