Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 37.9Mb
PDF (A4) - 37.9Mb
PDF (RPM) - 36.5Mb
HTML Download (TGZ) - 9.9Mb
HTML Download (Zip) - 9.9Mb
HTML Download (RPM) - 8.7Mb
Man Pages (TGZ) - 209.5Kb
Man Pages (Zip) - 318.7Kb
Info (Gzip) - 3.5Mb
Info (Zip) - 3.5Mb
Excerpts from this Manual

13.7.5.21 SHOW GRANTS Syntax

SHOW GRANTS [FOR user]

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.

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 system database, except to display privileges for the current user.

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:

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.

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 output does not include IDENTIFIED BY PASSWORD clauses. Use the SHOW CREATE USER statement instead. See Section 13.7.5.12, “SHOW CREATE USER Syntax”.


User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
  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.