SHOW GRANTS [FOR user|role]
The SHOW GRANTS
statement lists privileges granted to a particular user or role.
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 the
GRANT
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 with
SQL SECURITY DEFINER
), the grants displayed are those of the
definer and not the invoker.
Note that the DELETE HISTORY
privilege, introduced in MariaDB 10.3.4, was displayed as DELETE VERSIONING ROWS
when running SHOW GRANTS
until MariaDB 10.3.15 (MDEV-17655).
SHOW GRANTS
can also be used to view the privileges granted to a role.
SHOW GRANTS FOR journalist; +------------------------------------------+ | Grants for journalist | +------------------------------------------+ | GRANT USAGE ON *.* TO 'journalist' | | GRANT DELETE ON `test`.* TO 'journalist' | +------------------------------------------+
GRANT ... TO PUBLIC was introduced in MariaDB 10.11 to grant 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` | +------------------------------------------------+