MySQL :: MySQL 5.6 リファレンスマニュアル

8.0 English
5.7 English
5.6 English
5.5 English

MySQL 5.6 リファレンスマニュアル / ... / SHOW GRANTS 構文

13.7.5.22 SHOW GRANTS 構文

SHOW GRANTS [FOR user]

このステートメントは、MySQL ユーザーアカウントに付与される権限を複製するために発行する必要のある 1 つまたは複数の GRANT ステートメントを一覧表示します。このアカウントは、GRANT ステートメントの場合と同じ形式 ('jeffrey'@'localhost' など) を使用して指定されます。アカウント名のユーザー名の部分のみを指定した場合は、'%' のホスト名の部分が使用されます。アカウント名の指定の詳細は、セクション13.7.1.4「GRANT 構文」を参照してください。

mysql> SHOW GRANTS FOR 'root'@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+

サーバーに接続するために使用しているアカウントに付与される権限を一覧表示するには、次のいずれのステートメントでも使用できます。

SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR CURRENT_USER();

SHOW GRANTS FOR CURRENT_USER (または、いずれかの同等の構文) が DEFINER コンテキスト (SQL SECURITY DEFINER で定義されているストアドプロシージャー内など) で使用されている場合、表示される付与権限は呼び出し元ではなく、定義者のものです。

SHOW GRANTS は、指定されたアカウントに明示的に付与される権限のみを表示します。そのアカウントでほかの権限を使用できる可能性もありますが、それらは表示されません。たとえば、匿名アカウントが存在する場合、指定されたアカウントはその権限を使用できる可能性がありますが、SHOW GRANTS はそれらを表示しません。

SHOW GRANTS には、mysql データベースに対する SELECT 権限 (現在のユーザーの権限の表示を除く) が必要です。

PREV HOME UP NEXT

User Comments

Posted by on August 21, 2003

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

}

Posted by Norbert Kremer on October 20, 2003

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;

Posted by Sylvain Viart on October 11, 2006

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

Posted by Guy Baconnière on October 27, 2006

;-)

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

Posted by Guy Baconniere on October 21, 2008

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

Posted by Jorge Torralba on February 2, 2011

This shell scripit I created allows you to show grants for a user and generate the sql to reproduce it. If you pass a 2nd argument, It will take the grants for the first user (arg1) and create them for the 2nd user (arg2).

clear;

username=$1
newname=$2

X="X"$1
Y="X"$2

if [ $X = "X" ]; then
echo
echo "You must provide a username or partial username to use."
echo
echo "showgrants.sh username1 [username2]"
echo
echo "If only username1 is passed, all privileges for username1 will be listed as sql statements."
echo "If username1 and username2 are passeed, all privileges for username1 will be mimicked by username2."
echo "This would include passwords and hosts when available."
echo
fi

passwd="*****"
pid=$$
file1="/tmp/$pid"
file2="/tmp/$pid"2

echo "create temporary table tempuser ( user varchar(30) );
insert into tempuser select distinct user from user where user like '$username%';
select concat(\"show grants for '\",tempuser.user, \"'@'\",host,\"';\") from tempuser, user where user.user = tempuser.user;" | mysql mysql -u root -p$passwd --skip-column-names > $file1

mysql mysql -u root -p$passwd --skip-column-names < $file1 > $file2

if [ $Y != "X" ]; then
cat $file2 | sed 's/$/;/g' | sed "s/${username}[^']*./${newname}/g"
fi

if [ $Y = "X" ]; then
cat $file2 | sed 's/$/;/g'
fi

rm -f $file1
rm -f $file2