SHOW GRANTS

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

user_or_role: {
user (see )
| role (see .
}

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 [HELP SHOW CREATE USER].

SHOW GRANTS requires the SELECT privilege for the mysql system schema,
except to display privileges and roles 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 '%'.

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 equivalent syntax) 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:

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

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

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

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 privilege granted to 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.

MySQL 8.0.16 and higher supports partial revokes of global privileges,
such that a global privilege can be restricted from applying to
particular schemas. To
indicate which global schema privileges have been revoked for
particular schemas, SHOW GRANTS output includes REVOKE statements:

mysql> SET PERSIST partial_revokes = ON;
mysql> CREATE USER u1;
mysql> GRANT SELECT, INSERT, DELETE ON *.* TO u1;
mysql> REVOKE SELECT, INSERT ON mysql.* FROM u1;
mysql> REVOKE DELETE ON world.* FROM u1;
mysql> SHOW GRANTS FOR u1;
+--------------------------------------------------+
| Grants for u1@% |
+--------------------------------------------------+
| GRANT SELECT, INSERT, DELETE ON *.* TO `u1`@`%` |
| REVOKE SELECT, INSERT ON `mysql`.* FROM `u1`@`%` |
| REVOKE DELETE ON `world`.* FROM `u1`@`%` |
+--------------------------------------------------+

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.