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

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.

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.

In addition to displaying the privileges and roles of the current user, to execute SHOW GRANTSthe statement, you need to have privileges mysql on the system database .SELECT

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.

Create a new database using CREATE DATABASE, named sqlizdb:
```
CREATE DATABASE sqlizdb;
```
Select the sqlizdb database :
```
USE sqlizdb;
```

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

Create a new user named sqliz@'%':

CREATE user sqliz@'%' IDENTIFIED by 'SqLiZ9879123!';

Show the privileges for sqliz@'%' :

SHOW GRANTS FOR sqliz@'%';

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

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

Grant all privileges on the sqlizdb database to the user sqliz@'%':
```
GRANT ALL PRIVILEGES ON sqlizdb.* TO sqliz@'%';
```

Finally, show the privileges granted to the user sqliz@'%':

SHOW GRANTS FOR sqliz@'%';

+----------------------------------------------------+
| Grants for sqliz@%                                 |
+----------------------------------------------------+
| GRANT USAGE ON *.* TO `sqliz`@`%`                  |
| GRANT ALL PRIVILEGES ON `sqlizdb`.* TO `sqliz`@`%` |
+----------------------------------------------------+

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;

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.

Create a new role named write_role@'%' using the following statement:
```
CREATE ROLE write_role@'%';
```

Show privileges granted to the role write_role@'%' using the following statement:

SHOW GRANTS FOR write_role@'%';

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

Grant SELECT, INSERT, UPDATE, and DELETE privileges on the database sqlizdb to the role write_role@'%' using the following statement:
```
GRANT SELECT, INSERT, UPDATE, DELETE ON sqlizdb.* TO write_role@'%';
```

Show privileges granted to the role write_role@'%' using the following statement:

SHOW GRANTS FOR write_role@'%';

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

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.

Create a new user named sqliz2@'%' using the following statement:
```
CREATE user sqliz2@'%' IDENTIFIED by 'SqLiZ9879123!';
```
Grant EXECUTE privileges to the user sqliz2@'%' using the following statement:
```
GRANT EXECUTE ON sqlizdb.* TO sqliz2@'%';
```
Grant the role write_role@'%' to the user sqliz2@'%' using the following statement:
```
GRANT write_role@'%' TO sqliz2@'%';
```

Show the privileges granted to the user sqliz2@'%':

SHOW GRANTS FOR sqliz2@'%';

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

Use the USING clause in the SHOW GRANTS statement to show the privileges associated with the write_role@'%' role for the user sqliz2@'%' :

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

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

Conclusion

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

MySQL SHOW GRANTS Syntax

MySQL SHOW GRANTS Examples