Documentation Home
MySQL 5.6 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 31.5Mb
PDF (A4) - 31.6Mb
PDF (RPM) - 30.6Mb
HTML Download (TGZ) - 7.6Mb
HTML Download (Zip) - 7.7Mb
HTML Download (RPM) - 6.6Mb
Man Pages (TGZ) - 187.6Kb
Man Pages (Zip) - 302.1Kb
Info (Gzip) - 2.9Mb
Info (Zip) - 2.9Mb
Excerpts from this Manual SHOW GRANTS Syntax


This statement displays the privileges that are assigned to a MySQL user account, in the form of GRANT statements that must be executed to duplicate the privilege assignments.

SHOW GRANTS requires the SELECT privilege for the mysql database, except to display privileges for the current user. For output that includes an IDENTIFIED BY PASSWORD clause displaying an account password hash value, the SUPER privilege is required to see the actual hash value. Otherwise, the value displays as <secret>.

To name the account for SHOW GRANTS, use the same format as for the GRANT statement; for example, 'jeffrey'@'localhost':

mysql> SHOW GRANTS FOR 'jeffrey'@'localhost';
| Grants for jeffrey@localhost                                     |
| GRANT USAGE ON *.* TO `jeffrey`@`localhost`                      |
| GRANT SELECT, INSERT, UPDATE ON `db1`.* TO `jeffrey`@`localhost` |

The host part, if omitted, defaults to '%'. For additional information about specifying account names, see Section 6.2.3, “Specifying Account Names”.

To display the privileges granted to the current user (the account you are using to connect to the server), you can use any of the following statements:


If SHOW GRANTS FOR CURRENT_USER (or any of the equivalent syntaxes) is used in DEFINER context, such as within a stored procedure that executes with DEFINER rather than INVOKER privileges), the grants displayed are those of the definer and not the invoker.

SHOW GRANTS does not display privileges that are available to the named account but are granted to a different account. For example, if an anonymous account exists, the named account might be able to use its privileges, but SHOW GRANTS does not display them.

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

Here is a small shell scrip which might also help.

mysql --batch --skip-column-names -e "SELECT user, host FROM user" mysql > $tmp
cat $tmp | while read user host
echo "# $user @ $host"
mysql --batch --skip-column-names -e"SHOW GRANTS FOR '$user'@$host"
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

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

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




if [ $X = "X" ]; then
echo "You must provide a username or partial username to use."
echo " username1 [username2]"
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 "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"

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

rm -f $file1
rm -f $file2

Sign Up Login You must be logged in to post a comment.