User Comments

Selecting everything from mysql.user isn't quite the same as doing a SHOW GRANTS for user@host. Ideally, MySQL should allow a subquery on "show", where you could do "SHOW grants for (select concat(user,'@',host) from mysql.user)". However, until then, this Perl script might help (substitute "youruser" and "yourpassword" with details of a suitably privileged user):

#!/usr/bin/perl -w

use strict;
use DBI;
use Text::Wrap qw($columns &wrap);


my $dbase = "mysql";
my $dbuser = "youruser";
my $dbpassword = "yourpassword";
my $dbhost = "localhost";

my $dbh;
$dbh = DBI->connect( "DBI:mysql:$dbase:$dbhost", $dbuser, $dbpassword ) or die "can't open database ", $dbh->errstr, __LINE__;

my $statement = qq|SELECT User, Host from user |;

my $que = $dbh->prepare($statement);
my $result = $que->execute or die "error on database statement ", $que->errstr, __LINE__;
my $tmp;
my $columns = 120;


while ( $tmp = $que->fetchrow_hashref ) {

my $statement2 = qq| SHOW GRANTS for | . "'" . $tmp->{User} . "'\@'" . $tmp->{Host} . "'";

my $que2 = $dbh->prepare($statement2);
my $result2 = $que2->execute or die "error on database statement ", $que2->errstr, __LINE__;

print qq(Privileges for $tmp->{User}\@$tmp->{Host}:\n\n);

while ( my $tmp2 = $que2->fetchrow_hashref ) {

print wrap( "", "", $tmp2->{ "Grants for $tmp->{User}\@$tmp->{Host}" } ), "\n\n";


}
print "-" x 120, "\n\n";

}

The perl script provided by simon.ransome is very good, and runs as is. However, the print formatting is not perfect. I think the author intended the $columns variable to set the width of wrapping. If you remove the "my" from this line: my $columns = 120; then the script will work as the author intended. (in my case, I wanted to wrap at 200). Also, the line print "-" x 120, "\n\n"; could be changed to print "-" x $columns, "\n\n"; so that the separator bar will be the same width as the wrapped text. Finally, it's not a bad idea to put this at the end of the script (will occur implicitly, but I like to clean up anyway) $dbh->disconnect;

Hi

Here is a small shell scrip which might also help.

#!/bin/bash
tmp=/tmp/showgrant$$
mysql --batch --skip-column-names -e "SELECT user, host FROM user" mysql > $tmp
cat $tmp | while read user host
do
echo "# $user @ $host"
mysql --batch --skip-column-names -e"SHOW GRANTS FOR '$user'@$host"
done
rm $tmp

;-)

mysql -B -N -e "SELECT DISTINCT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') AS query FROM user" mysql | mysql

Hi,

If you want to backup your MySQL grants this is a way to do it.

You need to create a ~/.my.cnf or add --user=<username> --password=<password> next to mysql

# ~/.my.cnf
[client]
user="root"
password="********"


To backup grants execute the following on your shell

mysql --batch --skip-column-names --execute="SELECT DISTINCT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') AS query FROM user" mysql | mysql --batch --skip-column-names mysql | perl -p -e '$_ =~ s/$/;/; END { print "FLUSH PRIVILEGES;\n" }' > mysql-grants.sql


To backup the corresponding revokes execute the following on your shell

mysql --batch --skip-column-names --execute="SELECT DISTINCT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') AS query FROM user" mysql | mysql --batch --skip-column-names mysql | perl -p -e 'if(/.root.\@.localhost./) { $_ = undef; } else { $_ =~ s/$/;/; $_ =~ s/^GRANT /REVOKE /; $_ =~ s/ TO / FROM /; $_ =~ s/.+ FROM (.+) IDENTIFIED BY .+/-- DROP USER $1;/; } END { print "FLUSH PRIVILEGES;\n" }' > mysql-revokes.sql

If you want to drop users remove "--" before each "DROP USER". Please note I have excluded 'root'@'localhost' for safety reason ;-P

Best Regards,
Guy Baconniere

Add your own comment.