Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 32.8Mb
PDF (A4) - 32.8Mb
PDF (RPM) - 30.8Mb
HTML Download (TGZ) - 7.8Mb
HTML Download (Zip) - 7.9Mb
HTML Download (RPM) - 6.7Mb
Man Pages (TGZ) - 143.5Kb
Man Pages (Zip) - 203.6Kb
Info (Gzip) - 3.0Mb
Info (Zip) - 3.0Mb


Pre-General Availability Draft: 2017-07-20

13.7.5.21 SHOW GRANTS Syntax

SHOW GRANTS
    [FOR user_or_role
        [USING role [, role] ...]]

user_or_role: {
    user
  | role
}

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

Note

To display nonprivilege information for MySQL accounts, use the SHOW CREATE USER statement. See Section 13.7.5.12, “SHOW CREATE USER Syntax”.

SHOW GRANTS requires the SELECT privilege for the mysql database, except to display privileges and roles for the current user. The USING clause requires the SUPER privilege, except to display role privileges for the current user.

To name the account or role 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 and role names, see Section 6.2.4, “Specifying Account Names”, and Section 6.2.5, “Specifying Role 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:

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

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.

In MySQL 8.0 compared to previous series, SHOW GRANTS no longer displays ALL PRIVILEGES in its global-privileges output because the meaning of ALL PRIVILEGES at the global level varies depending on which dynamic privileges are defined. Instead, SHOW GRANTS explictly lists each granted global privilege:

mysql> SHOW GRANTS FOR 'root'@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD,         |
| SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES,  |
| SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION   |
| SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE,  |
| ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE,      |
| CREATE ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT      |
| OPTION                                                              |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+

Applications that process SHOW GRANTS output should be adjusted accordingly.

At the global level, GRANT OPTION applies to all granted static global privileges if granted for any of them, but applies individually to granted dynamic privileges. SHOW GRANTS displays global privileges this way:

  • One line listing all granted static privileges, if there are any, including WITH GRANT OPTION if appropriate.

  • One line listing all granted dynamic privileges for which GRANT OPTION is granted, if there are any, including WITH GRANT OPTION.

  • One line listing all granted dynamic privileges for which GRANT OPTION is not granted, if there are any, without WITH GRANT OPTION.

With the optional USING clause, SHOW GRANTS enables you to examine the privileges associated with roles for the user. Each role named in the USING clause must be granted to the user.

The USING clause requires the SUPER privilege, except to display information for the current user.

Suppose that user u1 is assigned roles r1 and r2, as follows:

CREATE ROLE 'r1', 'r2';
GRANT SELECT ON db1.* TO 'r1';
GRANT INSERT, UPDATE, DELETE ON db1.* TO 'r2';
CREATE USER 'u1'@'localhost' IDENTIFIED BY 'u1pass';
GRANT 'r1', 'r2' TO 'u1'@'localhost';

SHOW GRANTS without USING shows the granted roles:

mysql> SHOW GRANTS FOR 'u1'@'localhost';
+---------------------------------------------+
| Grants for u1@localhost                     |
+---------------------------------------------+
| GRANT USAGE ON *.* TO `u1`@`localhost`      |
| GRANT `r1`@`%`,`r2`@`%` TO `u1`@`localhost` |
+---------------------------------------------+

Adding a USING clause causes the statement to also display the privileges associated with each role named in the clause:

mysql> SHOW GRANTS FOR 'u1'@'localhost' USING 'r1';
+---------------------------------------------+
| Grants for u1@localhost                     |
+---------------------------------------------+
| GRANT USAGE ON *.* TO `u1`@`localhost`      |
| GRANT SELECT ON `db1`.* TO `u1`@`localhost` |
| GRANT `r1`@`%`,`r2`@`%` TO `u1`@`localhost` |
+---------------------------------------------+
mysql> SHOW GRANTS FOR 'u1'@'localhost' USING 'r2';
+-------------------------------------------------------------+
| Grants for u1@localhost                                     |
+-------------------------------------------------------------+
| GRANT USAGE ON *.* TO `u1`@`localhost`                      |
| GRANT INSERT, UPDATE, DELETE ON `db1`.* TO `u1`@`localhost` |
| GRANT `r1`@`%`,`r2`@`%` TO `u1`@`localhost`                 |
+-------------------------------------------------------------+
mysql> SHOW GRANTS FOR 'u1'@'localhost' USING 'r1', 'r2';
+---------------------------------------------------------------------+
| Grants for u1@localhost                                             |
+---------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `u1`@`localhost`                              |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `db1`.* TO `u1`@`localhost` |
| GRANT `r1`@`%`,`r2`@`%` TO `u1`@`localhost`                         |
+---------------------------------------------------------------------+
Note

A privileges granted an an account is always in effect, but a role is not. The active roles for an account can differ across and within sessions, depending on the value of the activate_all_roles_on_login system variable, the account default roles, and whether SET ROLE has been executed within a session.

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.

SHOW GRANTS displays mandatory roles named in the mandatory_roles system variable value as follows:

  • SHOW GRANTS without a FOR clause displays privileges for the current user, and includes mandatory roles.

  • SHOW GRANTS FOR user displays privileges for the named user, and does not include mandatory roles.

This behavior is for the benefit of applications that use the output of SHOW GRANTS FOR user to determine which privileges are granted explicitly to the named user. Were that output to include mandatory roles, it would be difficult to distinguish roles granted explicitly to the user from mandatory roles.

For the current user, applications can determine privileges with or without mandatory roles by using SHOW GRANTS or SHOW GRANTS FOR CURRENT_USER, respectively.


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

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