MySQL SHOW GRANTS Statement

In this tutorial, we will see how to display roles and privileges assigned to the MySQL users using the SHOW GRANTS statement in multiple ways. Here we go!

Also read: MySQL GRANT Privileges

Introduction to MySQL SHOW GRANTS

The SHOW GRANTS statement displays the privileges and the roles assigned to the user. Moreover, This statement returns the privileges and roles assigned to other roles.

The syntax of the SHOW GRANTS statement is –

SHOW GRANTS
    [FOR user_or_role
        [USING role [, role] ...]]Code language: SQL (Structured Query Language) (sql)

Here, the second and third lines are optional. Even if you omit these lines, you will get proper output.

You can display the privileges or roles granted to the user as well as a particular role. Along with it, you can display the privileges granted to the user based on the roles.

If you specify the role using the USING clause, make sure that role is already assigned to that user.

MySQL SHOW GRANTS Example

Before proceeding to the methods to display the privileges of the user, let’s create a user account and a role and assign them some privileges.

Creating a user and assigning a privilege-

 CREATE USER depp@localhost IDENTIFIED BY '@jack';
 GRANT CREATE ON *.* TO depp@localhost;Code language: SQL (Structured Query Language) (sql)

Here, we have created a user called ‘depp@localhost’ and assigned only the ‘CREATE’ privilege to it for all the databases available in the database system.

Creating a role and assigning privileges-

CREATE ROLE author@localhost;
GRANT SELECT,INSERT,UPDATE ON *.* TO author@localhost;Code language: SQL (Structured Query Language) (sql)

Here, we have created a role called ‘author@localhost’ and assigned three privileges to it.

We are all set now.

Also read: MySQL REVOKE – Revoke privileges from the user

SHOW GRANTS For Current User

We created a user earlier. However, it is not the active user. While creating this tutorial, the active user of the MySQL DBMS is the admin. So, the admin has all privileges assigned already.

We can display the privilege of the current user by simply using the SHOW GRANTS statement.

Note that, the below three statements are equivalent to each other.

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

As you can see, all privileges are granted to the current user, which is an admin.

SHOW GRANTS For Other Users

We can use the SHOW GRANTS statement to see all privileges granted to other users as well.

Earlier we created a user ‘depp@localhost’ and assigned some privileges to it. Let’s display its privileges.

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

As you can see here, we get the expected output.

SHOW GRANTS For Roles

The SHOW GRANTS statement is not limited to displaying the privileges of the users, but it can also be used to display the privilege of the roles that you create in your MySQL DBMS.

We have already created a role called ‘author@localhost’ earlier and assigned it the privileges- SELECT, INSERT AND UPDATE.

Let’s display its privileges using the SHOW GRANTS statement.

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

As you can see, the statement is returning the correct result.

SHOW GRANTS With USING Clause

If you have assigned a role to the user, we can display it using the USING clause.

For this, let’s assign the role that we have created to the user first.

GRANT author@localhost TO depp@localhost;Code language: SQL (Structured Query Language) (sql)

As you can see, we have granted the role to the user.

Now, if we display the privileges of the user ‘depp@localhost’, it will show us the name of the role as a privilege but we can’t see what actual privileges are there in that role.

Didn’t understand? Let’s first display the privileges of the user.

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

Here, we can see the CREATE privilege that we have granted at the beginning and the role name ‘author@localhost’. But, can you tell what privileges are there behind the ‘author@localhost’ role? – No.

To display all privileges of the user including the privileges of the role which is assigned to that user, we can use the USING clause.

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

Here you can see that the first line shows all the privileges that have been assigned to the user including the roles’ privileges.

Conclusion

In this tutorial, we have learned what is the SHOW GRANTS statement and how to use it for displaying the privileges of the current user, other users as well as roles. For more information, refer to the official documentation.

References

MySQL Official Documentation on the SHOW GRANTS statement.