MySQL SHOW GRANTS

Summary: In this tutorial, you will learn how to use MySQL SHOW GRANTS statement to view the privileges previously granted to a user or role.

Introduction to MySQL SHOW GRANTS statement

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

Here’s the basic syntax of the SHOW GRANTS statement:

SHOW GRANTS
[FOR {user | role}
[USING role [, role] ...]]Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, specify the name of the user account or role for which you want to display the previously granted privileges after the FOR keyword. If you omit the FOR clause, the SHOW GRANTS statement returns the privileges of the current user.
• Second, use the USING clause to inspect the privileges associated with roles for the user. The roles specified in the USING clause must have been previously granted to the user.

To execute the SHOW GRANTS statement, you need to have SELECT privilege for the mysql system database, except when you 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.

1) Using the SHOW GRANTS statement to display the privileges of the current user

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

SHOW GRANTS;Code language: SQL (Structured Query Language) (sql)

If you use the mysql tool, the output may not be readable. To fix it, you can display the output in the vertical layout:

SHOW GRANTS\G

It is equivalent to the following statement:

SHOW GRANTS FOR CURRENT_USER\GCode language: SQL (Structured Query Language) (sql)

and

SHOW GRANTS FOR CURRENT_USER()\GCode language: SQL (Structured Query Language) (sql)

Note that both CURRENT_USER and CURRENT_USER() functions return the currently logged-in user.

2) Using the SHOW GRANTS statement to display the privileges granted to a user

First, create a new database named vehicles:

CREATE DATABASE vehicles;Code language: SQL (Structured Query Language) (sql)

Second, select the database vehicles :

USE vehicles;Code language: SQL (Structured Query Language) (sql)

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

CREATE TABLE cars (
    id INT AUTO_INCREMENT,
    make VARCHAR(100) NOT NULL,
    model VARCHAR(100) NOT NULL,
    PRIMARY KEY (id)
);Code language: SQL (Structured Query Language) (sql)

Fourth, create a new user called musk@localhost:

CREATE USER musk@localhost 
IDENTIFIED BY 'Super1Pass!';Code language: SQL (Structured Query Language) (sql)

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

SHOW GRANTS 
FOR musk@localhost;Code language: SQL (Structured Query Language) (sql)

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

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

GRANT ALL 
ON vehicles.* 
TO musk@localhost;Code language: SQL (Structured Query Language) (sql)

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

SHOW GRANTS 
FOR musk@localhost;Code language: SQL (Structured Query Language) (sql)

3) Using the SHOW GRANTS statement to display the privileges granted for a role

First, create a new role called writer@localhost:

CREATE ROLE writer@localhost;Code language: SQL (Structured Query Language) (sql)

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

SHOW GRANTS 
FOR 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:

GRANT 
    SELECT, 
    INSERT, 
    UPDATE, 
    DELETE
ON vehicles.*
TO writer@localhost;Code language: SQL (Structured Query Language) (sql)

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

SHOW GRANTS 
FOR writer@localhost;Code language: SQL (Structured Query Language) (sql)

4) Using SHOW GRANTS with USING clause example

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

CREATE USER jame@localhost
IDENTIFIED BY 'Secret@Pass1';Code language: SQL (Structured Query Language) (sql)

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

GRANT EXECUTE 
ON vehicles.* 
TO jame@localhost;Code language: SQL (Structured Query Language) (sql)

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

GRANT writer@localhost
TO jame@localhost;Code language: SQL (Structured Query Language) (sql)

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

SHOW GRANTS 
FOR 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:

SHOW GRANTS 
FOR jame@localhost 
USING writer@localhost;Code language: SQL (Structured Query Language) (sql)

Summary

• Use the MySQL SHOW GRANTS statement to display privileges granted to a user or role.