Menu

Use the SHOW GRANTS statement to show privileges of a user in MySQL

This article describes how to use the SHOW GRANTS statement to list the privileges granted to a user or role.

Updated on

Sometimes, you need to see the privileges a user has been granted to review.

MySQL allows you to use the SHOW GRANTS statement to show the privileges granted to a user account or a role.

If a connected account is denied access to a database, use SHOW GRANTS to help diagnose MySQL Error 1044.

MySQL SHOW GRANTS Syntax

The following is the basic syntax of the SHOW GRANTS statement :

SHOW GRANTS
[FOR {user | role}
[USING role [, role] ...]]

Here:

  • You should specify the user account or the role after the FOR keyword to show the privileges previously granted to the user account or the role. If the FOR clause is omitted, SHOW GRANTS will return the privileges of the current user.
  • You should use the USING clause to check privileges related to the user’s role. The role you specify in the USING clause must have been granted to the user beforehand.

SHOW GRANTS requires the SELECT privilege on the mysql system schema when you inspect another account or role. You can display your own grants without that privilege. See the official MySQL SHOW GRANTS documentation.

MySQL SHOW GRANTS Examples

There are some examples for the usage of MySQL SHOW GRANTS statements here.

Show the privileges granted to a specified user

This example demonstrates the complete steps to create a user, grant privileges, and view privileges.

  1. Create a new database using CREATE DATABASE, named sqlizdb:

    CREATE DATABASE sqlizdb;

  2. Select the sqlizdb database :

    USE sqlizdb;

  3. Create a new table named sqlizdb in the database : test_table

    CREATE TABLE test_table(
    id int AUTO_INCREMENT PRIMARY KEY,
    txt varchar(100) NOT NULL
);

  4. Create a new local user named 'sqliz'@'localhost':

    CREATE USER 'sqliz'@'localhost' IDENTIFIED BY RANDOM PASSWORD;

    MySQL returns a generated password. Save it securely; this syntax requires MySQL 8.0.18 or later.

  5. Show the privileges for 'sqliz'@'localhost':

    SHOW GRANTS FOR 'sqliz'@'localhost';

    +-----------------------------------+
| Grants for sqliz@localhost        |
+-----------------------------------+
| GRANT USAGE ON *.* TO `sqliz`@`localhost` |
+-----------------------------------+

    GRANT USAGE means no authority. By default, when a new user is created, he has no privileges.

  6. Grant the row privileges used in this example to 'sqliz'@'localhost':

    GRANT SELECT, INSERT ON sqlizdb.* TO 'sqliz'@'localhost';

  7. Finally, show the privileges granted to the user 'sqliz'@'localhost':

    SHOW GRANTS FOR 'sqliz'@'localhost';

    +------------------------------------------------------------------------+
| Grants for sqliz@localhost                                              |
+------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `sqliz`@`localhost`                               |
| GRANT SELECT, INSERT ON `sqlizdb`.* TO `sqliz`@`localhost`               |
+------------------------------------------------------------------------+

Show the privileges granted to the current user

The following statement uses the SHOW GRANTS statement to show the privileges granted to the current user:

SHOW GRANTS;

It is equivalent to the following statement:

SHOW GRANTS FOR CURRENT_USER;

or

SHOW GRANTS FOR CURRENT_USER();

Both CURRENT_USER and CURRENT_USER() return the current user.

Show privileges granted to roles

This example demonstrates the complete steps to create a role, grant privileges to the role, and view privileges of the role.

  1. Create a new local role named 'write_role'@'localhost':

    CREATE ROLE 'write_role'@'localhost';

  2. Show privileges granted to the role 'write_role'@'localhost':

    SHOW GRANTS FOR 'write_role'@'localhost';

    +----------------------------------------+
| Grants for write_role@localhost        |
+----------------------------------------+
| GRANT USAGE ON *.* TO `write_role`@`localhost` |
+----------------------------------------+

  3. Grant SELECT, INSERT, UPDATE, and DELETE privileges on the database sqlizdb to the role 'write_role'@'localhost':

    GRANT SELECT, INSERT, UPDATE, DELETE ON sqlizdb.* TO 'write_role'@'localhost';

  4. Show privileges granted to the role 'write_role'@'localhost':

    SHOW GRANTS FOR 'write_role'@'localhost';

    +-------------------------------------------------------------------------+
| Grants for write_role@localhost                                         |
+-------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `write_role`@`localhost`                           |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `sqlizdb`.* TO `write_role`@`localhost` |
+-------------------------------------------------------------------------+

D) Show the privileges associated with the role of the user

This example demonstrates the detailed steps for creating a new user, assigning role to a user, and displaying privileges.

  1. Create a new local user named 'sqliz2'@'localhost':

    CREATE USER 'sqliz2'@'localhost' IDENTIFIED BY RANDOM PASSWORD;

    Save the generated password shown by MySQL securely.

  2. Grant EXECUTE privileges to the user 'sqliz2'@'localhost':

    GRANT EXECUTE ON sqlizdb.* TO 'sqliz2'@'localhost';

  3. Grant the role 'write_role'@'localhost' to the user 'sqliz2'@'localhost':

    GRANT 'write_role'@'localhost' TO 'sqliz2'@'localhost';

  4. Show the privileges granted to the user 'sqliz2'@'localhost':

    SHOW GRANTS FOR 'sqliz2'@'localhost';

    +----------------------------------------------+
| Grants for sqliz2@localhost                  |
+----------------------------------------------+
| GRANT USAGE ON *.* TO `sqliz2`@`localhost`   |
| GRANT EXECUTE ON `sqlizdb`.* TO `sqliz2`@`localhost` |
| GRANT `write_role`@`localhost` TO `sqliz2`@`localhost` |
+----------------------------------------------+

  5. Use the USING clause in SHOW GRANTS to show the privileges from the 'write_role'@'localhost' role:

    SHOW GRANTS FOR 'sqliz2'@'localhost' USING 'write_role'@'localhost';

    +------------------------------------------------------------------------------+
| Grants for sqliz2@localhost                                                  |
+------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `sqliz2`@`localhost`                                   |
| GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON `sqlizdb`.* TO `sqliz2`@`localhost` |
| GRANT `write_role`@`localhost` TO `sqliz2`@`localhost`                       |
+------------------------------------------------------------------------------+

Conclusion

In MySQL, you can use the SHOW GRANTS statement to show the privileges granted to a user or a role.