- Documentation Library
- Table of Contents
- MySQL 6.0 Reference Manual
- MySQL 5.1 Reference Manual
- MySQL 5.0 Reference Manual
- MySQL 3.23/4.0/4.1 Manual
- Table of Contents
- 12.5.5 SHOW Syntax
- 12.5.5.1 SHOW AUTHORS Syntax
- 12.5.5.2 SHOW CHARACTER SET Syntax
- 12.5.5.3 SHOW COLLATION Syntax
- 12.5.5.4 SHOW COLUMNS Syntax
- 12.5.5.5 SHOW CONTRIBUTORS Syntax
- 12.5.5.6 SHOW CREATE DATABASE Syntax
- 12.5.5.7 SHOW CREATE EVENT
- 12.5.5.8 SHOW CREATE PROCEDURE and SHOW CREATE FUNCTION Syntax
- 12.5.5.9 SHOW CREATE TABLE Syntax
- 12.5.5.10 SHOW CREATE TRIGGER Syntax
- 12.5.5.11 SHOW CREATE VIEW Syntax
- 12.5.5.12 SHOW DATABASES Syntax
- 12.5.5.13 SHOW ENGINE Syntax
- 12.5.5.14 SHOW ENGINES Syntax
- 12.5.5.15 SHOW ERRORS Syntax
- 12.5.5.16 SHOW EVENTS
- 12.5.5.17 SHOW GRANTS Syntax
- 12.5.5.18 SHOW INDEX Syntax
- 12.5.5.19 SHOW INNODB STATUS Syntax
- 12.5.5.20 SHOW OPEN TABLES Syntax
- 12.5.5.21 SHOW PLUGINS Syntax
- 12.5.5.22 SHOW PRIVILEGES Syntax
- 12.5.5.23 SHOW PROCEDURE CODE and SHOW FUNCTION CODE Syntax
- 12.5.5.24 SHOW PROCEDURE STATUS and SHOW FUNCTION STATUS Syntax
- 12.5.5.25 SHOW PROCESSLIST Syntax
- 12.5.5.26 SHOW SCHEDULER STATUS Syntax
- 12.5.5.27 SHOW STATUS Syntax
- 12.5.5.28 SHOW TABLE STATUS Syntax
- 12.5.5.29 SHOW TABLES Syntax
- 12.5.5.30 SHOW TRIGGERS Syntax
- 12.5.5.31 SHOW VARIABLES Syntax
- 12.5.5.32 SHOW WARNINGS Syntax
SHOW GRANTS [FOR user]
This statement lists the GRANT statement or
statements that must be issued to duplicate the privileges that
are granted to a MySQL user account. The account is named using
the same format as for the GRANT statement;
for example, 'jeffrey'@'localhost'. If you
specify only the username part of the account name, a hostname
part of '%' is used. For additional
information about specifying account names, see
Section 12.5.1.3, “GRANT Syntax”.
mysql> SHOW GRANTS FOR 'root'@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
To list the privileges granted to the account that 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();
As of MySQL 5.1.12, if SHOW GRANTS FOR
CURRENT_USER (or any of the equivalent syntaxes) is
used in DEFINER context, such as within a
stored procedure that is defined with SQL SECURITY
DEFINER), the grants displayed are those of the
definer and not the invoker.
SHOW GRANTS displays only the privileges
granted explicitly to the named account. Other privileges might
be available to the account, but they are not displayed. For
example, if an anonymous account exists, the named account might
be able to use its privileges, but SHOW
GRANTS will not display them.
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
Add your own comment.