SHOW GRANTS

Complete SHOW GRANTS reference for MariaDB. Complete syntax guide with filtering, pattern matching, and access control options with comprehensive examples.

Syntax

SHOW GRANTS [FOR user|role]

Description

The SHOW GRANTS statement lists privileges granted to a particular user or role.

Users

The statement lists the GRANT statement or statements that must be issued to duplicate the privileges that are granted to a MariaDB user account. The account is named using the same format as for theGRANT statement; for example, 'jeffrey'@'localhost'. If you specify only the user name part of the account name, a host name part of '%' is used. For additional information about specifying account names, see GRANT.

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();

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 withSQL SECURITY DEFINER), the grants displayed are those of the definer and not the invoker.

Roles

SHOW GRANTS can also be used to view the privileges granted to a role.

Example

SHOW GRANTS FOR journalist;
+------------------------------------------+
| Grants for journalist                    |
+------------------------------------------+
| GRANT USAGE ON *.* TO 'journalist'       |
| GRANT DELETE ON `test`.* TO 'journalist' |
+------------------------------------------+

From MariaDB 10.11:

FOR PUBLIC

GRANT ... TO PUBLIC grants privileges to all users. SHOW GRANTS FOR PUBLIC shows all these grants.

SHOW GRANTS FOR public;
+------------------------------------------------+
| Grants for PUBLIC                              |
+------------------------------------------------+
| GRANT ALL PRIVILEGES ON `dev_db`.* TO `PUBLIC` |
+------------------------------------------------+

Before MariaDB 10.11:

FOR PUBLIC is not available.

Denies

From MariaDB 13.1, privileges blocked with DENY are listed as separate DENY lines beside the GRANT lines:

SHOW GRANTS FOR 'alice'@'localhost';
+---------------------------------------------------------------+
| Grants for alice@localhost                                    |
+---------------------------------------------------------------+
| GRANT USAGE ON *.* TO `alice`@`localhost`                     |
| DENY SELECT ON *.* TO `alice`@`localhost`                     |
| GRANT SELECT ON `hr`.`staff` TO `alice`@`localhost`           |
| DENY UPDATE (`salary`) ON `hr`.`staff` TO `alice`@`localhost` |
+---------------------------------------------------------------+

DENY lines are only visible to a connection that holds the SELECT privilege on the mysql database. An account without it sees its own GRANT lines but none of its denies, so denies are not self-advertising. This applies to SHOW GRANTS, SHOW GRANTS FOR CURRENT_ROLE, SHOW GRANTS FOR role, and SHOW GRANTS FOR PUBLIC alike.

The visibility check is itself subject to denies: an account with SELECT on mysql.* but a deny on mysql.global_priv sees no denies, and cannot run SHOW GRANTS for another account.