Summary: in this tutorial, you will learn how to use the MySQL SHOW GRANTS statement to display the privileges and roles assigned to an account user.

Introduction to MySQL SHOW GRANTS statement

The MySQL SHOW GRANTS statement returns all privileges and roles granted to an account user or role.

Here is the basic syntax of the SHOW GRANTS statement:

Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, specify the name of the user account or role that you want to display the privileges that are previously granted to the user account or role after the FOR keyword. If you skip the FOR clause, the SHOW GRANTS returns the privileges of the current user.
• Second, use the USING clause to examine the privileges associated with roles for the user. The roles that you specify in the USING clause must previously granted to the user.

To execute the SHOW GRANTS statement, you need to have SELECT privilege for the mysql system database, except to show privileges and roles for the current user.

MySQL SHOW GRANTS statement examples

Let’s take some examples of using the MySQL SHOW GRANTS statement.

A) Using MySQL SHOW GRANTS to display the privileges granted for the current user

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

Code language: SQL (Structured Query Language) (sql)

It is equivalent to the following statement:

Code language: SQL (Structured Query Language) (sql)

and

Code language: SQL (Structured Query Language) (sql)

Both CURRENT_USER and CURRENT_USER() return the current user.

B) Using MySQL SHOW GRANTS to display the privileges granted for a user

First, create a new database named vehicles:

Code language: SQL (Structured Query Language) (sql)

Second, select the database vehicles :

Code language: SQL (Structured Query Language) (sql)

Third, create a new table called cars in the vehicles database:

Code language: SQL (Structured Query Language) (sql)

Fourth, create a new user called musk@localhost:

Code language: SQL (Structured Query Language) (sql)

Fifth, show the default privileges granted to the user musk@localhost:

Code language: SQL (Structured Query Language) (sql)

The GRANT USAGE is the synonym of no privilege. By default, when a new user created, it has no privilege.

Sixth, grant all privileges on the vehicles database to the user musk@localhost:

Code language: SQL (Structured Query Language) (sql)

Finally, show the privileges granted for the user musk@localhost:

Code language: SQL (Structured Query Language) (sql)

C) Using MySQL SHOW GRANTS to display the privileges granted for a role

First, create a new role called writer@localhost:

Code language: SQL (Structured Query Language) (sql)

Second, show privileges granted for the role writer@localhost:

Code language: SQL (Structured Query Language) (sql)

Third, grant SELECT, INSERT, UPDATE, and DELETE privileges on the vehicles database to the writer@localhost:

Code language: SQL (Structured Query Language) (sql)

Fourth, show privileges granted for the role writer@localhost:

Code language: SQL (Structured Query Language) (sql)

D) Using MySQL SHOW GRANTS with USING clause example

First, create a new account user called jame@localhost:

Code language: SQL (Structured Query Language) (sql)

Second, grant the EXECUTE privilege to the user jame@localhost:

Code language: SQL (Structured Query Language) (sql)

Third, grant the role writer@localhost to the user jame@localhost:

Code language: SQL (Structured Query Language) (sql)

Fourth, display the privileges granted for the user jame@localhost:

Code language: SQL (Structured Query Language) (sql)

Finally, use the USING clause in the SHOW GRANTS statement to display privileges associated with the writer@localhost role:

Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the MySQL SHOW GRANTSstatement to display privileges granted for an account user or role.